Quick Reference to Auditing Information

Subject: Quick Reference to Auditing Information
Doc ID: Note:41800.1 Type: REFERENCE
Last Revision Date: 09-MAY-2007 Status: PUBLISHED


"Checked for relevance on 09-May-2007"

*** Please note this is a quick reference page and so does NOT contain
full explanations of the various statements and views.

Database Audit mode
~~~~~~~~~~~~~~~~~~~
show parameter audit

AUDIT_TRAIL DB , OS or NONE See
AUDIT_FILE_DEST File location See


What Statements are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set audit:
AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

select * from dba_stmt_audit_opts where USER_NAME='...';

Columns are:
AUDIT_OPTION from STMT_AUDIT_OPTION_MAP
SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
FAILURE ""

What Privileges are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set audit:
AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

select * from dba_priv_audit_opts where USER_NAME='...';

Columns are:
PRIVILEGE from SYSTEM_PRIVILEGE_MAP
SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
FAILURE ""


What Objects are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set Auditing:
AUDIT [object_option] ON [schema].object|DEFAULT [BY SESSION|ACCESS]
[WHENEVER {NOT} SUCCESSFUL]

select * from dba_obj_audit_opts where owner='..' and OBJECT_NAME='...';
select * from all_def_audit_opts;

Columns are:
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA

X/Y - is no option set X is when successful
S set by session Y is when Unsuccessful
A set by access


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


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. See Note 103964.1 for more details.


Related Articles and Examples:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note 99137.1 Setting up, Interpreting Auditing Using Windows NT Event Viewer
Note 103964.1 How to Audit Connect Internal Using Oracle Server
Note 1020945.6 How to Setup Auditing
Note 1068714.6 How does the NOAUDIT option work
Note 99786.1 How to Audit User Connection, Disconnection Date and Time
Note 103964.1 How to Audit Connect Internal Using Oracle Server
Note 249438.1 10G: New Value DB_EXTENDED for the AUDIT_TRAIL init.ora
Note 287436.1 SCRIPT Generate AUDIT and NOAUDIT Statements for Current Audit Settings