Oracle7 Server SQL Reference

Contents Index Home Previous Next

AUDIT (SQL Statements)

Purpose

To choose specific SQL statements for auditing in subsequent user sessions. To choose particular schema objects for auditing, use the AUDIT command (Schema Objects).

Prerequisites

You must have AUDIT SYSTEM system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the users whose SQL statements you are auditing.

Syntax

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 4 - 7 and Table 4 - 8.

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 4 - 11.

BY user

chooses only SQL statements issued by specified users for auditing. If you omit this clause, Oracle7 audits all users' statements.

BY SESSION

causes Oracle7 to write a single record for all SQL statements of the same type issued in the same session.

BY ACCESS

causes Oracle7 to write one record for each audited statement.

If you specify statement options or system privileges that audit Data Definition Language statements, Oracle7 automatically audits by access regardless of whether you specify the BY SESSION or BY ACCESS option.

For statement options and system privileges that audit other types of SQL statements, you can specify either the BY SESSION or BY ACCESS option. BY SESSION is the default.

WHENEVER SUCCESSFUL

chooses auditing only for SQL statements that complete successfully.

NOT chooses auditing only for statements that fail, or result in errors.

If you omit the WHENEVER clause, Oracle7 audits SQL statements regardless of success or failure.

Auditing

Auditing keeps track of operations performed by database users. For each audited operation, Oracle7 produces an audit record containing this information:

Oracle7 writes audit records to the audit trail. The audit trail is a database table that contains audit records. You can review database activity by examining the audit trail through data dictionary views. For information on these views, see the "Data Dictionary" chapter of Oracle7 Server Reference.

How to Audit

To generate audit records, you must perform the following steps:

Enable auditing: You must enable auditing with the initialization parameter AUDIT_TRAIL.

Specify auditing options: To specify auditing options, you must use the AUDIT command. Auditing options choose which SQL commands, operations, database objects, and users Oracle7 audits. After you specify auditing options, they appear in the data dictionary. For more information on data dictionary views containing auditing options see the "Data Dictionary" chapter of Oracle7 Server Reference.

You can specify auditing options regardless of whether auditing is enabled. However, Oracle7 does not generate audit records until you enable auditing.

Auditing options specified by the AUDIT command (SQL Statements) apply only to subsequent sessions, rather than to current sessions.

Statement Options

Table 4 - 7 lists the statement options and the statements that they audit.

Statement Option SQL Statements and Operations
CLUSTER CREATE CLUSTER AUDIT CLUSTER DROP CLUSTER TRUNCATE CLUSTER
DATABASE LINK CREATE DATABASE LINK DROP DATABASE LINK
EXISTS All SQL statements that fail because an object, part of an object, or values already exists in the database. This option is only available with Trusted Oracle.
INDEX CREATE INDEX ALTER INDEX DROP INDEX
NOT EXISTS All SQL statements that fail because a specified object does not exist.
PROCEDURE CREATE FUNCTION CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION 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 options DROP TRIGGER ALTER TABLE with ENABLE ALL TRIGGERS and DISABLE ALL TRIGGERS clauses
USER CREATE USER ALTER USER DROP USER
VIEW CREATE VIEW DROP VIEW
Table 4 - 7. (continued) Statement Auditing Options

Short Cuts for System Privileges and Statement Options

Oracle7 provides short cuts for specifying system privileges and statement options. With these shortcuts, you can specify auditing for multiple system privileges and statement options at once:

CONNECT

This short cut is equivalent to specifying the CREATE SESSION system privilege.

RESOURCE

This short cut is equivalent to specifying the following system privileges:

DBA

This short cut is equivalent to the SYSTEM GRANT statement option and the following system privileges:

ALL

This short cut is equivalent to specifying all statement options shown in Table 4 - 7, but not the additional statement options shown in Table 4 - 8.

ALL PRIVILEGES

This short cut is equivalent to specifying all system privileges.

Oracle Corporation encourages you to choose individual system privileges and statement options for auditing, rather than these short cuts. These short cuts may not be supported in future versions of Oracle.

Additional Statement Options

Table 4 - 8 lists additional statement options and the SQL statements and operations that they audit. Note that these statement options are not included in the ALL short cut.

Statement Option

SQL Statements and Operations

ALTER SEQUENCE

ALTER SEQUENCE

ALTER TABLE

ALTER TABLE

COMMENT TABLE

COMMENT ON TABLE table, view, snapshot
COMMENT ON COLUMN table.column, view.column, snapshot.column

DELETE TABLE

DELETE FROM table, view

EXECUTE PROCEDURE

Execution of any procedure or function or access to any variable or cursor inside a package.

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, snapshot.
REVOKE privilege ON table, view, snapshot

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

UPDATE TABLE

UPDATE table, view

Table 4 - 8. Additional Statement Auditing Options

Example I

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 Oracle7 error, issue the following statement: 

AUDIT ROLE
 	WHENEVER NOT SUCCESSFUL

Example II

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

Example III

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

AUDIT DELETE ANY TABLE

Related Topics

AUDIT (Schema Objects) command [*] NOAUDIT (SQL Statements) command [*]

Contents Index Home Previous Next