Supporting an 11i SOX Audit in a DBA Environment

1. DBA Tasks Supporting a SOX Audit of an 11i Environment Brian HitchcockOCP 10g DBA Sun Microsystems brian.hitchcock@sun.com brhora@aol.com Brian Hitchcock November 3, 2007 www.brianhitchcock.net Page 1

2. Presentation is Available

3. SOX Audit • SOX Auditors • Have a job to do • Making you happy isn’t one of them • Are not Oracle DBAs • Are not Oracle Applications DBAs • Have to audit many different applications • No two work the same way • Have to get something done • Audit may not be perfect…

4. DBA and the SOX Audit • DBA • Can make things difficult for the auditor • I am a genius (Guilty Spark 343) • This is how we do things here • That’s a stupid question • I don’t have time for this • Can make themselves invaluable • Here is what I know • This is my understanding of how Oracle works • (WOW) Way Oracle Works

5. Auditors Want to Know • Who can update data • I’m also concerned about who can read data • Company financial data is not public • Who can access passwords in files • Typical application has password file(s) • Application reads file(s) to setup db access • This is really the first issue in disguise… • These are reasonable questions • “Were it so easy” (Arbiter) • Not easy to answer • Lots of details and exceptions

6. Theme • Started out with • “Who can update data?” • Turns into • “Do we know who can update data?” • We know the individuals that can update data • Link this back to approval process • Create and modify users • Link this back to the audit process

7. Theme • We offer auditors • How we know who can update data • How we know who should update data • How we maintain the answers to the first two • This is the practical answer to • “Who can update data?”

8. Users That Can Update Data • Oracle Applications Users • SYSADMIN • Application Responsibilities • Oracle Database Users • APPS, Select any table privilege • OS level Users • Remote Users • Database links • Users in Clones • Password Files

9. Who Can Update Data? Remote Users Production Oracle Applications Environment OS users OS Users Single Tier SQL*Plus SQL*Plus Password Files Application Tier Database Tier Database DbLinks Application Responsibilities Users Data Users Data Data Data Application Users Data Oracle Applications Clone Database user with select any table Passwords Not Changed --same as Production End-User Browser End-User Browser

10. Oracle Applications Users • Don’t have any direct access to db data • Apps access is through responsibilities • What is a responsibility? • How to link Apps user with db data access? • Apps does this through responsibility • Apps users access changes when DBA changes responsibilities assigned to Apps user • How to help auditors to understand this? • Document who has which responsibilities and why

11. Oracle Applications Users • Disabling Applications users • Can’t drop applications user once created • Do end-date, change passwords • Do not disable SYSADMIN, GUEST • Best Practices for Securing E-Business Suite • Page 23 shows users • Which can be disabled • Doc 189367.1

12. Oracle Applications User • Connects to Applications • Based on responsibilities assigned • Can access specific forms • Tied to specific data in tables • Database access done through APPS db user • APPS database user • Doesn’t own data tables • Has synonyms to schemas that own data tables • Auditors aren’t used to this architecture…

13. Active Applications Users • SQL to find • All applications users • Active users (no end-date) • Active users that have end-date in the future • SQL for reports • Same as above • But linked with user employee information • Identifies apps users not linked to a person • Audit against creation tickets and approvals

14. Active Applications Users SQL --Total of All Users Active and Inactive select count(*) from apps.fnd_user; 997 --Total of Active Users, no end date select count(*) from apps.fnd_user where end_date is null; 755 --Total of Inactive Users, end date has passed select count(*) from apps.fnd_user where end_date is not null and end_date < sysdate; 241 --Total of Active users with end date specified in the future select count(*) from apps.fnd_user where end_date is not null and end_date > sysdate; 1

15. Active Applications Users SQL --> report with employee information spool PROD_FND_USER_02062007.txt select sysdate from dual; select user_name, employee_id, end_date, email_address from apps.fnd_user order by employee_id; spool off spool PROD_end_date_NULL_02062007.txt select sysdate from dual; select user_name, employee_id, end_date, email_address from apps.fnd_user where end_date is null order by employee_id; spool off

16. Active Applications Users SQL spool PROD_end_date_NULL_full_name_02062007.txt select sysdate from dual; select U.user_name, H.full_name, U.email_address, U.employee_id, U.end_date from apps.fnd_user U, hr.per_all_people_f H where U.employee_id=H.person_id (+) and U.end_date (+) is NULL order by U.employee_id; spool off spool PROD_end_date_in_future_02062007.txt select sysdate from dual; select U.user_name, H.full_name, U.email_address, U.employee_id, U.end_date from apps.fnd_user U, hr.per_all_people_f H where U.employee_id=H.person_id (+) and U.end_date > sysdate order by U.employee_id; spool off

17. Can’t Drop Users • Oracle Applications • Can’t remove a user once created • Can only end-date the user • Can end-date all the user’s responsibilities • Issues • Users can be un-end-dated • Who can do this? • Any apps user with System Administrator responsibility • Need to audit routinely • Change user password and end-date • Prevent access even if someone else un-end-dates

18. Diagram – APPS users access GL Schema APPS Schema Views Tables AR Schema Triggers Packages Tables Synonyms Synonyms Runtime usage of Oracle Applications is through APPS schema only. Users do not connect directly to product schemas such as GL or AR.

19. Diagram of Apps user connecting End-User connect APPLSYSPUB connect APPS GL AR HR …

20. SYSADMIN • Oracle Applications user • Created by default when installed • Has System Administrator responsibility • Can do anything in Oracle Applications • SYSADMIN isn’t the problem • Can lock down this user login • change password • It is the responsibility that matters • May have been assigned to other Apps users • Audit?

21. SYSADMIN • When environment first installed • Developers had SYSADMIN password • Sad, but true – truly bad management • After go-live • All DBAs shared SYSADMIN login • SYSADMIN password changed ‘mysteriously’ • All DBAs locked out • If anyone failed 3 logins (password failure limit) • All DBAs locked out • How to recover?

22. SYSADMIN • Switch to each DBA has individual account • BH1234 with System Administrator responsibility • No further password issues • If any DBA has 3 login failures • Any other DBA can reset password • Only one DBA locked out • More robust support process • Can audit DBA activity to an individual

23. Oracle Database Users Already discussed APPS database user • For every database schema • Associated database user • GL db schema • Db user can update everything in associated schema • Auditors looked at list of db users • Two letter usernames must be end-users • GL – George Lucas? • This is how he paid for the Death Star? • Updating GL in systems in a far away galaxy? • Auditors need your help to understand • Oracle Applications is different…

24. Oracle Database Users • Auditors • Wanted to drop all of these ‘end-users’ • Oracle Applications won’t work • How to resolve this? • Identify db users needed for • Oracle Applications • Oracle Database • Other • Show auditors how we control all types of users

25. Oracle Database Users • Oracle Applications Database Users • Multiple types of database schemas • exist in every db, Applications or not • used by shared Apps components • APPS, APPLSYS, APPLSYSPUB • provided by individuals Apps products • GL, AP, HR etc. • optional db features, end users • CTXSYS, SCOTT, BH1234

26. Oracle Database Users • Best Practices for Securing Oracle E-Business Suite, Version 3.0.5 • Metalink 189367.1 • Appendix C, page 51, Database Schemas Shipped with E-Business Suite • lists schemas, which schema passwords can be changed by FNDCPASS • which schema passwords should not be changed • details on how to change password for the various db users

27. Oracle Database Users • Oracle Database Security Checklist, January 2007, Appendix A • 10g Enterprise Edition Default Accounts • locked at install if you used Database Creation Assistant (DBCA) • not locked if db created manually • Appendix B, C, D for Oracle 9iR2, 9iR1, 8i respectively

28. Database Users Script • Document all db users • Ties back to Oracle documents • Auditors like this • Repeatable process • Identify why users exist • Identify end-users • Audit specific privileges

29. Database Users Script ####--> Create table and populate... #### ####sqlplus / set pagesize 1500 set linesize 200 alter session set nls_date_format = 'DD-Mon-YYYY hh24:mi:ss'; ####WHY_CREATED ####Apps Module -- for specific Oracle Applications products ####Database -- created when Oracle database installed ####Apps Db -- created when database installed for Oracle Applications ####Discoverer -- Oracle Discoverer End User Layer ####Database Admin -- DBA account ####--> create table create table SOX_dbuser_doc (username varchar2(20), user_id number, created date, account_status varchar2(32), Why_Created varchar2(15));

30. Database Users Script ####--> insert data for all existing db users insert into SOX_dbuser_doc (username, user_id, account_status, created) select username, user_id, account_status, created from dba_users order by created; ####--> update db users that are Applications Module schema owners update SOX_dbuser_doc set Why_Created='Apps Module' where username in (select oracle_username from apps.FND_ORACLE_USERID); ####update Database ####--> db users created when Oracle database installed or optional ####products installed ####--> from Appendix A - Oracle Database 10G Release 1 and Release 2 Enterprise ####Edition Default Accounts and their status, from Oracle Database Security Checklist ####January 2007. update SOX_dbuser_doc set Why_Created='Database' where username in ();

31. Database Users Script update SOX_dbuser_doc set Why_Created='Discoverer' where username in ('EUL4_US','EUL_US'); update SOX_dbuser_doc set Why_Created='Database Admin' where username in ('OPS$ORACLE', 'DBA_PROBE', 'STBY_PROBE'); ####--> db users created for Oracle Applications in addition to Apps modules whose database ####accounts are stored in the database table FND_ORACLE_USERID ####--> from Appendix C - Database Schemas Shipped with E-Business Suite, from Best ####Practices for Securing Oracle E-Business Suite, Version 3.0.4, Metalink Note 189367.1 update SOX_dbuser_doc set Why_Created='Apps Db' where username in (); ####--> Generate report sqlplus / set pagesize 1500 set linesize 200 alter session set nls_date_format = 'DD-Mon-YYYY hh24:mi:ss'; select * from SOX_dbuser_doc order by created;

32. Select any Table • Why is this granted • User wants ‘read-only’ access • Hit the easy button • Why is this a bad thing? • User can see data in all tables • Violates principle of Least Privilege • Only grant what is really needed and approved • How is this related to updating data?

33. Select any Table • User can select from FND_USER • Can see encrypted user passwords • For Oracle Applications users • FND_ORACLE_USERID isn’t good either • Encrypted db schema passwords • Well known issues with Oracle passwords • Doc 340240.1 Assessment of Oracle Password Hashing • Users can find methods to crack passwords • Take passwords to another Apps installation • APPS user can update any/all data • As well as view confidential data

34. Select any Table • But I know I didn’t grant this… • You have to check your databases • On-site consultant granted this to our developers • Why? • Because “they needed it” • Regular audit for ‘select any table’ grants • See documents list for further issues • Other tables, privileges that should not be granted

35. Cracking Passwords • I’m not suggesting you do this • But if I can find out how it’s done • Your users can figure it out as well • If we trusted everyone • No need for SOX • No need for security audit • Assume someone will try to crack passwords • Auditors won’t know about this • You have to explain it to them • Have plan to explain how you will deal with this issue

36. Select any Table • Release 12 (and RUP6) may be better • Doc 457166.1 Non-Reversible Hash Password • Database users • Dba_users • Encrypted passwords visible • Not as bad as FND_USER • Once user has encrypted passwords • Can send elsewhere for cracking • Why takes this chance?

37. OS Users • Database user oracle identified externally • Granted DBA_ROLE • Used by DBAs • Connect using SQL*Plus • Sqlplus / as sysdba • OS user ‘oracle’ connects without db password • Then has DBA_ROLE • Anyone with ‘oracle’ password • Can connect from anywhere on your network • Can’t audit who this person is

38. Remote Users • Connecting from other servers • Database links • Created in remote database • Won’t show up in audit of local database • Security relies on protecting passwords • Managed SQL*Net Access • Invited nodes (more later…) • Most remote servers locked out • Access limited to small number of remote servers • Ideally, only the 11i apps tier server

39. Users in Clones • Production environment • Others created as clones of prod • Cloning process • Brings production db/apps user passwords • Doesn’t change passwords automatically • If DBA doesn’t change passwords • They can be the same in dev as in production • Dev users may not know it • But they have prod access, can update prod data • Doc 419475.1 • Not just user passwords…

40. Password Files • Auditors concerned • Who can access passwords in files • Many applications store passwords in files • Oracle Applications • No, users don’t access passwords in a file • Yes, passwords may be in some files • No for applications users connecting • Log files may have passwords

41. Password Files • Many Apps DBA tasks • Require APPS and SYSTEM password • Appears on screen • Adadmin, adpatch • Email screen output, save to file • Passwords in email, in files on disk • Are those emails, files properly secured? • Send output to Oracle Support • Can they see your passwords?

42. What Would Sgt. Johnson Do? • Let’s “get tactical” • Practical steps • Oracle Applications Users • Oracle Database Users • OS Users • Remote Users • Users in Clones • Password Files

43. Applications Users • Who approved each of them? • Who approved their responsibilities • In Oracle Applications • Who approves changes to responsibilities? • Separate organization maintains • Master list of all approved users/responsibilities • Each user created in a separate ticket • Responsibilities modified for one user in a separate ticket

44. Report on Apps Users • Active Users • Active Users Concurrent Request • SQL presented earlier • Active Responsibilities • Concurrent Request(s) • Tedious, each report requires entering responsibility • Users that shouldn’t have access • Left company for example • End-date • Change password

45. System Administrator • Find all apps users that have • System Administrator responsibility • Revoke from apps users except for • DBA users • SYSADMIN • change password

46. Database Users • SQL, generate reports • Multiple types of database schemas • exist in every db, Apps or not • used by shared Apps components • APPS, APPLSYS, APPLSYSPUB • provided by individuals Apps products • GL, AP, HR etc. • optional db features, end users • CTXSYS, SCOTT, BH1234 • Change passwords • RUP4, FNDCPASS, change all apps users at once • Prevents database link issues from remote systems

47. Database Users • Database end-users • Not needed for Applications or database • Examine each user • Database privileges • Dba_role_privs • Dba_sys_privs • Dba_tab_privs • Roles granted • Examine privileges of each role

48. Database Users • SQL to examine each user • What to look for • Non-standard role (SUN_SELECT) • What privileges does this role have? • Who else has this role • Examine these users • Access to FND_USER • Other system tables • Who has “select any table” • Revoke select any table from all non-dba users

49. Database Users • Get specific approval for each • And all of their db privileges • Watch for users created before go-live • Used by developers • Everyone has the password • Was granted full privileges on all db objects accessible by APPS • Developers ran a script while they had access

50. Database User With Grants • How to deal with existing user • Has access to all db objects • Granted when developers had full access • Developers don’t know what they need • “everything” • They don’t know what their code access