Purpose
You can use this statement to configure or delete an audit rule for an SQL statement.
Note
- To configure an audit rule for an SQL statement, you must have the
AUDIT SYSTEMprivilege and log in as the ORAAUDITOR user, whose password is always ORAAUDITOR. - After an audit rule is configured for an SQL statement, it takes effect immediately for all sessions.
- An audit rule configured for a statement takes effect only after you enable the audit feature by specifying the audit_trail system parameter.
Syntax
/*Statement audit*/
{AUDIT | NOAUDIT} statement_operation_clause
[auditing_by_user_clause] [whenever_option]
/*Object audit*/
{AUDIT | NOAUDIT} object_operation_clause auditing_on_clause [whenever_option]
statement_operation_clause:
statement_operation_list
| ALL
| ALL STATEMENTS
statement_operation_list:
statement_operation [statement_operation...]
object_operation_clause:
object_operation_list
| ALL
object_operation_list:
object_operation [object_operation...]
auditing_on_clause:
ON obj_name
| ON DEFAULT
auditing_by_user_clause:
BY user_name [user_name...]
whenever_option:
WHENEVER NOT SUCCESSFUL
| WHENEVER SUCCESSFUL
statement_operation:
ALTER SYSTEM
| CLUSTER
| CONTEXT
| DATABASE LINK
| MATERIALIZED VIEW
| NOT EXISTS
| OUTLINE
| PROCEDURE
| PROFILE
| PUBLIC DATABASE LINK
| PUBLIC SYNONYM
| ROLE
| SEQUENCE
| SESSION
| SYNONYM
| SYSTEM AUDIT
| SYSTEM GRANT
| TABLE
| TABLESPACE
| TRIGGER
| TYPE
| USER
| VIEW
| ALTER SEQUENCE
| ALTER TABLE
| COMMENT TABLE
| DELETE TABLE
| EXECUTE PROCEDURE
| GRANT PROCEDURE
| GRANT SEQUENCE
| GRANT TABLE
| GRANT TYPE
| INSERT TABLE
| SELECT SEQUENCE
| SELECT TABLE
| UPDATE TABLE
object_operation:
ALTER
| AUDIT
| COMMENT
| DELETE
| EXECUTE
| FLASHBACK
| GRANT
| INDEX
| INSERT
| RENAME
| SELECT
| UPDATE
Parameters
| Parameter | Description |
|---|---|
| statement_operation | The statement auditing type. Valid values: ALL and ALL STATEMENTS.
|
| object_operation | The object auditing type. |
| obj_name | The name of the object to be audited. The object can be a table, view, or sequence. |
| ON DEFAULT | Sets the default audit rule for new objects. |
| user_name | The username. |
| WHENEVER NOT SUCCESSFUL | Specifies to audit a statement only when the execution fails. |
| WHENEVER SUCCESSFUL | Specifies to audit a statement only when the execution succeeds. |
The following tables describe the SQL statements that can be audited.
Table 1
| Statement | Description |
|---|---|
| ALTER SYSTEM | Audits the ALTER SYSTEM statement. |
| CLUSTER | Audits the ADD CLUSTER and REMOVE CLUSTER statements. |
| INDEX | Audits the CREATE INDEX, DROP INDEX, FLASHBACK INDEX, and PURGE INDEX statements. |
| NOT EXISTS | Audits actions that failed because the specified object does not exist. |
| OUTLINE | Audits the CREATE OUTLINE, ALTER OUTLINE, and DROP OUTLINE statements. |
| PROCEDURE | Audits the CREATE PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, DROP FUNCTION, CREATE PACKAGE, and DROP PACKAGE statements. |
| PROFILE | Audits the CREATE PROFILE, ALTER PROFILE, and DROP PROFILE statements. |
| ROLE | Audits the CREATE ROLE, ALTER ROLE, DROP ROLE, and SET ROLE statements. |
| SEQUENCE | Audits the CREATE SEQUENCE and DROP SEQUENCE statements. |
| SESSION | Audits login and logoff actions. |
| SYNONYM | Audits the CREATE SYNONYM and DROP SYNONYM statements. |
| SYSTEM AUDIT | Audits the AUDIT and NOAUDIT statements. |
| SYSTEM GRANT | Audits the GRANT and REVOKE statements. |
| TABLE | Audits the CREATE TABLE, DROP TABLE, and TRUNCATE TABLE statements. |
| TABLESPACE | Audits the CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. |
| TRIGGER | Audits the CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER statements. |
| TYPE | Audits the CREATE TYPE, DROP TYPE, CREATE TYPE BODY, and DROP TYPE BODY statements. |
| USER | Audits the CREATE USER, ALTER USER, and DROP USER statements. |
| VIEW | Audits the CREATE VIEW and DROP VIEW statements. |
Table 2
| Statement | Description |
|---|---|
| ALTER SEQUENCE | Audits the ALTER SEQUENCE statement. |
| ALTER TABLE | Audits the ALTER TABLE statement. |
| COMMENT TABLE | Audits the COMMENT ON TABLE and COMMENT ON VIEW statements. |
| DELETE TABLE | Audits the DELETE FROM TABLE and DELETE FROM VIEW statements. |
| EXECUTE PROCEDURE | Audits the CALL statement. |
| GRANT PROCEDURE | Audits the GRANT obj_privilege ON PROCEDURE \| FUNCTION \| PACKAGE and REVOKE obj_privilege ON PROCEDURE \| FUNCTION \| PACKAGE statements. |
| GRANT SEQUENCE | Audits the GRANT obj_privilege ON SEQUENCE and REVOKE obj_privilege ON SEQUENCE statements. |
| GRANT TABLE | Audits the GRANT obj_privilege ON TABLE \| VIEW and REVOKE obj_privilege ON TABLE \| VIEW statements. |
| GRANT TYPE | Audits the GRANT obj_privilege ON TYPE and REVOKE obj_privilege ON TYPE statements. |
| INSERT TABLE | Audits the INSERT INTO TABLE and INSERT INTO VIEW statements. |
| SELECT SEQUENCE | Audits all statements that contain sequence.CURRVAL or sequence.NEXTVAL. |
| SELECT TABLE | Audits the SELECT TABLE and SELECT VIEW statements. |
| UPDATE TABLE | Audits the UPDATE TABLE and UPDATE VIEW statements. |
Examples
Log in as the ORAAUDITOR user before the audit.
/*Unlock the ORAAUDITOR user.*/
obclient> ALTER USER ORAAUDITOR ACCOUNT UNLOCK;
Query OK, 0 rows affected
/*Log in as the ORAAUDITOR user.*/
[admin@k08j13249.eu95sqa /home/admin]
$obclient -h10.XXX.XXX.XXX -P2881 -uORAAUDITOR@Oracle -pORAAUDITOR
Audit the successful statements executed by
user1on all tables.obclient> AUDIT TABLE BY user1 WHENEVER SUCCESSFUL; Query OK, 0 rows affectedAudit the
INSERT,UPDATE, andDELETEstatements executed on thetbl1table of theuser2user.obclient> AUDIT INSERT,UPDATE,DELETE ON user2.tbl1; Query OK, 0 rows affected