Database Audit

Configuring and Administering Auditing

1. Set the AUDIT_TRAIL parameter

Possible values are:
none - audit disabled
os - output is sent to op system
db - output is stored in database sys.aud$ table
db,extended - additionally SQLBIND and SQLTEXT fields are populated
xml
xml,extended - stores output as XML files

2. Possible types of AUDIT

* statement
* privilege
* object
* fine-grained

A special type of audit is SYS AUDIT. To enable it, set the AUDIT_SYS_OPERATIONS = TRUE

This will log all the activity of SYS user or any other user who logs in with SYSDBA privilege.

3. Managing audit events

Use the AUDIT and NOAUDIT statements to control the audit events
Possible options:
BY SESSION/BY ACCESS
WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

Example.
To enable logging of jeff's and lori's login/logoff attempts, run:

AUDIT session BY jeff, lori;

To disable: NOAUDIT session BY jeff, lori;

To turn off all statement auditing:
NOAUDIT ALL;

All privilege auditing:
NOAUDIT ALL PRIVILEGES;

You must specify the USER if the audit was created with BY SOME_USER option. Like this:
NOAUDIT ALL BY SCOTT;

To see the list of currently enabled statement audit events:

select * from DBA_STMT_AUDIT_OPTS

To see the list of privilege audits:

select * from DBA_PRIV_AUDIT_OPTS

And object audits:

select * from DBA_OBJ_AUDIT_OPTS

All audit trail views



4. Checking audit results

Query the DBA_AUDIT_TRAIL or DBA_COMMON_AUDIT_TRAIL view to see the audit logs

Useful tip. The AUDIT SESSION event registers the logon and logoff events and it also saves the number of logical and physical reads for the disconnected session. This could be used for tracking the performance issues. To see this information query the DBA_AUDIT_SESSION view.

5. Purging AUDIT records

You have to manually delete the no longer needed records from the SYS.AUD$ table.
For example to delete the records which are older than 90 days, execute:

delete from sys.aud$ where timestamp# < sysdate - 90

6. Fine-grained audit (FGA)

You use the DBMS_FGA package to configure and manage FGA.

To add a policy

BEGIN DBMS_FGA.ADD_POLICY
(object_schema=>'SCOTT',
object_name=>'EMP',
policy_name=>'track_the_shit',
audit_condition=>'sal > 2000',
audit_column=>'SAL, COMM',
statement_types=>'UPDATE, DELETE');
END;

To remove policy

exec dbms_fga.drop_policy('SCOTT','EMP','TRACK_THE_SHIT');

To enable or disable use .ENABLE_POLICY and .DISABLE_POLICY respectively.

To get the list of policies, query:

select * from dba_audit_policies;

To get the audit records, query:

select * from dba_fga_audit_trail;

It seems that any activity by a user logged in as sysdba is not recorded by FGA. I haven't found oracle docs saying this but this was the behavior I noticed while doing some experiments.