Oracle Database Administration: The Essential Refe

Statement Auditing

Statement auditing allows you to track who is issuing specific types of statements. The AUDIT statement form of the AUDIT command allows you to specify any SQL statement to be audited. You are also able to audit the use of a SQL statement by all users or a specific user.

AUDIT sql_statement[, sql_statement ...]
   [BY user[,user ...]]
   [BY SESSION [WHENEVER [NOT] SUCCESSFUL]
   [BY ACCESS [WHENEVER [NOT] SUCCESSFUL]

In this case, the sql_statement can be either a SQL statement or shortcut provided by Oracle. When you use one of the shortcuts, you are able to specify a group of SQL statements to be audited.

Tip

There is a certain level of overlap between statement auditing and privilege auditing. For example, the AUDIT CREATE TABLE statement is regarded as both a statement audit command (CREATE TABLE SQL statement) and a privilege audit command (CREATE TABLE system privilege). See Chapter 13, for a list of SQL statements relevant to the DBA, and Chapter 15, for a list of all system privileges.

Table 7.2 provides a list of shortcuts for DDL statements; Table 7-3 provides a list of shortcuts for DML statements.

Table 7-2. DML Statement Auditing Shortcuts

SQL Statement Shortcut	SQL Statements Represented
CLUSTER	CREATE CLUSTER AUDIT CLUSTER DROP CLUSTER TRUNCATE CLUSTER
DATABASE LINK	CREATE DATABASE LINK DROP DATABASE LINK
DIRECTORY	CREATE DIRECTORY DROP DIRECTORY
INDEX	CREATE INDEX ALTER INDEX DROP INDEX
NOT EXISTS	All SQL statements that fail because a specified object ...