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: NOAUDIT for information on disabling auditing of SQL statement

Prerequisites

To audit occurrences of a SQL statement, you must have AUDITSYSTEM 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 AUDITANY system privilege. In addition, if the object you choose for auditing is a directory object, even if you created it, you must have AUDITANY 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.

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 8-1 but not the additional statement options shown in Table 8-2.

ALLPRIVILEGES

Specify ALLPRIVILEGES to audit 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.

auditing_by_clause

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

BYuser

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

BYproxy

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

See Also:Oracle8i Concepts for more information on proxies and their use of the database

ONBEHALFOF

user indicates auditing of statements executed on behalf of a particular user.

ANY indicates auditing of statements executed on behalf of any user.

schema_object_clause

object_option

Specify the particular operation for auditing. Table 8-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, Oracle audits all ALTERTABLE statements issued against the table. If you choose to audit a sequence with the SELECT option, 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, 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, or materialized view.

ONDEFAULT

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, 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.

ONDIRECTORYdirectory_name

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

BYSESSION

Specify BYSESSION 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.

BYACCESS

Specify BYACCESS 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, Oracle automatically audits by access regardless of whether you specify the BYSESSION clause or BYACCESS clause.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BYSESSION or BY ACCESS. BYSESSION is the default.

WHENEVER [NOT] SUCCESSFUL

Specify WHENEVERSUCCESSFUL to audit only SQL statements and operations that succeed.

Specify WHENEVERNOTSUCCESSFUL to audit only statements and operations that fail or result in errors.

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

Tables of Auditing Options

Table 8-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

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 8-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 8-3 Object Auditing Options

Object Option

Table

View

Sequence

ProcedureFunctionPackagea

Materialized View / Snapshot

Directory

Library

ObjectType

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

Audit 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 CREATEROLE, ALTERROLE, DROPROLE, or SETROLE statement that results in an Oracle error, issue the following statement:

AUDIT ROLE
WHENEVER NOT SUCCESSFUL;

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

AUDIT SELECT TABLE, UPDATE TABLE
BY scott, blake;

Audit Deletions Example

To choose auditing for statements issued using the DELETEANYTABLE system privilege, issue the following statement:

AUDIT DELETE ANY TABLE;

Audit Statements Relating to Directories Example

To choose auditing for statements issued using the CREATEANYDIRECTORY system privilege, issue the following statement:

AUDIT CREATE ANY DIRECTORY;

To choose auditing for CREATEDIRECTORY (and DROPDIRECTORY) statements that do not use the CREATEANYDIRECTORY system privilege, issue the following statement:

AUDIT DIRECTORY;

Audit Queries on a Table Example

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

AUDIT SELECT
ON scott.emp;

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

AUDIT SELECT
ON scott.emp
WHENEVER SUCCESSFUL;

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

AUDIT SELECT
ON scott.emp
WHENEVER NOT SUCCESSFUL;

Audit Inserts and Updates on a Table Example

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

AUDIT INSERT, UPDATE
ON blake.dept;

Audit All Operations on a Sequence Example

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

AUDIT ALL
ON adams.order;

The above 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

Audit Read Operations on a Directory Example

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

AUDIT READ ON DIRECTORY bfile_dir1;

Set 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, provided that auditing has been enabled:

If you create a table, Oracle automatically audits any ALTER, GRANT, INSERT, UPDATE, or DELETE statements issued against the table.

If you create a view, Oracle automatically audits any GRANT, INSERT, UPDATE, or DELETE statements issued against the view.

If you create a sequence, Oracle automatically audits any ALTER or GRANT statements issued against the sequence.

If you create a procedure, package, or function, Oracle automatically audits any ALTER or GRANT statements issued against it.