Description
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 on as the ORAAUDITOR user. The password is ORAAUDITOR.After an audit rule for an SQL statement is configured, it takes effect immediately on all sessions.
An audit rule configured for a statement takes effect only if you set the system configuration audit_trail to enable the audit feature.
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
| NOT EXISTS
| OUTLINE
| PROCEDURE
| PROFILE
| 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
| Field | Description |
|---|---|
| statement_operation | Specifies the statement auditing type. Valid values: All and ALL STATEMENTS. * ALL indicates to audit all the statements described in Table 1 . * ALL STATEMENTS indicates to audit all the statements described in statement_operation. |
| object_operation | Specifies the object auditing type. |
| obj_name | Specifies the name of the object to be audited. Valid values: * table * view * sequence |
| ON DEFAULT | Sets the default audit rule for new objects. |
| user_name | Specifies the username. |
| WHENEVER NOT SUCCESSFUL | Indicates to audit a statement only when the execution fails. |
| WHENEVER SUCCESSFUL | Indicates 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 | ALTER SYSTEM statements |
| CLUSTER | ADD CLUSTER and REMOVE CLUSTER statements |
| INDEX | CREATE/DROP/FLASHBACK/PURGE INDEX statements |
| NOT EXISTS | Audits actions that failed because the specified object does not exist. |
| OUTLINE | CREATE/ALTER/DROP OUTLINE statements |
| PROCEDURE | CREATE/DROP PROCEDURE, CREATE/DROP FUNCTION, and CREATE/DROP PACKAGE statements |
| PROFILE | CREATE/ALTER/DROP PROFILE statements |
| ROLE | CREATE/ALTER/DROP/SET ROLE statements |
| SEQUENCE | CREATE/DROP SEQUENCE statements |
| SESSION | Audits logon and logoff actions. |
| SYNONYM | CREATE/DROP SYNONYM statements |
| SYSTEM AUDIT | AUDIT/NOAUDIT statements |
| SYSTEM GRANT | GRANT/REVOKE statements |
| TABLE | CREATE/DROP/TRUNCATE TABLE statements |
| TABLESPACE | CREATE/ALTER/DROP TABLESPACE statements |
| TRIGGER | CREATE/ALTER/DROP USER statements |
| TYPE | CREATE/DROP TYPE or CREATE/DROP TYPE BODY statements |
| USER | CREATE/ALTER/DROP USER statements |
| VIEW | CREATE/DROP VIEW statements |
Table 2
| Statement | Description |
|---|---|
| ALTER SEQUENCE | ALTER SEQUENCE statements |
| ALTER TABLE | ALTER TABLE statements |
| COMMENT TABLE | COMMENT ON TABLE/VIEW statements |
| DELETE TABLE | DELETE FROM TABLE/VIEW statements |
| EXECUTE PROCEDURE | CALL statements |
| GRANT PROCEDURE | GRANT/REVOKE obj_privilege ON PROCEDURE/FUNCTION/PACKAGE statements |
| GRANT SEQUENCE | GRANT/REVOKE obj_privilege ON SEQUENCE statements |
| GRANT TABLE | GRANT/REVOKE obj_privilege ON TABLE/VIEW statements |
| GRANT TYPE | GRANT/REVOKE obj_privilege ON TYPE statements |
| INSERT TABLE | INSERT INTO TABLE/VIEW statements |
| SELECT SEQUENCE | All statements that contain sequence.CURRVAL or sequence.NEXTVAL |
| SELECT TABLE | SELECT TABLE/VIEW statements |
| UPDATE TABLE | UPDATE TABLE/VIEW statements |
Examples
- Audit table-related statements of the test user when they are executed.
//Unlock the ORAAUDITOR user.
obclient> alter user ORAAUDITOR account unlock;
Query OK, 0 rows affected (5.76 sec)
//Log on as the ORAAUDITOR user.
obclient> exit;
Bye
[admin@k08j13249.eu95sqa /home/admin]
$obclient -h10.10.10.10 -P2883 -uORAAUDITOR@Oracle#test -pORAAUDITOR
obclient> AUDIT TABLE BY test WHENEVER SUCCESSFUL;
Query OK, 0 rows affected (21.14 sec)
- Audit all INSERT, UPDATE, and DELETE statements for table t1.
obclient> AUDIT INSERT, UPDATE, DELETE on t1;
Query OK, 0 rows affected (31.62 sec)