Oracle Database Auditing


Auditing allows recording of database activity, tracking database user activity, what and when it occurred. Auditing records are written to the Oracle audit trail table owned by the SYS user called SYS.AUD$. Auditing will impact database availability depending on the type of auditing enabled. Auditing activity can range from simple connecrtion activity to full transactional auditing. The less auditing done the less overhead. The users SYS and INTERNAL plus remote databases can not be audited. Thus auditing is applicable to the local database only.

Auditing can be used to track potential suspicious activity aomngst tracking of potential speed bottlenecks. For instance, password changes, tablespace or quota settings, too many deadlocks, unauthorised row access.

There are three Type of Database Auditing.

  1. Statement Auditing.
  2. Privelage Auditing.
  3. Object Auditing.

Auditing Enabling and Disabling

AUDIT_TRAIL parameter in the init.ora file can be set to NONE, DB or OS.

Types of Auditing

Value-based auditing tracks column values and database auditing tracks user actions.

Value-Based (SQL Statement) Auditing

Auditing does not store before and after change image data values on audited statements. Old and new values of updated rows are not stored when auditing an UPDATE DML statement. Specialised value-based column value change auditing can be done on tables using database triggers. That would probably be a little extreme and perhaps would cause serious performance problems.

Database (User Actions Tracking) Auditing

Note that any changes to auditing options apply to subsequent not currently connected sessions.

Statement and Privelage Auditing

[NO]AUDIT statement { [option | system privilege | additional statement option] ... }
	[BY user [,...]] [BY SESSION | ACCESS] [WHENEVER [NOT] SUCCESSFUL]
;

Statement auditing is the tracking of SQL statements issued by users of the database. Privelage auditing is the tracking of SQL statements issued by users granted rights to access the specified statement with a system privelage. The examples below show the enabling and disabling of auditing. Statement auditing is auditing of DDL SQL statements.

AUDIT DELETE TABLE, INSERT TABLE, EXECUTE ANY PROCEDURE BY ACCESS WHENEVER SUCCESSFUL;
NOAUDIT DELETE TABLE, INSERT TABLE, EXECUTE ANY PROCEDURE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE BY ACCESS;

There are four additional options for enabling or disabling auditing onto statement combinations.

  1. CONNECT.
  2. RESOURCE.
  3. DBA.
  4. ALL.
AUDIT RESOURCE WHENEVER SUCCESSFUL;

Statement auditing options are available for the following commands.

Object Auditing

[NO]AUDIT { object option [,...] } ON {[schema.]object | DEFAULT}
	[BY SESSION | ACCESS] [WHENEVER [NOT] SUCCESSFUL]
;

Object auditing is the auditing of SQL DML statements issued against the database by database users. Note that with object auditing all users are audited. The emphasis is on objects. Object auditing can be enabled by any user with the AUDIT ANY privelage. Objects which can be statement audited are tables, views, sequences, packages, procedures and functions. If a function for instance accesses many object then that function can potentially generate many audit trail entries.

AUDIT SELECT, INSERT, UPDATE, DELETE ON ALL WHENEVER SUCCESSFUL;

The ON DEFAULT option will audit actions on all objects on objects created after the audit statement was issued.

AUDIT INSERT, UPDATE, DELETE ON DEFAULT;

Object auditing options available are as follows.

Audit Trail Management

Audit trail records are written to the database regardless of commit or rollback. Access auditing writes multiple records. The point to note is that the audit trail for a database can grow very rapidly. Growth of the audit trail must be controlled carefully, even to the point of purging audit trail records from the SYS.AUD$ table from time-to-time by truncating the table. Be selective in what is audited. Control very carefully who can performing auditing. The SYS.AUD$ table can be set not to exceed a specific size. If audit records are required to be kept then export the SYS.AUD$ table prior to truncation.

Audit Trail Information

Numerous views are created overlaying the SYS.AUD$ table. Specific notations are used to show specific facts.

For example A/S means auditing BY ACCESS WHENEVER SUCCESSFUL and auditing BY SESSION WHENEVER SUCCESSFUL.

Character codes are used for object actions. These are (S)uccess, (F)ailure and (B)oth.

Auditing Using Database Triggers

Triggers allow the writing of audit trails of database table rows both prior to and after a row update. Auditing will record which objects have changed. Triggers can be used to record actual data column value changes. Additionally triggers can be executed for database auditing to occur before or after a database action. For instance, one could record the after image of a row update operation and avoid recording an image if there was a rollback by triggering auditing actions on an AFTER basis.