1 / 39

Oracle Auditing

Oracle Auditing. Natalka Roshak Presented to ABCD-Oracle 4 March 2004. About me. Oracle and Sybase Database Administrator, Analyst and Architect Experienced database programmer Oracle Certified Professional (OCP)

benjamin
Télécharger la présentation

Oracle Auditing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related