Oracle8i SQL Reference
Release 3 (8.1.7)
Part Number A85397-01

Library
Product
Contents
Index

Go to previous page Go to beginning of chapter Go to next page

SQL Statements:
ALTER TABLE to constraint_clause, 10 of 14

AUDIT

Purpose

Use the AUDIT statement to:

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

See Also: Oracle8i Reference for information on the AUDIT_TRAIL parameter 

Syntax


sql_statement_clause::=


auditing_by_clause::=


schema_object_clause::=


auditing_on_clause::=


Keywords and Parameters

sql_statement_clause

statement_option 

Specify a statement option to audit specific SQL statements.

See Also: Table 8-1 and Table 8-2 for a list of these statement options and the SQL statements they audit

 

 

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.

See Also: Oracle8i Reference for information on these views

 

system_privilege 

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

See Also: Table 11-1, " System Privileges" for a list of all system privileges and the SQL statements that they authorize

 

 

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 Also: GRANT for more information on these 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. 

 

ALL PRIVILEGES 

Specify ALL PRIVILEGES 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.  

 

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.

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

 

 

ON BEHALF OF 
  • 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 ALTER TABLE 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.  

 

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, 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, 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, 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  Procedure Function Packagea  Materialized View / Snapshot  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

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 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;

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 DELETE ANY TABLE system privilege, issue the following statement: 

AUDIT DELETE ANY TABLE;

Audit Statements Relating to Directories Example

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;

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:

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:

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index