AUDIT sql_statements

Syntax

Purpose

To track the occurrence of specific SQL statements in subsequent user sessions. Auditing options specified by the AUDIT sql_statements statement apply only to subsequent sessions, not to current sessions.

To choose particular schema objects for auditing, see "AUDIT schema_objects". For information on disabling auditing of SQL statements, see "NOAUDIT sql_statements".

Prerequisites

You must have AUDIT SYSTEM system privilege.

You must enable auditing by setting the initialization parameter AUDIT_TRAIL to DB. You can specify auditing options regardless of whether auditing is enabled. However, Oracle does not generate audit records until you enable auditing.

Keywords and Parameters

statement_opt	chooses specific SQL statements for auditing. For a list of these statement options and the SQL statements they audit, see Table 7-1 and Table 7-2. For each audited operation, Oracle produces an audit record containing this information: user performing the operation type of operation object involved in the operation date and time of the operation Oracle writes audit records to the audit trail, which is a database table containing audit records. You can review database activity by examining the audit trail through data dictionary views. For information on these views, see the Oracle8i Reference.
system_priv	chooses SQL statements that are authorized by the specified system privilege for auditing. For a list of all system privileges and the SQL statements that they authorize, see Table 7-5. Rather than specifying many individual system privileges, you can specify the roles CONNECT, RESOURCE, and DBA. Doing so is equivalent to auditing all of the system privileges granted to those roles. See "GRANT system_privileges_and_roles" for more information on these roles.
	Oracle also provides two shortcuts for specifying groups of system privileges and statement options at once: ALL is equivalent to specifying all statements options shown in Table 7-1 but not the additional statement options shown in Table 7-2. ALL PRIVILEGES is equivalent to specifying all system privileges.
	Note: Oracle Corporation recommends that you specify individual system privileges and statement options for auditing rather than roles or shortcuts. The specific system privileges and statement options encompassed by roles and shortcuts change from one release to the next and may not be supported in future versions of Oracle.
BY user	chooses only SQL statements issued by specified users for auditing. If you omit this clause, Oracle audits all users' statements.
BY proxy	chooses for auditing only SQL statements issued by the specified proxy. See Also: Oracle8i Concepts for more information on proxies and their use of the database.
	ON BEHALF OF specifies the user or users on whose behalf the proxy executes the specified statement. user specifies auditing of statements executed on behalf of a particular user. ANY specifies auditing of statements executed on behalf of any user.
BY SESSION	causes Oracle to write a single record for all SQL statements of the same type issued in the same session.
BY ACCESS	causes Oracle to write one record for each audited statement.
	If you specify statement options or system privileges that audit data definition language (DDL) statements, Oracle automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.
	For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.
WHENEVER SUCCESSFUL	chooses auditing only for statements that succeed. NOT chooses auditing only for statements that fail or result in errors.
	If you omit the WHENEVER SUCCESSFUL clause, Oracle audits SQL statements regardless of success or failure.

Table 7-1  Statement Auditing Options for Database Objects

Statement Option	SQL Statements and Operations
CLUSTER	CREATE CLUSTER AUDIT CLUSTER DROP CLUSTER TRUNCATE CLUSTER
CONTEXT	CREATE CONTEXT DROP CONTEXT
DATABASE LINK	CREATE DATABASE LINK DROP DATABASE LINK
DIMENSION	CREATE DIMENSION ALTER DIMENSION DROP DIMENSION
DIRECTORY	CREATE DIRECTORY DROP DIRECTORY
INDEX	CREATE INDEX ALTER INDEX DROP INDEX
NOT EXISTS	All SQL statements that fail because a specified object does not exist.
PROCEDUREa	CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION DROP LIBRARY DROP PACKAGE DROP PROCEDURE
PROFILE	CREATE PROFILE ALTER PROFILE DROP PROFILE
PUBLIC DATABASE LINK	CREATE PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK
PUBLIC SYNONYM	CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM
ROLE	CREATE ROLE ALTER ROLE DROP ROLE SET ROLE
ROLLBACK STATEMENT	CREATE ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT DROP ROLLBACK SEGMENT
SEQUENCE	CREATE SEQUENCE DROP SEQUENCE
SESSION	Logons
SYNONYM	CREATE SYNONYM DROP SYNONYM
SYSTEM AUDIT	AUDIT sql_statements NOAUDIT sql_statements
SYSTEM GRANT	GRANT system_privileges_and_roles REVOKE system_privileges_and_roles
TABLE	CREATE TABLE DROP TABLE TRUNCATE TABLE COMMENT ON TABLE DELETE [FROM] table
TABLESPACE	CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE
TRIGGER	CREATE TRIGGER ALTER TRIGGER with ENABLE and DISABLE clauses DROP TRIGGER ALTER TABLE with ENABLE ALL TRIGGERS clause and DISABLE ALL TRIGGERS clause
TYPE	CREATE TYPE CREATE TYPE BODY ALTER TYPE DROP TYPE DROP TYPE BODY
USER	CREATE USER ALTER USER DROP USER
VIEW	CREATE VIEW DROP VIEW
aJava schema objects (sources, classes, and resources) are considered the same as procedures for purposes of auditing SQL statements.

Table 7-2  Additional Statement Auditing Options for SQL Statements

Statement Option	SQL Statements and Operations
ALTER SEQUENCE	ALTER SEQUENCE
ALTER TABLE	ALTER TABLE
COMMENT TABLE	COMMENT ON TABLE table, view, materialized view COMMENT ON COLUMN table.column, view.column, materialized view.column
DELETE TABLE	DELETE FROM table, view
EXECUTE PROCEDURE	CALL Execution of any procedure or function or access to any variable, library, or cursor inside a package.
GRANT DIRECTORY	GRANT privilege ON directory REVOKE privilege ON directory
GRANT PROCEDURE	GRANT privilege ON procedure, function, package REVOKE privilege ON procedure, function, package
GRANT SEQUENCE	GRANT privilege ON sequence REVOKE privilege ON sequence
GRANT TABLE	GRANT privilege ON table, view, materialized view. REVOKE privilege ON table, view, materialized view
GRANT TYPE	GRANT privilege ON TYPE REVOKE privilege ON TYPE
INSERT TABLE	INSERT INTO table, view
LOCK TABLE	LOCK TABLE table, view
SELECT SEQUENCE	Any statement containing sequence.CURRVAL or sequence.NEXTVAL
SELECT TABLE	SELECT FROM table, view, materialized view
UPDATE TABLE	UPDATE table, view

Examples

Role Examples

To choose auditing for every SQL statement that creates, alters, drops, or sets a role, regardless of whether the statement completes successfully, issue the following statement:

AUDIT ROLE; 

To choose auditing for every statement that successfully creates, alters, drops, or sets a role, issue the following statement:

AUDIT ROLE
    WHENEVER SUCCESSFUL; 

To choose auditing for every CREATE ROLE, ALTER ROLE, DROP ROLE, or SET ROLE statement that results in an Oracle error, issue the following statement:

AUDIT ROLE
    WHENEVER NOT SUCCESSFUL; 

Query/Update Examples

To choose auditing for any statement that queries or updates any table, issue the following statement:

AUDIT SELECT TABLE, UPDATE TABLE; 

To choose auditing for statements issued by the users SCOTT and BLAKE that query or update a table or view, issue the following statement:

AUDIT SELECT TABLE, UPDATE TABLE
    BY scott, blake; 

Delete Example

To choose auditing for statements issued using the DELETE ANY TABLE system privilege, issue the following statement:

AUDIT DELETE ANY TABLE; 

Directory Examples

To choose auditing for statements issued using the CREATE ANY DIRECTORY system privilege, issue the following statement:

AUDIT CREATE ANY DIRECTORY;

To choose auditing for CREATE DIRECTORY (and DROP DIRECTORY) statements that do not use the CREATE ANY DIRECTORY system privilege, issue the following statement:

AUDIT DIRECTORY;