This section describes the auditing capabilities available in Oracle database. These recommendations should not have a measurable performance impact.

In init.ora, set AUDIT_TRAIL to DB, OS or TRUE. Consult with the Applications Database Administrator before setting this value to TRUE. When set to OS, the database stores its audit records on the file system:

AUDIT_TRAIL = OS

Set parameter AUDIT_FILE_DEST to the directory where the audit records should be stored. By default, the operating system files are in the $ORACLE_BASE/admin/$ORACLE_SID/adumpdirectory for both UNIX and Windows systems.

AUDIT_FILE_DEST = /opt/app/oracle/admin/ORCL/adump

Monitoring and auditing database sessions, provides valuable information on database activity and is the only way to identify certain types of attacks (for example, password guessing attacks on an application schema). By auditing database sessions, suspicious connections to highly privileged schemas may be identified.

To audit sessions, login through sqlplus as SYSTEM and issue the following command:

SQL> audit session;

Audit any changes to the standard application database schema or creation of new schemas. As rare events, these changes may indicate inappropriate or malicious activity.

To audit schema changes, login through sqlplus as SYSTEM and issue the following command:

SQL> audit user;

To complete the recommended auditing, enable three other audit events: create database link, alter system and system audit. The remaining audit options generate significant entries of little value.

To audit the other events, login through sqlplus as SYSTEM and issue the following commands:

SQL> AUDIT DATABASE LINK; -- Audit create or drop database links

SQL> AUDIT PUBLIC DATABASE LINK; -- Audit create or drop public database links

SQL> AUDIT SYSTEM AUDIT; -- Audit statements themselves

SQL> AUDIT ALTER ANY ROLE by ACCESS; -- Audit alter any role statements

SQL> AUDIT ALTER DATABASE by ACCESS; -- Audit alter database statements

SQL> AUDIT ALTER SYSTEM by ACCESS; -- Audit alter system statements

SQL> AUDIT CREATE ROLE by ACCESS; -- Audit create role statements

SQL> AUDIT DROP ANY ROLE by ACCESS; -- Audit drop any role statements

SQL> AUDIT PROFILE by ACCESS; -- Audit changes to profiles

SQL> AUDIT PUBLIC SYNONYM by ACCESS; -- Audit public synonyms statements

SQL> AUDIT SYSDBA by ACCESS; -- Audit SYSDBA privileges

SQL> AUDIT SYSOPER by ACCESS; -- Audit SYSOPER privileges

SQL> AUDIT SYSTEM GRANT by ACCESS; -- Audit System grant privileges

Connections to the database as well as SYSDBA and SYSOPER actions (instance startup/ shutdown) are always logged to the directory $ORACLE_HOME/rdbms/audit (unless AUDIT_FILE_DEST property is overridden). This file contains the operating system user and terminal ID.

If AUDIT_TRAIL is set to OS, review audit records stored in the file name; in AUDIT_FILE_DEST.

Archive and purge the audit trail on a regular basis, at least every 90 days. The database connection entries take up significant space. Backup the audit file before purging.

Audit data may contain confidential or privacy related data. Restrict audit trail access appropriately.

It must be noted that auditing features can impose a significant performance overhead. Auditing should thus be limited to the set of items outlined above. Auditing application schema objects should be strictly avoided.