AUDIT

Purpose

This statement is used to configure or delete audit rules for SQL statements.

Limitations and considerations

Before enabling audit, you must unlock the ORAAUDITOR user.

To unlock the ORAAUDITOR user, use the following example:

ALTER USER ORAAUDITOR ACCOUNT UNLOCK;

For more information about audit, see Enable security audit.

Syntax

/* Statement audit */
{AUDIT | NOAUDIT}
    { statement_operation_list | ALL | ALL STATEMENTS }
    [BY user_name [, user_name]...]
    [BY ACCESS]
    [WHENEVER [NOT] SUCCESSFUL]

/* Object audit */
{AUDIT | NOAUDIT}
    { object_operation_list | ALL }
    ON { obj_name | DEFAULT }
    [WHENEVER [NOT] SUCCESSFUL]

statement_operation_clause:
    statement_operation_list
    | ALL
    | ALL STATEMENTS

statement_operation_list:
    statement_operation [statement_operation...]

object_operation_clause:
    object_operation_list
    | ALL

object_operation_list:
    object_operation [object_operation...]

auditing_on_clause:
    ON obj_name
    | ON DEFAULT

auditing_by_user_clause:
    BY user_name [user_name...]

whenever_option:
    WHENEVER NOT SUCCESSFUL
    | WHENEVER SUCCESSFUL

Parameters

The following table lists the types of SQL statements that can be audited.

Table 1: Audit statement descriptions

Table 2: Audit statement descriptions

Examples

Basic audit configuration

1. View users:

   obclient> SELECT username, account_status FROM dba_users ORDER BY created;
   

   The returned result is:

   +------------+----------------+
   | USERNAME   | ACCOUNT_STATUS |
   +------------+----------------+
   | SYS        | OPEN           |
   | LBACSYS    | LOCKED         |
   | ORAAUDITOR | OPEN           |
   +------------+----------------+
   3 rows in set (0.211 sec)
   

2. Audit the SELECT table operations of the SYS user:

   obclient> AUDIT SELECT TABLE BY SYS BY ACCESS;
   

3. Audit only the failed table operations of the SYS user:

   obclient> AUDIT TABLE BY SYS BY ACCESS WHENEVER NOT SUCCESSFUL;
   

Disable auditing

1. Disable auditing of table operations for the SYS user:

   obclient> NOAUDIT TABLE BY SYS;
   

2. Disable auditing of the SELECT table operations for the SYS user:

   obclient> NOAUDIT SELECT TABLE BY SYS;
   

View the audit configuration

1. View the statement audit configuration:

   obclient> SELECT * FROM DBA_STMT_AUDIT_OPTS;
   

2. View the object audit configuration:

   obclient> SELECT * FROM DBA_OBJ_AUDIT_OPTS;
   

Configure system parameters

1. Enable system operation auditing:

   obclient> ALTER SYSTEM SET audit_sys_operations = TRUE;
   -- or
   obclient> ALTER SYSTEM SET audit_sys_operations = '1';
   

2. Set the output destination for audit logs:

   -- Output to a database table
   obclient> ALTER SYSTEM SET audit_trail = 'DB';
   
   -- or output to an operating system file
   obclient> ALTER SYSTEM SET audit_trail = 'OS';