Purpose
This statement is used to configure or delete audit rules for SQL statements.
Note
- When configuring audit rules for SQL statements, you must have the
AUDIT SYSTEMsystem privilege and log in as the ORAAUDITOR user. - After configuring audit rules for SQL statements, they take effect immediately for all sessions.
- You need to enable the audit feature through the system configuration item audit_trail for the audit rules configured by this statement to take effect.
Limitations and considerations
Before enabling audit, you need to unlock the ORAAUDITOR user.
To unlock the ORAAUDITOR user, here is an example:
ALTER USER ORAAUDITOR ACCOUNT UNLOCK;
For more information about audit, see Enable security audit.
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 type of statement audit operation.
|
| object_operation | The type of object audit operation. |
| obj_name | The name of the object to be audited. Supported object types include: table, view, and sequence. |
| ON DEFAULT | Sets the default audit rule, which applies to new objects created later. |
| user_name | The username. |
| WHENEVER NOT SUCCESSFUL | Triggers audit only when the execution fails. |
| WHENEVER SUCCESSFUL | Triggers audit only when the execution succeeds. |
The following table lists the SQL statements that can be audited.
Table 1
| Audit 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 operations that fail due to the nonexistence of an object. |
| 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 logout operations. |
| 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 Audit statements
| Audit 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
Audit all operations on the tables of user
user1if the operations are successful.obclient> AUDIT TABLE BY user1 WHENEVER SUCCESSFUL; Query OK, 0 rows affectedAudit all
INSERT,UPDATE, andDELETEoperations on thetbl1table of useruser2.obclient> AUDIT INSERT,UPDATE,DELETE ON user2.tbl1; Query OK, 0 rows affected