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.
AUDITsql_statement
[, sql_statement
...] [BYuser
[,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.
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.
SQL Statement Shortcut |
SQL Statements Represented |
CREATE CLUSTER AUDIT CLUSTER DROP CLUSTER TRUNCATE CLUSTER | |
CREATE DATABASE LINK DROP DATABASE LINK | |
CREATE DIRECTORY DROP DIRECTORY | |
CREATE INDEX ALTER INDEX DROP INDEX | |
All SQL statements that fail because a specified object does not exist | |
CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION DROP LIBRARY DROP PACKAGE DROP PROCEDURE | |
CREATE PROFILE ALTER PROFILE DROP PROFILE | |
CREATE PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK | |
CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM | |
CREATE ROLE ALTER ROLE DROP ROLE SET ROLE | |
CREATE ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT DROP ROLLBACK SEGMENT | |
CREATE SEQUENCE CROP SEQUENCE | |
Logins | |
CREATE SYNONYM DROP SYNONYM | |
AUDIT {SQL statements | system privileges} NOAUDIT {SQL statements | system privileges} | |
GRANT {system privileges and roles} REVOKE {system privileges and roles} | |
CREATE TABLE DROP TABLE TRUNCATE TABLE | |
CREATE TABLESPACE DROP TABLESPACE ALTER TABLESPACE | |
CREATE TRIGGER ALTER TRIGGER DROP TRIGGER ALTER TABLE schema.table ENABLE ALL TRIGGERS ALTER TABLE schema.table DISABLE ALL TRIGGERS | |
CREATE TYPE CREATE TYPE BODY ALTER TYPE DROP TYPE DROP TYPE BODY | |
CREATE USER ALTER USER DROP USER | |
CREATE VIEW DROP VIEW | |
All shortcuts listed in Table 7.2 |
SQL Statement Shortcut |
SQL Statements Represented |
COMMENT ON TABLE COMMENT ON COLUMNS | |
DELETE FROM {table or view} | |
Execution of any procedure or function, or access to any variable, library, or cursor inside a package | |
GRANT privilege ON directory REVOKE privilege ON directory | |
GRANT privilege ON procedure, function, or package REVOKE privilege ON procedure, function, or package | |
GRANT privilege ON sequence REVOKE privilege ON sequence | |
GRANT privilege ON table, view, or snapshot REVOKE privilege ON table, view, or snapshot | |
GRANT privilege ON TYPE REVOKE privilege ON TYPE | |
INSERT INTO {table or view} | |
LOCK TABLE {table or view} | |
Any statement containing sequence.CURRVAL or sequence.NEXTVAL | |
SELECT FROM {table, view, or snapshot} | |
UPDATE {table or view} |