Auditing monitors and records selected actions by database users and processes.  It can be based on specific SQL, object, system or object privilege and monitor both successful and/or failed attempts.  Auditing must be enabled to perform auditing.  Audit records are either stored in data dictionary or OS file.  Auditing provides internal controls and typical implementations are:

1. Enable accountability for actions.
2. Deter user or intruders form inappropriate actions.
3. Investigate suspicious activity.
4. Notify an auditor of unauthorized actions.
5. Monitor and gather data about specific database activities.
6. Detect problems with an authorization or access control.
7. Address auditing requirements for regulatory compliance.

The system initialization parameter AUDIT_TRAIL controls database auditing process and has the following options.

• DB – audit trail is sent to data dictionary. All mandatory and SYS audit trails are always sent to OS audit trail.   DB is the default setting form AUDIT_TRAIL.
• DB, EXTENDED – Same as DB, but gives SQL, Bind variables, and CLOB columns to SYS.AUD$
• OS – Puts all audit trails on OS directory defined in AUDIT_FILE_DEST init parameter.
• XML – Puts all audit trails in XML file on OS directory defined in XML_AUDIT_TRAIL value. Does not affect syslog.audit file, which will always be in text.
• XML, EXTENDED – same as XML but includes SQL, Bind variables, etc.
• NONE – Disables auditing.

The SYS.AUD$ table holds standard audit records within the database and the SYS.FGA_LOG$ table holds records for fine grain auditing.  Audit records can only be deleted by administrator.  IF parameter 07_DIRCTIONARY_ACCESSIBILITY is set to FALSE the default value, only SYSDBA can perform DML on SYS.AUD$ or SYS.FGA_LOG$.   Oracle Database Vault and Oracle Label Security can add additional protection to audit trail but requires additional licensing.

Some actions by SYSDBA and SYSOPER are always audited and place in OS audit trail, this are referred to as mandatory auditing and happens even if audit trail is disabled.    These audit records are located in $ORACLE_BASE/admin/$ORACLE_SID/adump directory by default.   Mandatory actions that are audited:

1. Database Startup
2. SYSDBA and SYSOPER logins
3. Database shutdown.

FGA – Fine-grained auditing extends the capabilities of standard auditing and allows for conditions to be set to trigger auditing to occur.

Larry Catt

OCP