Auditing Techniques for Oracle Database 11g

1. Carl Dudley – University of Wolverhampton Carl Dudley University of Wolverhampton, UK UKOUG Committee Oracle ACE Director carl.dudley@wlv.ac.uk Auditing Techniques for Oracle Database 11g

2. Working with Oracle since 1986 Oracle DBA - OCP Oracle7, 8, 9, 10 Oracle DBA of the Year – 2002 Oracle ACE Director Regular Presenter at Oracle Conferences Consultant and Trainer Technical Editor for a number of Oracle texts UK Oracle User Group Director Member of IOUC Day job – University of Wolverhampton, UK Auditing Techniques for Oracle Database 11g Auditing Overview Application Auditing Trigger-based Auditing Auditing the sys User Standard Auditing Fine-Grained Auditing Managing the Audit Trail Auditing Recommendations Working with Oracle since 1986 Oracle DBA - OCP Oracle7, 8, 9, 10 Oracle DBA of the Year – 2002 Oracle ACE Director Regular Presenter at Oracle Conferences Consultant and Trainer Technical Editor for a number of Oracle texts UK Oracle User Group Director Member of IOUC Day job – University of Wolverhampton, UK

3. Security – Main facets • Authentication / Identification • Who are you • Authorisation • What you can do/see • Auditing • What you did • The what, when, who, where and how • Security should be database-centric, not application centric • There is now a focus on the database • A well known adage about software development “You can have it done right, you can have it done fast, you can have it done cheap Pick any two” • A possible adage about database security “You can have high performance, high security, high usability Pick any one”

4. Auditing Overview • What to audit • How to audit • How to use audit records • Handling performance issues • Auditing can also show work patterns, frequency of use etc. • Auditing allows you to know when you have been robbed and by whom • Data can be 'stolen' without anyone knowing • Perform selective auditing • Blanket auditing can have a negative performance effect • Also produces massive, difficult to handle, audit trails • Last phase in security cycle – never dispense with it

5. Auditing Techniques for Oracle Database 11g Auditing Overview Application Auditing Trigger-based Auditing Auditing the sys User Standard Auditing Fine-Grained Auditing Managing the Audit Trail Auditing Recommendations

6. Application Auditing • Programmed into an application • Often implemented in third party applications • Often done for portability across DBMSs, or when database auditing is not well understood • All aspects can be audited • Extremely flexible and extensible • Maintenance of the code can be onerous • Big applications are often targets for hackers • Application can be bypassed rendering auditing useless • It’s application centric

7. Application Auditing Example • Create a table (aud_emp) designed to capture audit information for the emp_under_audit table CREATE TABLE emp_under_audit AS SELECT * FROM empcopy; CREATE TABLE aud_emp ( username VARCHAR2(30) ,action VARCHAR2(12) ,empno NUMBER(4), ,column_name VARCHAR2(255) ,call_stack VARCHAR2(4000) ,client_id VARCHAR2(255) ,old_value VARCHAR2(25) ,new_value VARCHAR2(25) ,action_date DATE);

8. Application Auditing Example (continued) • Create a procedure to generate auditing information CREATE OR REPLACE PROCEDURE proc_audit_emp ( pi_username IN VARCHAR2 ,pi_action IN VARCHAR2 ,pi_empno IN NUMBER ,pi_column_name IN VARCHAR2 ,pi_old_value IN VARCHAR2 ,pi_new_value IN VARCHAR2) AS BEGIN INSERT INTO aud_emp (username,action,empno,column_name,call_stack, client_id,old_value,new_value,action_date) VALUES (pi_username ,pi_action ,pi_empno ,pi_column_name ,dbms_utility.format_call_stack ,sys_context('userenv','client_identifier') ,pi_old_value ,pi_new_value ,sysdate); END;

9. Application Auditing Example (continued) • Create a procedure to show and format the auditing information CREATE OR REPLACE PROCEDURE proc_format_aud_emp AS BEGIN FOR r IN (SELECT * FROM aud_emp ORDER BY action_date DESC) LOOP dbms_output.put_line('User: '||r.username); dbms_output.put_line('Client ID: '||r.client_id); dbms_output.put_line('Action: '||r.action); dbms_output.put_line('Empno: '||r.empno); dbms_output.put_line('Column: '||r.column_name); dbms_output.put_line('Old Value: '||r.old_value); dbms_output.put_line('New Value: '||r.new_value); dbms_output.put_line('Date: '|| TO_CHAR(r.action_date,'MON-DD-YYYY HH24:MI')); END LOOP; END;

10. Application Auditing Example (continued) • Create an application procedure that is audited CREATE OR REPLACE PROCEDURE proc_update_sal( pi_empno IN NUMBER, pi_salary IN NUMBER) AS v_old_sal VARCHAR2(25); BEGIN SELECT sal INTO v_old_sal FROM emp_under_audit WHERE empno = p_empno FOR UPDATE; UPDATE emp_under_audit SET sal = pi_salary WHERE empno = pi_empno; proc_audit_emp (pi_username => user ,pi_action => 'UPDATE' ,pi_empno => pi_empno ,pi_column_name => 'SAL' ,pi_old_value => v_old_sal ,pi_new_value => pi_salary); END; /

11. Application Auditing Example (continued) • Run application, executing the update procedure and auditing the changes BEGIN proc_update_sal(p_empno => 7369,p_salary => 950); proc_format_aud_emp; END; / • Show the resultant call stack SELECT username,call_stack FROM aud_emp; USERNAME CALL_STACK -------- ------------------------------------- SCOTT ----- PL/SQL Call Stack ----- object line object handle number name 664B1434 1 anonymous block 6A1DFC34 10 procedure SCOTT.PROC_AUDIT_EMP 66614FA0 11 procedure SCOTT.PROC_UPDATE_SAL 6651D620 2 anonymous block

12. Application Auditing Example (continued) • Show the captured audit information • The Client ID returns the IP_address if user was remote • Could capture much more about the user context BEGIN proc_format_aud_emp; END; / User: SMITH Client ID: 127.0.0.1 Action: UPDATE Empno: 7369 Column: SAL Old Value: 800 New Value: 950 Date: SEP-07-2012 18:37

13. Auditing Techniques for Oracle Database 11g Auditing Overview Application Auditing Trigger-based Auditing Auditing the sys User Standard Auditing Fine-Grained Auditing Managing the Audit Trail Auditing Recommendations

14. Trigger-based Auditing • Database centric – very popular • Sometimes called value-based auditing • Can be used on INSERT, UPDATE, DELETE events (but not SELECTs) • Transparent to all applications • Flexible and extensible • Do not always fire • Do not fire on TRUNCATE • Cannot receive parameters – restricted to column values • Need to be created for each and every object • Could call common procedures

15. Trigger-based Auditing – Simple Example • Capture salary changes, who made the change and when using a simple trigger CREATE TRIGGER trg_a_idu_r_emp_sal AFTER INSERT OR DELETE OR UPDATE OF sal ON emp FOR EACH ROW BEGIN IF (:NEW.sal > :OLD.sal * 1.10) THEN INSERT INTO emp_sal_audit VALUES (:OLD.empno ,:OLD.sal ,:NEW.sal ,user ,sysdate); END IF; END; / • Triggers cannot capture the triggering statement • Cannot be used to define alert actions • Fine-Grained auditing may be a better option

16. Trigger to Populate Audit table • Trigger fires on updates of sal • Executes proc_audit_emp to populate the pre-constructed audit table (aud_emp) CREATE OR REPLACE TRIGGER trg_b_u_r_emp_copy_sal BEFORE UPDATE OF sal ON emp_copy FOR EACH ROW DECLARE BEGIN proc_audit_emp (p_username => user, ,p_action => 'UPDATE' ,p_empno => :OLD.empno ,p_column_name => 'SAL' ,p_old_value => TO_CHAR(:OLD.sal) ,p_new_value => TO_CHAR(:NEW.sal)); END; /

17. Firing the Audit Trigger • Smith performs an update which fires the trigger SMITH> UPDATE scott.emp_copy 2 SET sal = sal*1.1 3 WHERE job = 'ANALYST'; • The call stack shows the trigger firing SCOTT> SELECT DISTINCT call_stack FROM aud_emp; CALL_STACK ------------------------------------------------ ----- PL/SQL Call Stack ----- object line object handle number name 665C3F84 1 anonymous block 6675288C 10 procedure SCOTT.PROC_AUDIT_EMP 6A297C30 3 SCOTT.TRG_B_U_R_EMP_COPY_SAL

18. The Triggered Audit Records • The auditing information shows two records suffering update SCOTT> BEGIN 2 proc_format_aud_emp; 3 END; 4 / User: SMITH Client ID: Action: UPDATE Empno: 7902 Column: SAL Old Value: 3000 New Value: 3300 Date: SEP-07-2012 19:37 User: SMITH Client ID: Action: UPDATE Empno: 7788 Column: SAL Old Value: 3000 New Value: 3300 Date: SEP-07-2012 19:37

19. Handling Rollback - Autonomous Transactions • Scenario • User makes an update, inspects values and then rolls back the transaction • Records in the auditing table will also be rolled back • Loss of auditing information • Cannot place COMMIT in the trigger • But can use Autonomous Transactions • Allows actions of triggers to commit independently of the triggering statement • Preserves the auditing information on rollback

20. Handling Rollback – Autonomous Transactions (continued) • All updates will be audited CREATE OR REPLACE PROCEDURE proc_audit_emp ( p_username IN VARCHAR2 ,p_action IN VARCHAR2 ,p_empno IN NUMBER ,p_column_name IN VARCHAR2 ,p_old_value IN VARCHAR2 ,p_new_value IN VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO aud_emp (username,action,empno,column_name,call_stack, client_id,old_value,new_value,action_date) VALUES (p_username ,p_action ,p_empno ,p_column_name ,dbms_utility.format_call_stack ,sys_context('userenv','client_identifier') ,p_old_value ,p_new_value ,sysdate); COMMIT; END;

21. Auditing Techniques for Oracle Database 11g Auditing Overview Application Auditing Trigger-based Auditing Auditing the sys User Standard Auditing Fine-Grained Auditing Managing the Audit Trail Auditing Recommendations

22. Mandatory Database Auditing • Mandatory Auditing always records • Startup • Shutdown • User logins and logoffs with SYSDBA and SYSOPER privileges • Shows if an administrator has disabled auditing AUDIT_TRAIL = FALSE (or NONE) • Records must be stored in the operating system because database not available on starting or stopping • On Windows in the Event Logs • On Linux and unix in $ORACLE_HOME/rdbms/audit

23. Auditing the SYS User with AUDIT_SYS_OPERATIONS • Actions by users having SYSDBA or SYSOPER are written to OS files (XML as appropriate), not the database • All successful sys top-level SQL actions are audited • Can be seen in the Windows Event Viewer (not in aud$) • These database users should not have access to the audit records • The parameter is deliberately not dynamic • Database must be 'bounced' to change its value • Stops the DBA from simply turning off auditing, perform a malicious action and then turning auditing back on • Having to bounce the database captures the disabling of the auditing ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE SCOPE = SPFILE;

24. Example sys Audit CONNECT / AS SYSDBA ALTER SYSTEM FLUSH SHARED_POOL; UPDATE scott.emp SET sal=1000 WHERE ename='SCOTT'; • When sys auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the OS audit file or event log: Audit trail: LENGTH: '177' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[10] 'UNV\in8308' CLIENT TERMINAL:[14] 'WXPLT-ITR12680' STATUS:[1] '0' DBID:[10] '1318485259' . Audit trail: LENGTH: '201' ACTION :[30] 'ALTER SYSTEM FLUSH SHARED_POOL' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[10] 'UNV\in8308' CLIENT TERMINAL:[14] 'WXPLT-ITR12680' STATUS:[1] '0' DBID:[10] '1318485259' . Audit trail: LENGTH: '220' ACTION :[49] 'UPDATE scott.emp SET sal=1000 WHERE ename='SCOTT'' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[10] 'UNV\in8308' CLIENT TERMINAL:[14] 'WXPLT-ITR12680' STATUS:[1] '0' DBID:[10] '1318485259' .

25. Auditing Auditing Overview Application Auditing Trigger-based Auditing Auditing the sys User Standard Auditing Fine-Grained Auditing Managing the Audit Trail Auditing Recommendations

26. Values of AUDIT_TRAIL for Standard Auditing • The audit trail does not store data values

27. Setting Standard Auditing • The parameter is deliberately not dynamic • Database must be 'bounced' to change its value • Set this value on database creation to avoid a database 'bounce' later • If AUDIT_FILE_DEST is not specified, the default OS location is • Solaris $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump • Windows $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump • ALTER SYSTEM SET AUDIT_TRAIL = DB,EXTENDED SCOPE = SPFILE;

28. Scoping Audit Activity – Standard Auditing • Specific objects • Executing procedures • Use of a system privilege • Specific users • Successful and/or unsuccessful actions • Per action or per session (per session is not a realistic option on 11g) • Allows focussing of auditing activity • Important to fine tune this to avoid performance and storage issues • Allows monitoring of privileged users – DBAS etc.

29. Auditing Connections • Need to know who and when • Most outages are down to human activity • Not easy for users of applications using connection pools • Generates lots of records • Need adequate disk space and purging policy • To audit connections, two criteria must be set • Ensure AUDIT_TRAIL = DB,EXTENDED • When connected as the user system, issue the command • Audits connections only for scott and smith • Not much defence for not having this information • AUDIT SESSION; • AUDIT SESSION BY scott,smith;

30. Auditing Connections (continued) • Script to report on audit of user logons and logoffs BEGIN FOR r IN (SELECT username ,action_name ,TO_CHAR(timestamp, 'DD-MON HH24:MI') LOGON ,TO_CHAR(logoff_time, 'DD-MON HH24:MI') LOGOFF ,priv_used ,comment_text FROM dba_audit_trail) LOOP dbms_output.put_line('User: '||r.username); dbms_output.put_line('Action: '||r.action_name); dbms_output.put_line('Logon: '||r.LOGON); dbms_output.put_line('Logoff: '||r.LOGOFF); dbms_output.put_line('Priv: '||r.priv_used); dbms_output.put_line('Comments: '||r.comment_text); dbms_output.put_line('-----End of audit record-----'); END LOOP; END;

31. Audit Report Output • User scott has created a session and then exited almost immediately SYS>/ User: SCOTT Action: LOGON Logon: 12-SEP 09:24 Logoff: Priv: CREATE SESSION Comments: Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=169.254.207.135)(PORT=2817)) ------End of audit record------ PL/SQL procedure successfully completed. SYS> / User: SCOTT Action: LOGOFF Logon: 12-SEP 09:24 Logoff: 12-SEP 09:25 Priv: CREATE SESSION Comments: Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=169.254.207.135)(PORT=2817)) ---------End of audit record--------- User has yet to logoff • On Oracle11g you may see DBSNMP and SYSMAN activity

32. Statement Auditing • The use of any kind of SQL statement can be audited • Can be based on whether the statement is successful, unsuccessful or both • Examples of auditing based on statements affecting types of objects • Audits CREATE, ALTER, DROP of any role or table • Audits CREATE TABLE statements • Audits ALTER TABLE statements only when they are unsuccessful • AUDIT ROLE,TABLE; • AUDIT CREATE TABLE; • AUDIT ALTER TABLE WHENEVER NOT SUCCESSFUL;

33. Statement Auditing (continued) • Audit all unsuccessful SELECT, INSERT, DELETE statements on all tables and any unsuccessful attempt at executing a procedure • AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, • EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL; • Can be specified on a per user basis • AUDIT DELETE TABLE BY tt BY ACCESS;

34. Tracking Statement Auditing • Statement level auditing is shown in dba_stmt_audit_opts AUDIT CREATE EXTERNAL JOB BY tt; SELECT * FROM dba_stmt_audit_opts; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE --------- ---------- ------------------- ---------- ---------- : : : : DROP ANY TABLE BY SESSION BY SESSION CREATE EXTERNAL JOB BY SESSION BY SESSION TT CREATE EXTERNAL JOB BY SESSION BY SESSION : : : : NOAUDIT CREATE EXTERNAL JOB; SELECT * FROM dba_stmt_audit_opts; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE --------- ---------- ------------------- ---------- ---------- : : : : DROP ANY TABLE BY SESSION BY SESSION TT CREATE EXTERNAL JOB BY SESSION BY SESSION : : : :

35. Privilege-Based Auditing • Examples of auditing on types of privileges • Audits any successful or unsuccessful action that depends on the DELETE ANY TABLE privilege • Audits each unsuccessful use of the UPDATE ANY TABLE privilege • Default is BY SESSION • The AUDIT SYSTEM privilege is required by any user that sets up system or privilege-based auditing • This would normally be the security administrator and no-one else • AUDIT DELETE ANY TABLE; • AUDIT UPDATE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

36. Object-based Auditing • Object owners and administrators can set object-based auditing • AUDIT ANY allows auditing to be set on any object • Examples of auditing on specific objects • Audit successful attempts to query the emp table on a session basis • Audit all unsuccessful attempts to query scott'sdept table by access • AUDIT SELECT ON emp WHENEVER SUCCESSFUL; • AUDIT SELECT, INSERT, DELETE ON scott.dept • BY ACCESS WHENEVER NOT SUCCESSFUL;

37. Object-based Auditing (continued)

38. Object Level Auditing • Cannot be specified on a per user basis SELECT * FROM dba_obj_audit_opts; OBJECT_ OWNER OBJECT_NAME TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK ----- ----------- ------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- SCOTT EMP TABLE -/- -/- -/- -/S -/- -/- A/- -/- -/- A/A -/S -/- -/- -/- -/- -/- -/- AUDIT DELETE,UPDATE ON SCOTT.EMP WHENEVER NOT SUCCESSFUL; AUDIT SELECT ON SCOTT.EMP BY ACCESS; AUDIT INSERT ON SCOTT.EMP BY ACCESS WHENEVER SUCCESSFUL; AUDIT ALL ON dept; • Audits all possible options on the object NOAUDIT ALL ON dept; • Removes EVERY audit option on the object

39. BY SESSIONvsBY ACCESS • Originally, BY SESSION created one audit record for statements causing the same auditing activity • BY ACCESS creates audit record each time an auditable statement is run • On Oracle 11g BY SESSION causes auditing as many times as BY ACCESS but records less information • Still remains as default • Oracle recommend to use BY ACCESS • Similar overheads but more information

40. Monitoring Object Auditing S = by session A = by access - = no auditing • Status of objects being audited SELECT upd "Update Option" FROM dba_obj_audit_opts WHERE object_name IN ('DEPT','EMP'); OBJECT_NAME Update Option ----------- ------------- DEPT A/- EMP S/A First character shows if auditing is enabled for successful attempts Character after the '/' shows if auditing is enabled for unsuccessful attempts • Object auditing in audit trail SELECT ses_actions,returncode FROM dba_audit_object; SES_ACTIONS RETURNCODE ---------------- ---------- ---------F------ 913 ---F------------ 913 ---------S------ 0 ---S------------ 0 DELETE FROM emp WHERE empno = (SELECT * FROM emp WHERE ename = 'x'); ORA-00913: too many values DELETE FROM emp WHERE empno = (SELECT empno FROM emp WHERE ename = 'x'); select delete

41. Monitoring System Wide Auditing • Show auditing status of user logins with dba_stmt_audit_opts SELECT * FROM dba_stmt_audit_opts; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE --------- ---------- -------------- --------- --------- CREATE SESSION BY ACCESS BY ACCESS • Show auditing status of system privileges with dba_priv_audit_opts SELECT * FROM dba_priv_audit_opts; USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE --------- ---------- ---------------- --------- --------- SELECT ANY TABLE BY ACCESS BY ACCESS • The dba_common_audit_trail view shows all auditing information • Includes Fine-Grained Auditing

42. Auditing the Audit Trail • If ordinary users have access to sys.aud$ then that access needs to be audited • Any actions performed by non-SYSDBA users will now be audited • A simple SELECT on sys.aud$ will generate an audit record in aud$ • A DELETE of this audit record will succeed, but it will generate another record of the delete operation • Any records of DML performed on aud$ cannot be deleted by regular users • Setting up this type of auditing acts as a safety feature, potentially revealing unusual or unauthorized actions • AUDIT SELECT, INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

43. The Auditing Views STMT_AUDIT_OPTION_MAP Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time. AUDIT_ACTIONS Contains descriptions for audit trail action type codes ALL_DEF_AUDIT_OPTS Contains default object-auditing options to be applied on object creation DBA_STMT_AUDIT_OPTS Describes current system auditing options across system and by user DBA_PRIV_AUDIT_OPTS Describes current system privileges being audited across system and by user DBA_OBJ_AUDIT_OPTS Describes auditing options on all objects USER_OBJ_AUDIT_OPTS The USER view shows auditing options on all objects owned by current user DBA_AUDIT_TRAIL Lists all audit trail entries USER_AUDIT_TRAIL The USER view shows audit trail entries relating to current user. DBA_AUDIT_OBJECT Contains audit trail records for all objects in the system USER_AUDIT_OBJECT The USER view lists audit trail records for statements concerning objects that are accessible to the current user DBA_AUDIT_SESSION Lists all audit trail records concerning CONNECT and DISCONNECT USER_AUDIT_SESSION The USER view lists all audit trail records concerning connections and disconnections for the current user DBA_AUDIT_STATEMENT Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database USER_AUDIT_STATEMENT , or for the USER view, issued by the user. DBA_AUDIT_EXISTS Lists audit trail entries produced by AUDIT NOT EXISTS DBA_AUDIT_POLICIES Shows all the auditing policies on the system. DBA_FGA_AUDIT_TRAIL Lists audit trail records for value-based auditing DBA_COMMON_AUDIT_TRAIL Combines standard and fine-grained audit log records, and includes sys and mandatory audit records written in XML format

44. Setting up Audit Information • Trigger sets the client identifier for each session at login time CREATE OR REPLACE TRIGGER trg_set_client_info AFTER LOGON ON DATABASE DECLARE v_module v$session.module%TYPE; BEGIN SELECT module INTO v_module FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.audsid = USERENV('sessionid'); dbms_session.set_identifier(sys_context ('userenv','ip_address') ||' - '||v_module); END; / • Could set many other criteria such as authentication method

45. Auditing Statements • Example shows three SELECT statements that will generate audit records • The statement issued by system will generate three audit records CONN system/manager SELECT ename,sal FROM scott.emp WHERE sal < (SELECT sal FROM scott.emp WHERE ename = 'WARD') AND job = (SELECT job FROM scott.emp WHERE ename = 'WARD'); CONN scott/tiger SELECT job FROM scott.emp; SELECT empno FROM scott.emp;

46. Correlating Audit Records CREATE OR REPLACE PROCEDURE format_aud AS BEGIN FOR r IN (SELECT db_user ,client_id ,object_schema ,object_name ,extended_timestamp ,sql_text ,statementid FROM dba_common_audit_trail GROUP BY db_user ,statementid ,sql_text ,object_schema ,object_name ,client_id ,extended_timestamp ORDER BY extended_timestamp ASC) LOOP dbms_output.put_line('Who: '||r.db_user); dbms_output.put_line('What: '||r.object_schema||'.'||r.object_name); dbms_output.put_line('Where: '||r.client_id); dbms_output.put_line('When: ' ||TO_CHAR(r.extended_timestamp,'MON-DD HH24:MI')); dbms_output.put_line('How: '||r.sql_text); dbms_output.put_line('--------------End of audit record--------------'); END LOOP; END; The grouping of statementids will cause the system statement to show as one entry

47. Correlated Output from the Audit Trail • The system record shows only once • SQLPLUS is shown as part of the identifier (set by the trigger) Who: SYSTEM What: SCOTT.EMP Where: 127.0.0.1 - sqlplus.exe When: SEP-2012 11:15 How: SELECT ename,sal FROM scott.emp WHERE sal < (SELECT sal FROM scott.emp WHERE ename = 'WARD') AND job = (SELECT job FROM scott.emp WHERE ename = 'WARD') --------------End of audit Record-------------- Who: SCOTT What: SCOTT.EMP Where: 127.0.0.1 - sqlplus.exe When: SEP-2012 11:15 How: SELECT job FROM scott.emp --------------End of audit Record-------------- Who: SCOTT What: SCOTT.EMP Where: 127.0.0.1 - sqlplus.exe When: SEP-2012 11:15 How: SELECT ename FROM scott.emp; The SELECT statement is captured because AUDIT_TRAIL = DB,EXTENDED

48. Performance Impact of Audit • With auditing CREATE OR REPLACE PROCEDURE perf_test_aud AS BEGIN FOR rec IN 1..50000 LOOP FOR inner_rec IN (SELECT ename FROM scott.emp) LOOP NULL; END LOOP; END LOOP; END; / EXEC perf_test_aud Elapsed : 39.93 seconds

49. Performance Impact of Audit (continued) • Disable auditing and then re-execute • NOAUDIT SELECT ON scott.emp; CREATE OR REPLACE PROCEDURE perf_test_aud AS BEGIN FOR rec IN 1 ..50000 LOOP FOR inner_rec IN (SELECT ename FROM scott.emp) LOOP NULL; END LOOP; END LOOP; END; / EXEC perf_test_aud Elapsed : 12.26 seconds

50. Auditing Limitations • Not able to show what data the user actually saw • But captures SCN of the operation • Could use Flashback based on the SCN to see the data • Depends on UNDO_RETENTION • Oracle Consultancy have Selective Audit as a 'full' solution • Audit data persists after a rollback • Audit is generated even when no rows affected • Cannot audit on specific columns or conditions • Fine-grained auditing gives extra possibilities