Oracle Auditing

1. Oracle Auditing Natalka Roshak Presented to ABCD-Oracle 4 March 2004

2. About me • Oracle and Sybase Database Administrator, Analyst and Architect • Experienced database programmer • Oracle Certified Professional (OCP) • Regular columnist for Oracle trade magazine, orafaq.com, and other trade publications • Oracle consultant, serving customers across North America • Available for consulting engagements • http://rdbms-insight.com Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

3. About this presentation • Presented to Oracle users’ group at Harvard University, ABCD-Oracle • 4 March 2004 Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

4. Auditing 2 main types of auditing: • Oracle-supplied auditing using AUD$ • Trigger-based DML auditing Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

5. Oracle vs. Trigger-based Auditing Different scope & application: • AUD$ does not record any of the data values involved in a DML change; need to use trigger-based auditing to capture this info • Obviously, Oracle auditing allows auditing of non-DML statements, eg. ALTER ROLE Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

6. Oracle Auditing • Overview of Oracle Auditing • Fun with AUDIT SESSION • Security considerations & AUD$ size Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

7. I. Overview of Oracle Auditing

8. Enabling Oracle Auditing First step: set static initialization parameter AUDIT_TRAIL in INIT.ORA Values: • DB/TRUE • OS • NONE/FALSE Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

9. OS Audit Trail Even if OS audit trail is not enabled, Oracle will still write default actions to OS audit trail: • instance startup • instance shutdown • connections to the database as SYSOPER or SYSDBA Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

10. OS Audit Trail Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

11. DB trail • Writes all audit information to SYS.AUD$ • AUD$ installed by catalog.sql • Create auditing data dictionary views by running $oracle_home\rdbms\admin\cataudit.sql • Remove these views using catnoaud.sql Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

12. SYS.AUD$ • SQL> desc aud$ • Name Null? Type • ----------------------------------------- -------- -------------- • SESSIONID NOT NULL NUMBER • ENTRYID NOT NULL NUMBER • STATEMENT NOT NULL NUMBER • TIMESTAMP# NOT NULL DATE • USERID VARCHAR2(30) • USERHOST VARCHAR2(128) • TERMINAL VARCHAR2(255) • ACTION# NOT NULL NUMBER • RETURNCODE NOT NULL NUMBER • OBJ$CREATOR VARCHAR2(30) • OBJ$NAME VARCHAR2(128) • AUTH$PRIVILEGES VARCHAR2(16) • AUTH$GRANTEE VARCHAR2(30) • NEW$OWNER VARCHAR2(30) • NEW$NAME VARCHAR2(128) • SES$ACTIONS VARCHAR2(19) • SES$TID NUMBER • LOGOFF$LREAD NUMBER • LOGOFF$PREAD NUMBER • LOGOFF$LWRITE NUMBER • LOGOFF$DEAD NUMBER • LOGOFF$TIME DATE • COMMENT$TEXT VARCHAR2(4000) • SPARE1 VARCHAR2(255) • SPARE2 NUMBER • OBJ$LABEL RAW(255) • SES$LABEL RAW(255) • PRIV$USED NUMBER Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

13. Audit Views STMT_AUDIT_OPTION_MAP Contains information about auditing option type codes. AUDIT_ACTIONS Contains descriptions for audit trail action type codes. ALL_DEF_AUDIT_OPTS Contains default object-auditing options that will be applied when objects are created. DBA_STMT_AUDIT_OPTS Describes current system auditing options across the system and by user. DBA_PRIV_AUDIT_OPTS Describes current system privileges being audited across the system and by user. DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS Describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user. DBA_AUDIT_TRAIL, USER_AUDIT_TRAIL Lists all audit trail entries. USER view shows audit trail entries relating to current user. DBA_AUDIT_OBJECT,USER_AUDIT_OBJECT Contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects that are accessible to the current user. DBA_AUDIT_SESSION, USER_AUDIT_SESSION Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user. DBA_AUDIT_STATEMENT, USER_AUDIT_STATEMENT Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user. DBA_AUDIT_EXISTS Lists audit trail entries produced by AUDIT EXISTS and AUDIT NOT EXISTS. Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

14. 3 levels of audit options Statement • Audits on the type of SQL statement used, such as any SQL statement on a table (which records each CREATE, TRUNCATE, and DROP TABLE statement) Object • Audits specific statements on specific objects, such as ALTER TABLE on the EMP table Privilege • Audits use of a particular system privilege, such as CREATE TABLE Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

15. Statement Auditing • Eg. AUDIT SELECT BY SCOTT audits all select statements performed by SCOTT • AUDIT SELECT TABLE, UPDATE TABLE BY SCOTT, BLAKE; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

16. Object Auditing • Eg. AUDIT SELECT ON scott.emp; • AUDIT SELECT ON scott.emp WHENEVER NOT SUCCESSFUL; • AUDIT SELECT ON scott.emp WHENEVER SUCCESSFUL; • AUDIT ALL ON scott.emp ; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

17. Privilege Auditing eg. AUDIT GRANT SEQUENCE audits any statements of the type: • GRANT privilege ON sequence ; • REVOKE privilege ON sequence ; AUDIT EXECUTE PROCEDURE audits • CALL of any procedure AUDIT SELECT TABLE audits • SELECT FROM table/view/materializedview; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

18. Privilege auditing, con’t AUDIT INDEX audits any statements of the type: • CREATE INDEX • ALTER INDEX • DROP INDEX AUDIT NOT EXISTS audits all SQL stmts that fail because an object doesn’t exist AUDIT SYSTEM AUDIT audits all AUDIT/NOAUDIT statments *AUDIT SESSION audits logon/logoff Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

19. BY SESSION/ACCESS • BY SESSION: One audit record is inserted for one session, regardless of the number of times the statement is executed • BY ACCESS: One audit record is inserted each time the statement is executed • Default is BY SESSION Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

20. Further examples • AUDIT SESSION BY JOHN, ALEX WHENEVER NOT SUCCESSFUL; • AUDIT UPDATE, DELETE ON scott.emp BY ACCESS; • NOAUDIT UPDATE, DELETE ON scott.emp; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

21. II. Fun with AUDIT SESSION

22. Fun with AUDIT SESSION • Underappreciated fact: AUD$ records IP when session auditing is enabled • SQL> select timestamp#, userid, machine, action#, returncode, logoff$time, comment$text from aud$ where action# in (100,101); • TIMESTAMP USERID MACHINE ACTION# RETURNCODE LOGOFF$TI COMMENT$TEXT • --------- ---------- --------------- ------- ---------- ------- • 02-FEB-04 EREQ_USER GYPSY 101 0 02-FEB-04 Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.232.23)(PORT=3406)) • 02-FEB-04 EREQ_USER GYPSY 100 0 Authenticated by: DATABASE; Client address: • (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.232.23)(PORT=3427)) • 02-FEB-04 EREQ_USER GYPSY 101 0 02-FEB-04 Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.232.23)(PORT=3432)) • 02-FEB-04 BULKLOAD SHALLOT 100 0 Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=140.247.164.242)(PORT=2544)) Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

23. AUD$ fields For 100/101 events (ACTION# IN (100,101)): • col SPARE1 = OS username • col RETURNCODE = ora-xxxx returncode • col TERMINAL = terminal name (eg. ttyp3 in UNIX, machine name in Windows) • col COMMENT$TEXT = IP and port of client -- very useful! Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

24. Extracting the IP select userid, terminal, spare1, substr(s1,1,instr(s1,')')-1) IP, returncode, timestamp# from (select A.*, substr(comment$text,instr(comment$text,'HOST=')+5,100) s1 from aud$ a where action# in (100,101) ) order by IP; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

25. Sample output USERID TERMINAL SPARE1 IP RETURNCODE TIMESTAMP# ------------------------------------------------------------------------------------------- HERS MARS apacheco 128.103.231.86 0 02/24/2004 3:08:36 PM HERS oracle 128.103.231.86 0 01/25/2004 11:14:11 AM EREQ_USER MRUTENBUR mrutenburg 140.247.10.130 0 01/30/2004 4:42:52 PM BULKLOAD CJTRU cjtru 140.247.10.132 0 02/05/2004 4:26:41 PM BULKLOAD ttyp3 oracle 140.247.10.132 0 02/09/2004 10:42:13 AM SYS ttyp3 oracle 140.247.10.132 0 02/09/2004 11:55:56 AM BULKLOAD ROBIN michael 140.247.10.135 0 01/29/2004 7:59:49 AM SYSTEM JB Jen?Braster 140.247.10.135 0 01/28/2004 8:34:31 PM EREQ_USER JB Jen?Braster 140.247.10.135 0 01/28/2004 8:39:07 PM Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

26. Returncodes • Indicates ora-XXXX if logon failed • Common returncodes: • 0 – successful • 1017 – invalid username/password • 1005 – null password given • 1035 – RESTRICTED SESSION enabled • Eg. Add “where” clause to query to filter on ora-1017s Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

27. Other possibilities • Create table of known IPs expected to connect, query for other IPs • Alert when there’s a connection attempt from a new or non-Harvard IP • Create table of known terminals expected to connect over VPN (140.247.10/11.0/255), query for other terminals (or terminal & username combo) connecting over VPN Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

28. Filtering on known IPs/terminals select*from( select userid, terminal, spare1, substr(s1,1,instr(s1,')')-1) IP, k.hostname, str.returncode, count(*)from (select A.*, substr(comment$text,instr(comment$text,'HOST=')+5,100) s1 from aud$ a ) str,system.known_ips k where1=1 and(substr(s1,1,instr(s1,')')-1)notin(select ip fromsystem.known_ips) andNOT((substr(s1,1,instr(s1,')')-1)like'140.247.10%' OR substr(s1,1,instr(s1,')')-1)like'140.247.11%') AND str.terminal in(select terminal fromsystem.known_terminals) ) ) and str.terminal NOTin(select terminal fromsystem.known_terminals) and substr(s1,1,instr(s1,')')-1)=k.ip(+) and timestamp#>sysdate-20 groupby userid, terminal, spare1, substr(s1,1,instr(s1,')')-1), hostname, returncode )where IP notlike'140.247.164.%'and IP notin('140.247.232.22','140.247.232.23') orderby IP Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

29. Filtering on known IPs/terminals USERID TERMINAL SPARE1 IP RETURNCOD COUNT(*) ------------------------------------------------------------------------------ SYS unknown n_user 140.247.10.149 0 1 UTILITY LOKI dfaux 140.247.10.150 0 1 BULKLOAD CLIENT1 kyu 140.247.11.20 0 3 SYS ttyp2 oracle 140.247.11.20 0 1 SYS ttyp2 oracle 140.247.11.20 1017 1 ASPERIN CLIENT1 kyu 140.247.11.9 0 5 HR_DATA CLIENT1 kyu 140.247.11.9 0 8 FIN_OFFIC UHALL-TEMP5 hadass 140.247.70.24 0 7 FIN_OFFIC IBM-5F0826A1BDE nasson 140.247.70.28 0 5 FIN_OFFIC IBM-5F0826A1BDE nasson 140.247.70.28 1017 4 Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

30. III. Security Considerations

31. Too Much AUD$ • Problem: AUD$ is located in SYSTEM tablespace • If AUDIT SESSION is enabled, DOS attack can fill up SYSTEM tablespace, bringing database to a complete halt • An untended AUD$ can do the same thing over time • Solution: Monitor size of AUD$ Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

32. MANAGE_AUD$ package • Job to check size of AUD$ and move rows out of SYS tablespace if AUD$ too large • Write to alert log if AUD$ has to be cleaned out • Procedures for quarterly log rotation Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

33. Create tables to archive AUD$ • SYSTEM.AUD$_BU • SYSTEM.AUD$_PREV_QUARTER drop table system.aud$_bu; create table system.aud$_bu tablespace tools as (select * from sys.aud$ where 1=2); drop table system.aud$_prev_quarter; create table system.aud$_prev_quarter tablespace tools as (select * from sys.aud$ where 1=2); Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

34. Rotate AUD$ quarterly procedure quarterly_rotation is /* Procedure quarterly_rotation * Cleans out table system.aud$_prev_quarter * and repopulates it with the previous quarter's worth of audit info * Should be scheduled to run every quarter */ begin commit; settransaction use rollback segment rbs_bulk; deletefrom system.aud$_prev_quarter; insertinto system.aud$_prev_quarter ( select * from sys.aud$ au where au.timestamp# < trunc(sysdate,'Q')); deletefrom sys.aud$ where timestamp# < trunc(sysdate,'Q') ; commit; end quarterly_rotation; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

35. Use dbms_system.kdswrt() to log … deletefrom sys.aud$ where timestamp# < trunc(sysdate,'Q') ; commit; sys.dbms_system.ksdwrt(3,'ORA-AUDIT TRAIL: Quarterly rotation COMPLETE'); sys.dbms_system.ksdwrt(3,'ORA-AUDIT TRAIL: This message brought to you by sys.manage_aud$'); end quarterly_rotation ; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

36. Check AUD$ size procedure keep_size_in_check is rowCount number; begin selectcount(*) into rowCount from sys.aud$ ; if rowCount > 50000 then commit; settransaction use rollback segment rbs_bulk; insertinto system.aud$_bu (select * from sys.aud$); deletefrom sys.aud$ ; commit; sys.dbms_system.ksdwrt (3,'ORA-AUDIT TRAIL: POSSIBLE DOS ATTACK!!!! AUD$ rows moved to AUD$_BU'); endif; end keep_size_in_check; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

37. Poll AUD$ table regularly declare myjob number; begin dbms_job.submit(myjob, what => ‘manage_aud$.keep_size_in_check;’, interval => ‘trunc(sysdate)+1/96’); End; Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

38. Summary • Oracle Auditing is good for auditing non-DDL events, and auditing DDL where a record of the data changed is not important • AUDIT SESSION lets you track connections by IP, OS username, & terminal • If DB audit trail is enabled, the size of AUD$ must be managed Oracle Auditing - Natalka Roshak - http://rdbms-insight.com

39. Natalka Roshak Oracle and Sybase Database Administrator, Architect and Analyst http://rdbms-insight.com http://toolkit.rdbms-insight.com