Standard Audit in Oracle Database

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

SELECT username, extended_timestamp, owner, obj_name, action_name, sql_text FROM  dba_audit_trail WHERE owner = 'USER1' ORDER BY timestamp;
 * Compare the output between AUDIT_TRAIL=db and db, extended

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

SQL> alter system set audit_sys_operations=true scope=spfile; System altered.
 * Audit SYSDBA operations

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;

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.
 * View Audits

COLUMN username FORMAT A10 COLUMN owner   FORMAT A10 COLUMN obj_name FORMAT A10 COLUMN extended_timestamp FORMAT A35
 * Sample

-- 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>

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;
 * More samples of the audit syntaxes

AUDIT SELECT, INSERT, UPDATE, DELETE on ittichai.t_audit BY ACCESS;

AUDIT SELECT, INSERT, UPDATE, DELETE on t_audit by access;
 * Audit Maintenance

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

NOAUDIT session; NOAUDIT session BY jeff, lori; NOAUDIT DELETE ANY TABLE; NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
 * Turn Off Statement and Privilege Auditing

NOAUDIT ALL;
 * Turn off all statement audit options:

NOAUDIT ALL PRIVILEGES;
 * Turn off all privilege audit options:

NOAUDIT ALL ON emp;
 * Turn off all object audit options on the emp table, enter the following statement:

NOAUDIT ALL ON DEFAULT; NOAUDIT NETWORK;
 * Turn off all default object audit options, enter the following statement:
 * Turn off network auditing:

SELECT * FROM DBA_STMT_AUDIT_OPTS;
 * List Active Statement Audit Options

USER_NAME              AUDIT_OPTION         SUCCESS         FAILURE --- --      - JWARD                   SESSION              BY SESSION      BY SESSION SWILLIAMS              SESSION              BY SESSION      BY SESSION LOCK TABLE          BY ACCESS       NOT SET

SELECT * FROM DBA_PRIV_AUDIT_OPTS;
 * View all the privilege audit options that are set:

USER_NAME          PRIVILEGE            SUCCESS      FAILURE --- -   -- ALTER USER          BY SESSION           BY SESSION

SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'USER1' AND OBJECT_NAME LIKE 'T_AUDIT%';
 * List Active Object Audit Options for Specific Objects

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 (/). SELECT * FROM ALL_DEF_AUDIT_OPTS;
 * List Default Object Audit Options

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /- -/-

SELECT * FROM DBA_AUDIT_OBJECT;
 * Query lists audit records generated by statement and object audit options:

SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK FROM DBA_AUDIT_SESSION;
 * List Audit Records for the AUDIT SESSION Option

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

SQL> truncate table sys.aud$; Table truncated.
 * Purge the Audit Trail Views


 * 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