Auditing Database Activity

Auditing involves monitoring and recording specific database activity. Oracle 10g supports four levels (statement auditing, privilege auditing, object auditing, fine-grained auditing) of auditing. Audit records can be stored in the database or in operating system files. You tell the database where to record audit trail records by setting initialization parameter audit_trail

audit_trail = {db | os | none | true | false | db_extended}

none or false will disable database auditing
os records all audit records to operating system audit trail. audit_file_dest specifies OS directory into which audit trail is written when audit_trail is set to 'os'
db or true records all audit records to database audit trail. All audit records are stored in SYS.AUD$ table
db_extended records all audit records to database audit trail. In addition, SQLBIND and SQLTEXT CLOB columns of SYS.AUD$ table

Managing Statement Auditing: Statement auditing involves monitoring and recording the execution of specific types of SQL statements.

Enabling Statement Auditing:
AUDIT table;
AUDIT table BY scott; [record audit entries for specific users]
AUDIT table BY SCOTT WHENEVER NOT SUCCESSFUL;

Identifying enabled Statement Auditing Options:
SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts;

Disabling Statement Auditing:
NOAUDIT session;
NOAUDIT table BY scott;

Managing Privilege Auditing: Privilege auditing involves monitoring and recording the execution of SQL statements that require a specific system privilege.

Enabling Privilege Auditing:
AUDIT create any table;
AUDIT create any table BY scott;
AUDIT delete any table BY scott BY ACCESS;

Identifying enabled Privilege Auditing Options:
SELECT privilege, user_name
FROM dba_priv_audit_opts
ORDER BY privilege, user_name;

Disabling Privilege Auditing:
NOAUDIT alter profile;
NOAUDIT delete any table BY scott;

Managing Object Auditing:
--audit SELECT statements on HR.EMPLOYEE_SALARY table
AUDIT select ON hr.employee_salary;

Identifying enabled Object Auditing Options:
SELECT owner, object_name, object_type
FROM dba_obj_audit_opts
WHERE owner = ‘SCOTT’
AND object_name = ‘EMPLOYEE_SALARY’;

Disabling Object Auditing:
NOAUDIT select ON hr.employee_salary WHENEVER NOT SUCCESSFUL;

Purging Audit Trail
Database audit records for statement, privilege, and object auditing are stored in the table SYS.AUD$. Depending on how extensive your auditing and retention policies are, you will need to periodically delete old audit records from this table. To purge audit records older than 90 days, execute the following as SYS user.

DELETE FROM sys.aud$ WHERE timestamp# < style="font-weight: bold; font-style: italic;">
Managing Fine Grained Auditing
FGA lets you monitor and record data access based on the content of the data. With FGA, you define an audit policy on a table and optionally a column. When the specified condition evaluates to TRUE, an audit record is created, and an optional event-handler program is called. You use PL/SQL package DBMS_FGA to configure and manage FGA.

Creating an FGA Policy
To create a new FGA policy, use packaged procedure DBMS_FGA.ADD_POLICY. This procedure has the following parameters:

* object_schema This is the owner of the object to be audited. The default is NULL, which tells database to use the current user
* object_name Name of the object to be monitored
* policy_name This is a unique name for the new policy.
* audit_condition This is a SQL expression that evaluates to a Boolean. When this condition evaluates to TRUE or NULL, an audit record can be created.
* handler_schema This is the owner of the event_handler procedure. Default is NULL which tells the database to use the current schema.
* handler_module This is the name of the event_handler procedure. If the event handler is a packaged procedure, the handler_module must reference both the package name and program using dot notation, like UT_MAIL.SEND_ATTACH_ROW
* enable Boolean that tells the database if this policy should be in effect. Default is TRUE
* statement_types This tells the database which types of statements to monitor. Valid values are a comma-delimited list of SELECT, INSERT, UPDATE, and DELETE. The default is SELECT
* audit_column_ops This parameter has only two values: DBMS_FGA.ALL_COLUMNS and DBMS_FGA.ANY_COLUMNS.

Ex: DBMS_FGA.ADD_POLICY(object_schema=>’HR’,
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’
,audit_column=>’SALARY, COMMISSION_PCT’
,enable=>FALSE
,statement_types=>’SELECT’);

Enabling FGA Policy
Use the procedure DBMS_FGA.ENABLE_POLICY to enable an FGA policy. This procedure will not raise any exception if the procedure is already enabled. To enable the policy that is created in the above example

Ex: DBMS_FGA.ENABLE_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’);

Disabling an FGA policy
DBMS_FGA.DISABLE_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’);

Dropping an FGA policy
DBMS_FGA.DROP_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’);

Identifying FGA Policies in the Database
Query the DBA_AUDIT_POLICIES data dictionary view to report on the FGA policies defined in your database.
Ex: SELECT policy_name, object_schema||’.’||object_name object_name, policy_column, enabled, audit_trail FROM dba_audit_policies;

Reporting on the FGA Audit Trail Entries
The DBA_FGA_AUDIT_TRAIL data dictionary view is used in reporting on the FGA audit entries that have been recorded in the database.

Ex: SELECT db_user, timestamp, userhost FROM dba_fga_audit_trail
WHERE policy_name=’COMPENSATION_AUD’

Dictionary Views
DBA_AUDIT_TRAIL displays all audit trail entries.

Related View
USER_AUDIT_TRAIL displays all audit trail entries related to the current user.