AUDIT

Purpose

Use the AUDIT statement to:

• Track the occurrence of SQL statements in subsequent user sessions. You can track the occurrence of a specific SQL statement or of all SQL statements authorized by a particular system privilege. Auditing operations on SQL statements apply only to subsequent sessions, not to current sessions.
• Track operations on a specific schema object. Auditing operations on schema objects apply to current sessions as well as to subsequent sessions.

  See Also: Oracle9i Supplied PL/SQL Packages and Types Reference for information on the DBMS_FGA package, which lets you create and administer value-based auditing policies NOAUDIT for information on disabling auditing of SQL statement

Prerequisites

To audit occurrences of a SQL statement, you must have AUDIT SYSTEM system privilege.

To audit operations on a schema object, the object you choose for auditing must be in your own schema or you must have AUDIT ANY system privilege. In addition, if the object you choose for auditing is a directory object, even if you created it, then you must have AUDIT ANY system privilege.

To collect auditing results, you must set 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.

Syntax

audit::=

Text description of audit

sql_statement_clause::=

Text description of sql_statement_clause

auditing_by_clause::=

Text description of auditing_by_clause

schema_object_clause::=

Text description of schema_object_clause

auditing_on_clause::=

Text description of auditing_on_clause

Semantics

sql_statement_clause

Use the sql_statement_clause to audit SQL statements.

statement_option

Specify a statement option to audit specific SQL statements.

For each audited operation, Oracle produces an audit record containing this information:

• The user performing the operation
• The type of operation
• The object involved in the operation
• The 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.

system_privilege

Specify a system privilege to audit SQL statements that are authorized by the specified system privilege.

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.

Oracle also provides two shortcuts for specifying groups of system privileges and statement options at once:

ALL

Specify ALL to audit all statements options shown in Table 12-1 but not the additional statement options shown in Table 12-2.

ALL PRIVILEGES

Specify ALL PRIVILEGES to audit system privileges.

auditing_by_clause

Specify the auditing_by_clause to audit only those SQL statements issued by particular users. If you omit this clause, then Oracle audits all users' statements.

BY user

Use this clause to restrict auditing to only SQL statements issued by the specified users.

BY proxy

Use this clause to restrict auditing to only SQL statements issued by the specified proxies.

ON BEHALF OF

Specify user to indicate auditing of statements executed on behalf of a particular user. ANY indicates auditing of statements executed on behalf of any user.

schema_object_clause

Use the schema_object_clause to audit operations on schema objects.

object_option

Specify the particular operation for auditing. Table 12-3 shows each object option and the types of objects to which it applies. The name of each object option specifies a SQL statement to be audited. For example, if you choose to audit a table with the ALTER option, then Oracle audits all ALTER TABLE statements issued against the table. If you choose to audit a sequence with the SELECT option, then Oracle audits all statements that use any of the sequence's values.

ALL

Specify ALL as a shortcut equivalent to specifying all object options applicable for the type of object.

auditing_on_clause

The auditing_on_clause lets you specify the particular schema object to be audited.

schema

Specify the schema containing the object chosen for auditing. If you omit schema, then Oracle assumes the object is in your own schema.

object

Specify the name of the object to be audited. The object must be a table, view, sequence, stored procedure, function, package, materialized view, or library.

You can also specify a synonym for a table, view, sequence, procedure, stored function, package materialized view, or user-defined type.

ON DEFAULT

Specify ON DEFAULT to establish the specified object options as default object options for subsequently created objects. Once you have established these default auditing options, any subsequently created object is automatically audited with those options. The default auditing options for a view are always the union of the auditing options for the view's base tables. You can see the current default auditing options by querying the ALL_DEF_AUDIT_OPTS data dictionary view.

If you change the default auditing options, then the auditing options for previously created objects remain the same. You can change the auditing options for an existing object only by specifying the object in the ON clause of the AUDIT statement.

ON DIRECTORY directory_name

The ON DIRECTORY clause lets you specify the name of a directory chosen for auditing.

BY SESSION

Specify BY SESSION if you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.

BY ACCESS

Specify BY ACCESS if you want Oracle to write one record for each audited statement and operation.

If you specify statement options or system privileges that audit data definition language (DDL) statements, then 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 [NOT] SUCCESSFUL

Specify WHENEVER SUCCESSFUL to audit only SQL statements and operations that succeed.

Specify WHENEVER NOT SUCCESSFUL to audit only statements and operations that fail or result in errors.

If you omit this clause, then Oracle performs the audit regardless of success or failure.

Tables of Auditing Options

Table 12-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 SEGMENT	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
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 12-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

Table 12-3  Object Auditing Options

Object Option	Table	View	Sequence	Procedure, Function, Packagea	Materialized View	Directory	Library	Object Type	Context
ALTER	X	--	X	--	X	--	--	X	--
AUDIT	X	X	X	X	X	X	--	X	X
COMMENT	X	X	--	--	X	--	--	--	--
DELETE	X	X	--	--	X	--	--	--	--
EXECUTE	--	--	--	X	--	--	X	--	--
GRANT	X	X	X	X	--	X	X	X	X
INDEX	X	--	--	--	X	--	--	--	--
INSERT	X	X	--	--	X	--	--	--	--
LOCK	X	X	--	--	X	--	--	--	--
READ	--	--	--	--	--	X	--	--	--
RENAME	X	X	--	X	X	--	--	--	--
SELECT	X	X	X	--	X	--	--	--	--
UPDATE	X	X	--	--	X	--	--	--	--
a Java schema objects (sources, classes, and resources) are considered the same as procedures, functions, and packages for purposes of auditing options.

Examples

Auditing SQL Statements Relating to Roles: Example

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; 

Auditing Query and Update SQL Statements: Example

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 hr and oe that query or update a table or view, issue the following statement:

AUDIT SELECT TABLE, UPDATE TABLE
    BY hr, oe; 

Auditing Deletions: Example

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

AUDIT DELETE ANY TABLE; 

Auditing Statements Relating to Directories: 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;

To choose auditing for every statement that reads files from the bfile_dir directory, issue the following statement:

AUDIT READ ON DIRECTORY bfile_dir;

Auditing Queries on a Table: Example

To choose auditing for every SQL statement that queries the employees table in the schema hr, issue the following statement:

AUDIT SELECT
    ON hr.employees; 

To choose auditing for every statement that successfully queries the employees table in the schema hr, issue the following statement:

AUDIT SELECT 
    ON hr.employees
    WHENEVER SUCCESSFUL; 

To choose auditing for every statement that queries the employees table in the schema hr and results in an Oracle error, issue the following statement:

AUDIT SELECT 
    ON hr.employees
    WHENEVER NOT SUCCESSFUL; 

Auditing Inserts and Updates on a Table: Example

To choose auditing for every statement that inserts or updates a row in the customers table in the schema oe, issue the following statement:

AUDIT INSERT, UPDATE
    ON oe.customers; 

Auditing Operations on a Sequence: Example

To choose auditing for every statement that performs any operation on the employees_seq sequence in the schema hr, issue the following statement:

AUDIT ALL
    ON hr.employees_seq; 

The preceding statement uses the ALL shortcut to choose auditing for the following statements that operate on the sequence:

• ALTER SEQUENCE
• AUDIT
• GRANT
• any statement that accesses the sequence's values using the pseudocolumns CURRVAL or NEXTVAL

Setting Default Auditing Options: Example

The following statement specifies default auditing options for objects created in the future:

AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE
    ON DEFAULT; 

Any objects created later are automatically audited with the specified options that apply to them, if auditing has been enabled:

• If you create a table, then Oracle automatically audits any ALTER, GRANT, INSERT, UPDATE, or DELETE statements issued against the table.
• If you create a view, then Oracle automatically audits any GRANT, INSERT, UPDATE, or DELETE statements issued against the view.
• If you create a sequence, then Oracle automatically audits any ALTER or GRANT statements issued against the sequence.
• If you create a procedure, package, or function, then Oracle automatically audits any ALTER or GRANT statements issued against it.