Auditing on Oracle Database in a Nutshell (11gR2)

In an Oracle Database we can mention following auditing types:

Mandatory Auditing
Standard Auditing
Fine-Grained Auditing
SYS Auditing

Mandatory Auditing causes database start-up/shut-down and SYSDBA-SYSOPER login logout information to be written into AUDIT_FILE_DEST. This auditing cannot be turned off and it's always written into operating system directory specified with AUDIT_FILE_DEST.

Standard Auditing is controlled with AUDIT_TRAIL parameter. (NONE,OS,DB,DB_EXTENDED,XML,XML_EXTENDED)

* When DB or DB_EXTENDED is used, audit records are written into database (aud$ table). (Not the mandatory auditing records, they're always on OS)

* XML options write to operating system in XML format.

* When *EXTENDED is used SQLBIND and SQLTEXT information is included in the audit trail. If not used, not included.

* By default, Standard Auditing audits SQL statements which use following privileges:

ALTER ANY PROCEDURE | CREATE ANY LIBRARY | DROP ANY TABLE | ALTER ANY TABLE | CREATE ANY PROCEDURE | DROP PROFILE | ALTER DATABASE | CREATE ANY TABLE | DROP USER | ALTER PROFILE | CREATE EXTERNAL JOB | EXEMPT ACCESS POLICY | ALTER SYSTEM | CREATE PUBLIC DATABASE LINK | GRANT ANY OBJECT PRIVILEGE | ALTER USER | CREATE SESSION | GRANT ANY PRIVILEGE | AUDIT SYSTEM | CREATE USER | GRANT ANY ROLE | CREATE ANY JOB | DROP ANY PROCEDURE

* It's possible to audit Statements, Privileges and Objects with Standard Auditing. For example:

Privilege auditing: audit select any table;
Statement auditing: audit select table;
Object auditing: audit select on SCOTT.SALARY;

* Following views give information about current Standard Auditing configuration in the database:

DBA_STMT_AUDIT_OPTS; ==> describes current statements being audited across the system
DBA_PRIV_AUDIT_OPTS; ==> describes current system privileges being audited across the system
DBA_OBJ_AUDIT_OPTS; ==> describes auditing options for all objects

* You can use the SQL "AUDIT" statement to set auditing options regardless of the setting of AUDIT_TRAIL parameter. However, Oracle Database does not generate audit records until you enable Standard Auditing using AUDIT_TRAIL parameter.

* Auditing to OS offers higher performance when compared with DB.

Fine-Grained Auditing is used to audit operations like:

Accessing a table outside of normal working hours
Logging in from a particular IP address
Selecting or updating a particular table column

* DBMS_FGA package is used to manage Fine-Grained Auditing. DBA_AUDIT_POLICIES view describes all fine-grained auditing policies in the database.

* It's not mandatory to enable Standard Auditing in order to use Fine-Grained Auditing or SYS Auditing.

SYS Auditing: AUDIT_SYS_OPERATIONS parameter (TRUE/FALSE) enables or disables the auditing of SQL statements that directly issued by users connected with SYSDBA or SYSOPER privileges (SQL statements run from within PL/SQL procedures or functions are not audited). These audit records are written into OS.

* It's not mandatory to enable Standard Auditing in order to use SYS Auditing.