Auditing

AUDIT command
Fine-Grained Auditing

Oracle allows you to audit the various actions of users, even down to the issuing of SELECT statements, CONNECT etc.
There are two basic mechanisms for achieving this, AUDIT command and Fine-Grained Auditing (FGA).

AUDIT command.

Oracle allows you (as a SYSDBA) to specify what circumstances need to be audited. This is achieved by the use of the AUDIT command (see http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm.

Set the AUDIT_TRAIL initialization parameter (via PFILE or SPFILE depending on how you've set up the database at 9i, init.ora otherwise) to "DB". This instructs Oracle to store all auditing information within the SYS.AUD$ table, and the information is exposed in views such as DBA_AUDIT_TRAIL, DBA_AUDIT_SESSION etc.

Note, you can instruct Oracle to put auditing information within an OS file ( see the above link on how to do this ).

Once this is set, then you can issue commands such as

SQL> AUDIT CONNECT BY orauser;

Audit succeeded.

Now, somebody connecting as orauser will create the an entry in SYS.AUD$, i.e. :

SQL> SELECT os_username, username, action_name, timestamp
  2  FROM dba_audit_trail;

OS_USERNAME          USERNAME                       ACTION_NAME                 TIMESTAMP
-------------------- ------------------------------ --------------------------- ---------
machadde             ORAUSER                        LOGON                       10-OCT-03

*IMPORTANT*
Note, that most commands can be done which apply to everyone, i.e.

SQL> AUDIT CONNECT;

Audit succeeded.

*DEV NOTE*
You can "turn off" the auditing by issuing the NOAUDIT command, i.e.

SQL> NOAUDIT CONNECT BY orauser;

Noaudit succeeded.

Fine-Grained Auditing.
Fine Grained Auditing is a powerful mechanism for auditing every operation on a particular object down to column level, using the DBMS_FGA package. Note, that this feature is available from 9i release 1 onwards.

The basic mechanism is similar to Fine Grained Access Control, i.e. create a table policy

SQL> BEGIN
  2    dbms_fga.add_policy ( object_schema   => user,
  3                          object_name     => 'T',
  4                          policy_name     => 'T_FGA_POL' );
  5  END;
  6  /

PL/SQL procedure successfully completed.

Once the policy is in place, you can then find information from the DBA_FGA_AUDIT_TRAIL view, i.e.

SQL> SELECT A FROM T;

A
----------
1
2
3
3

SQL> SELECT TIMESTAMP, DB_USER, OBJECT_NAME, POLICY_NAME, SQL_TEXT
  2  FROM DBA_FGA_AUDIT_TRAIL
  3  /

TIMESTAMP DB_USER    OBJECT_NAM POLICY_NAME                    SQL_TEXT
--------- ---------- ---------- ------------------------------ -------------------------------------
08-APR-03 ORAUSER    T          T_FGA_POL                      SELECT A FROM T

Removing the policy is then a matter of :

SQL> BEGIN
  2    dbms_fga.drop_policy( object_schema => user,
  3                          object_name   => 'T',
  4                          policy_name   => 'T_FGA_POL' );
  5  END;
  6  /

*DEV NOTE*
DBMS_FGA also allows you to enable and disable the policy, via the enable_policy and disable_policy procedures.

*DEV NOTE*
You can also add conditional auditing, and auditing only on certain columns, i.e.

SQL> BEGIN
  2    dbms_fga.add_policy ( object_schema   => user,
  3                          object_name     => 'T',
  4                          policy_name     => 'T_FGA_POL',
  5                          audit_condition => 'a = 10',
  6                          audit_column    => 'A' );
  7  END;
  8  /

PL/SQL procedure successfully completed.