Quick Reference to Auditing Information

1.       Database Audit mode

SQL> show parameter audit NAME                                 TYPE        VALUE ------------------------------------ ----------- ----------------------------- audit_file_dest                      string      /u02/oracle/admin/mydb/adump audit_sys_operations                 boolean     FALSE audit_syslog_level                   string audit_trail                          string      TRUE

2.       What Statements are being audited ?

To set audit:

AUDIT [option] [BY USER|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

To check audit result:

select * from dba_stmt_audit_opts where USER_NAME='...'; Note: AUDIT_OPTION: from table STMT_AUDIT_OPTION_MAP; SUCCESS:      'BY SESSION', 'BY ACCESS' or 'NOT SET' FAILURE:      ""

3.       What Privileges are being audited ?

To set audit:

AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

To check audit result:

select * from dba_priv_audit_opts where USER_NAME='...'; Note: PRIVILEGE: from SYSTEM_PRIVILEGE_MAP SUCCESS:   'BY SESSION', 'BY ACCESS' or 'NOT SET' FAILURE:   ""

4.       What Objects are being audited ?

To set Auditing:

AUDIT [object_option] ON [schema].object|DEFAULT [BY SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

To check audit result:

select * from dba_obj_audit_opts where owner='..' and OBJECT_NAME='...'; select * from all_def_audit_opts; Note: Values of columns (ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA) are X/Y: -    is no option set -    X is when successful -    S set by session -    Y is when Unsuccessful -    A set by access

5.       Audit results

-    Raw results go to DBA_AUDIT_TRAIL (view on SYS.AUD$ table).

-    Main where columns are: USERNAME, TIMESTAMP, OWNER

-    For underlying results see: Select STATEMENT, TIMESTAMP, ACTION, USERID from AUD$;

6.       Auditing administrative connections

The administrative user connections (CONNECT / AS SYSDBA or CONNECT / AS SYSOPER) are always logged regardless of audit setting. On UNIX platforms these are logged to *.aud files in $ORACLE_HOME/rdbms/audit regardless of any init.ora parameter settings.

Last updated: 2009-September-23, Tuesday