Auditing

How to Setup Auditing
~~~~~~~~~~~~~~~~~~~~~
Do the following to set up auditing on your database:

1. Modify the "init.ora" file, usually located in the "$ORACLE_HOME/dbs"
directory to enable the AUDIT_TRAIL parameter and stop/start the instance to
make the parameter effective.

AUDIT_TRAIL can be set to one of the following four values:

--> DB/TRUE enables systemwide auditing where audited records are written to
the database audit trail, the SYS.AUD$ table
--> OS enables systemwide auditing where audited records are written to the
operating system's audit trail

--> DB_EXTENDED enables systemwide auditing as DB/TRUE does; in addition, it
populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table

NOTE :
-----
DB_EXTENDED can be used starting with 10g only. More informations can be found in
Note 249438.1 - 10G New Value DB_EXTENDED for the AUDIT_TRAIL Parameter

--> NONE/FALSE disables auditing. This is the default value.

Example:
--------
AUDIT_TRAIL = DB

2. If you have set AUDIT_TRAIL = OS, modify the "init.ora" file to specify
the destination for the audited records using the AUDIT_FILE_DEST parameter.
If your operating system supports AUDIT_TRAIL = OS auditing, files are
automatically created in the AUDIT_FILE_DEST for certain actions, and the
generated name contains the OS PID of the shadow process audited:

Example:
--------
AUDIT_FILE_DEST = $ORACLE_HOME/rdbms/audit
$ ls -l $ORACLE_HOME/rdbms/audit
total 68
-rw-rw---- 1 ora92 dba 881 Mar 17 09:57 ora_13264.aud
$ ps -efgrep 13264

ora92 13264 13235 0 09:56:43 ? 0:00 oracleV92 (DESCRIPTION=(LOCAL=Y)
SQL> select spid, program, username from v$process;
SPID PROGRAM USERNAME
------------ -------------------------------------------- -------------
...
13264 oracle@frhp11 (TNS V1-V3) ora92

Note: Windows NT does not use the AUDIT_FILE_DEST parameter.
Operating System auditing changes are recorded in the NT Event Viewer.

3. Make sure auditing has not already been installed.
Check for the existence of audit views:

SVRMGR> connect internal
SVRMGR> select * from sys.aud$; -- no rows should be returned
SVRMGR> select * from dba_audit_trail; -- no rows should be returned

If these views do not exist continue with the following, otherwise go to
step 4:

SVRMGR> connect internal
SVRMGR> @cataudit.sql

The auditing table will be installed in the SYSTEM tablespace.
It is therefore important that you have enough space available in this
tablespace before you enable auditing.
Note that as of version 8.1.7, cataudit.sql is run as part of a normal database
installation as it is called by catalog.sql, so there should be no need to
run cataudit.sql manually.

4. Shutdown and then restart the database, to enable the changes made to the
initialization parameters to be recognized.

SVRMGR> shutdown immediate
SVRMGR> startup

5. Specify the Audit option to be used.
The user must have system privilege 'audit system' privilege to execute
audit or noaudit commands.
Example:
--------
SQL> connect system/manager
SQL> grant audit system to scott;
SQL> connect scott/tiger
SQL> audit session;
and to disable session auditing:
SQL> noaudit session;