Oracle Database Security

1. Oracle Database Security …from the application perspective Martin Nystrom September 2003

2. Purpose • In scope: application security of Oracle databases • Out of scope: system security of Oracle databases

3. Agenda • Oracle architecture • System architecture • Network architecture • Common Oracle objects • Schema/object security • Java security • Application integration techniques • Current challenges at Cisco

4. ONS query SQL*Net (Net8) Grossly oversimplified Oracle network architecture Client host ONS server Oracle client software Oracle Names service 1526 1521 Database server TNS Listener (ADDRESS=(PROTOCOL=TCP) (HOST=db.company.com) (PORT=1521)) Database Oracle architecture

5. ONS query Oracle networking example fields-sj-1 ons-sj Oracle client software Oracle Names service 1526 1521 cmrsdb TNS Listener (ADDRESS=(PROTOCOL=TCP) (HOST=cmrsdb.cisco.com) (PORT=1521)) cmrs Oracle architecture

6. Host #1 Oracle client software Host #2 Oracle client software Host #3 Oracle client software Simplified Oracle Network Architecture w/OCM OCM Server Allowed 1521 TNS Listener Host #1 Host #2 rejected rejected 1521 Database server TNS Listener Database Oracle architecture

7. SQL*Net • Introduced in Oracle V5 • Renamed “Net8” in Oracle8 • Supports multiple protocols (TCP/IP, DECnet, SPX/IPX, etc.) Oracle architecture

8. Authentication & credentials • Can be… • OS authentication • Userid/password • X.509 certificates • Smart card • Etc. • Stored in Oracle • As MD5 hash • …not so for dblinks or FND_USERS Oracle architecture

9. Authentication & credentials (cont.) • Transport encryption • DES encryption of db-selected random number w/user’s password hash • OS-integrated authentication available too • Password changes travel unencrypted • Password management features available • Aging & expiration • History (e.g., can prohibit reuse of last 3 passwords) • Composition & complexity (e.g., require letters + numbers) • Account lockout

10. view stored procedure table table function Common Oracle objects Database instance schema schema trigger index Public area synonym

11. grant select on EMPLOYEES to ASOK; alice’s schema employees candidates orders customers all_users Public objects Oracle object security asok’s schema

12. hr_steward DBA hrdata schema employees candidates Oracle role-based security grant all privileges on EMPLOYEES to role HR_STEWARD; grant HR_STEWARD to CATBERT;

13. orders employees Database links Create database link EMPLINK connect to DOGBERT identified by CISCO123 using HR_DB; ECOMMERCE_DB HR_DB dogbert’s schema dogbert’s schema EMPLINK

14. Java security in Oracle • System classes loaded by default, accessible & shared by all sessions wally session dilbert session com.cisco.ipc.* com.cisco.myapp.calc Java server classes (common, read-only) oracle.aurora.* oracle.jdbc.* java.*

15. Java security in Oracle • System classes loaded in shared area • Users can load classes • Into their own schema/session • Can grant execution rights to other users • Permissions • Stored in Oracle objects, not files • Stored in PolicyTable table • Granted by DBA or JAVA_ADMIN roles • “call dbms_java.grant_permission( “mnystrom”, “java.util.SocketPermission”, “localhost:1024-”, “connect”) • 2 privilege models • Invoker’s rights • Definer’s rights (setuid)

16. salary salary Invoker’s rights dogbert’s schema alice’s schema com.cisco.ipc.* com.cisco.myapp.calc

17. salary salary Definer’s rights dogbert’s schema alice’s schema com.cisco.ipc.* com.cisco.myapp.calc

18. Access beyond the database • Languages: PL/SQL or Java • Techniques: Stored procs or functions • Examples • Execute, read, write local files • Make and receive network calls (HTTP, MMX, etc.) • Access data in remote databases • Send mail Database server Database /oracle/apps/

19. Auditing • Obviously impacts database performance • Writes high-level info to a common table • Database user • Object (table, role, etc.) • Action (select, insert, etc.) • Date/time • Currently enabled on-request to DBA team • Difficult to trace actions to a live human • Can correlate with IP address

20. Common integration techniques • Shared database schemas • Separate schemas/dbs • Grant direct access to each other’s schemas • Grant only stored proc access

21. orders customers Typical modern application application application schema

22. orders customers Shared schemas Application #1 Application #2 insert update delete select grant select insert update select application #2’s schema

23. orders customers Shared objects Application #1 Application #2 insert update delete select grant select insert update select Application #1’s schema Application #2’s schema

24. stored procedure orders customers Shared, protected objects Application #1 Application #2 insert update delete select grant select insert update execute Application #1’s schema Application #2’s schema

25. orders customers Application-level integration • Shared libraries • MMX • Web services • IIOP Application #1 Application #2 insert update delete select grant select insert update Application #1’s schema Application #2’s schema

26. Current problems in industry • Account management • Passwords never changed • Accounts/passwords widely known • All developers • cgi-bin trees • CVS source repositories • Privileges too broad • No data stewardship • No segregation/special protection for sensitive data