Standard Audit in Oracle Database
Jump to navigation
Jump to search
Standard Audit in Oracle Database
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended } none or false - Auditing is disabled. db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$). db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated. xml- Auditing is enabled, with all audit records stored as XML format OS files. (10gR2) xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated. (10gR2) os- Auditing is enabled, with all audit records directed to the operating system's audit trail. SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE; SQL> SHUTDOWN SQL> STARTUP
- Compare the output between AUDIT_TRAIL=db and db, extended
SELECT username, extended_timestamp, owner, obj_name, action_name, sql_text FROM dba_audit_trail WHERE owner = 'USER1' ORDER BY timestamp; USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAM SQL_TEXT ---------- -------------------------------------- ---------- ---------- ---------- -------------------------------- USER2 15-JUN-12 11.12.15.955356000 AM AMERIC USER1 T_AUDIT SELECT A/CHICAGO USER2 15-JUN-12 11.13.04.955356000 AM AMERIC USER1 T_AUDIT SELECT select * from ittichai.t_audit A/CHICAGO
- Audit SYSDBA operations
SQL> alter system set audit_sys_operations=true scope=spfile; System altered. SQL> show parameter audit_sys_ope NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_sys_operations boolean TRUE
All Audits by a User
AUDIT ALL BY test_user BY ACCESS; AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY test_user BY ACCESS; AUDIT EXECUTE PROCEDURE BY test_user BY ACCESS;
- View Audits
Three main views are
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$). DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$). DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
- Sample
COLUMN username FORMAT A10 COLUMN owner FORMAT A10 COLUMN obj_name FORMAT A10 COLUMN extended_timestamp FORMAT A35 -- Audit query per user owner SELECT username, extended_timestamp, owner, obj_name, action_name FROM dba_audit_trail WHERE owner = 'AUDIT_TEST' ORDER BY timestamp; USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME ---------- ----------------------------------- ---------- ---------- ---------------------------- AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00 AUDIT_TEST TEST_TAB CREATE TABLE AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00 AUDIT_TEST TEST_TAB INSERT AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00 AUDIT_TEST TEST_TAB UPDATE AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00 AUDIT_TEST TEST_TAB SELECT AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00 AUDIT_TEST TEST_TAB DELETE AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00 AUDIT_TEST TEST_TAB DROP TABLE 6 rows selected. SQL>
- More samples of the audit syntaxes
AUDIT ALTER, INDEX, RENAME ON DEFAULT BY SESSION; CREATE VIEW jeff.employee AS SELECT * FROM jeff.emp; AUDIT SESSION BY jward, swilliams; AUDIT ALTER USER; AUDIT LOCK TABLE BY ACCESS WHENEVER SUCCESSFUL; AUDIT DELETE ON jeff.emp BY ACCESS WHENEVER SUCCESSFUL; AUDIT SELECT, INSERT, UPDATE, DELETE on ittichai.t_audit BY ACCESS;
- Audit Maintenance
AUDIT SELECT, INSERT, UPDATE, DELETE on t_audit by access; SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'USER1' AND OBJECT_NAME LIKE 'T_AUDIT%'; OOWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK ------------ -------------- ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- USER1 T_AUDIT TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/- NOAUDIT DELETE on t_audit; SELECT * FROM DBA_PRIV_AUDIT_OPTS; USER_NAME PRIVILEGE SUCCESS FAILURE ------------------- -------------------- --------- ---------- ALTER USER BY SESSION BY SESSION
- Turn Off Statement and Privilege Auditing
NOAUDIT session; NOAUDIT session BY jeff, lori; NOAUDIT DELETE ANY TABLE; NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
- Turn off all statement audit options:
NOAUDIT ALL;
- Turn off all privilege audit options:
NOAUDIT ALL PRIVILEGES;
- Turn off all object audit options on the emp table, enter the following statement:
NOAUDIT ALL ON emp;
- Turn off all default object audit options, enter the following statement:
NOAUDIT ALL ON DEFAULT;
- Turn off network auditing:
NOAUDIT NETWORK;
- List Active Statement Audit Options
SELECT * FROM DBA_STMT_AUDIT_OPTS; USER_NAME AUDIT_OPTION SUCCESS FAILURE -------------------- ------------------- ---------- --------- JWARD SESSION BY SESSION BY SESSION SWILLIAMS SESSION BY SESSION BY SESSION LOCK TABLE BY ACCESS NOT SET
- View all the privilege audit options that are set:
SELECT * FROM DBA_PRIV_AUDIT_OPTS; USER_NAME PRIVILEGE SUCCESS FAILURE ------------------- -------------------- --------- ---------- ALTER USER BY SESSION BY SESSION
- List Active Object Audit Options for Specific Objects
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'USER1' AND OBJECT_NAME LIKE 'T_AUDIT%'; OOWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK ------------ -------------- ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- USER1 T_AUDIT TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:
A dash (-) indicates that the audit option is not set.
The S character indicates that the audit option is set, BY SESSION.
The A character indicates that the audit option is set, BY ACCESS.
Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by a slash (/).
- List Default Object Audit Options
SELECT * FROM ALL_DEF_AUDIT_OPTS; ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /- -/-
- Query lists audit records generated by statement and object audit options:
SELECT * FROM DBA_AUDIT_OBJECT;
- List Audit Records for the AUDIT SESSION Option
SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK FROM DBA_AUDIT_SESSION; USERNAME LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO ---------- --------- ---------- ---------- ---------- ---------- JWARD 02-AUG-91 53 2 24 0 SWILLIAMS 02-AUG-91 3337 256 630 0
- Purge the Audit Trail Views
SQL> truncate table sys.aud$; Table truncated.
- References
http://www.oracle-base.com/articles/10g/auditing-10gr2.php#fine_grained_auditing http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm