Statement Auditing

Statement auditing allows you to track who is issuing specific types of statements. The AUDIT statement form of the AUDIT command allows you to specify any SQL statement to be audited. You are also able to audit the use of a SQL statement by all users or a specific user.

AUDIT sql_statement[, sql_statement ...]
   [BY user[,user ...]]
   [BY SESSION [WHENEVER [NOT] SUCCESSFUL]
   [BY ACCESS [WHENEVER [NOT] SUCCESSFUL]

In this case, the sql_statement can be either a SQL statement or shortcut provided by Oracle. When you use one of the shortcuts, you are able to specify a group of SQL statements to be audited.

Tip

There is a certain level of overlap between statement auditing and privilege auditing. For example, the AUDIT CREATE TABLE statement is regarded as both a statement audit command (CREATE TABLE SQL statement) and a privilege audit command (CREATE TABLE system privilege). See Chapter 13, for a list of SQL statements relevant to the DBA, and Chapter 15, for a list of all system privileges.

Table 7.2 provides a list of shortcuts for DDL statements; Table 7-3 provides a list of shortcuts for DML statements.

Table 7-2. DML Statement Auditing Shortcuts

SQL Statement Shortcut

SQL Statements Represented

CLUSTER

CREATE CLUSTER

AUDIT CLUSTER

DROP CLUSTER

TRUNCATE CLUSTER

DATABASE LINK

CREATE DATABASE LINK

DROP DATABASE LINK

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

PROCEDURE

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

CROP SEQUENCE

SESSION

Logins

SYNONYM

CREATE SYNONYM

DROP SYNONYM

SYSTEM AUDIT

AUDIT {SQL statements | system privileges}

NOAUDIT {SQL statements | system privileges}

SYSTEM GRANT

GRANT {system privileges and roles}

REVOKE {system privileges and roles}

TABLE

CREATE TABLE

DROP TABLE

TRUNCATE TABLE

TABLESPACE

CREATE TABLESPACE

DROP TABLESPACE

ALTER TABLESPACE

TRIGGER

CREATE TRIGGER

ALTER TRIGGER

DROP TRIGGER

ALTER TABLE schema.table ENABLE ALL TRIGGERS

ALTER TABLE schema.table DISABLE ALL TRIGGERS

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

ALL

All shortcuts listed in Table 7.2

Table 7-3. DDL Statements Auditing Shortcuts

SQL Statement Shortcut

SQL Statements Represented

COMMENT TABLE

COMMENT ON TABLE

COMMENT ON COLUMNS

DELETE TABLE

DELETE FROM {table or view}

EXECUTE PROCEDURE

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, or package

REVOKE privilege ON procedure, function, or package

GRANT SEQUENCE

GRANT privilege ON sequence

REVOKE privilege ON sequence

GRANT TABLE

GRANT privilege ON table, view, or snapshot

REVOKE privilege ON table, view, or snapshot

GRANT TYPE

GRANT privilege ON TYPE

REVOKE privilege ON TYPE

INSERT TABLE

INSERT INTO {table or view}

LOCK TABLE

LOCK TABLE {table or view}

SELECT SEQUENCE

Any statement containing sequence.CURRVAL or sequence.NEXTVAL

SELECT TABLE

SELECT FROM {table, view, or snapshot}

UPDATE TABLE

UPDATE {table or view}

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset