Purpose
This statement is used to configure audit rules for SQL statements or database objects. NOAUDIT is used to cancel a configured audit rule.
Note
After an audit rule is configured, it takes effect immediately for all sessions. Whether the rule is actually applied to log records also depends on whether the audit feature is enabled and the configuration of the audit log policy. For more information, see Enable security audit.
Limitations and considerations
- You must have the required privileges to execute the
AUDITorNOAUDITstatement. - Some audit options take effect only after the tenant's audit mode is set to compatibility mode. Please confirm the specific effective behaviors based on the security audit configuration of the current version.
- In MySQL-compatible mode, in addition to the
AUDITandNOAUDITstatements, you can also use functions such asAUDIT_LOG_FILTER_SET_FILTERandAUDIT_LOG_FILTER_SET_USERto configure audit filters. These two methods belong to different audit configuration interfaces. For more information, see Enable security audit.
Syntax
/*Statement audit*/
{AUDIT | NOAUDIT}
{ statement_operation_list | ALL | ALL STATEMENTS }
[BY user_name [, user_name]...]
[BY ACCESS]
[WHENEVER [NOT] SUCCESSFUL]
/*Object Audit*/
{AUDIT | NOAUDIT}
{ object_operation_list | ALL }
ON { obj_name | DEFAULT }
[WHENEVER [NOT] SUCCESSFUL]
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
Parameters
Parameter |
Description |
|---|---|
| statement_operation_list | The list of operations on the SQL statements to be audited. |
| ALL | Audit all auditable statements. |
| ALL STATEMENTS | Audit all statements. |
| user_name | List of user names to be audited. Separate multiple user names with commas (,). |
| BY ACCESS | An audit record is generated for each audit operation. |
| WHENEVER NOT SUCCESSFUL | Only failed operations are audited. |
| WHENEVER SUCCESSFUL | Only operations that are executed successfully are audited. |
| object_operation_list | The list of operations on the audited object. |
| obj_name | The name of the object to be audited. |
| DEFAULT | Sets the default audit options to be applied to objects created subsequently. |
The following table lists common statement-level audit operations.
Audit operations |
Description |
|---|---|
| ALTER SYSTEM | Audit ALTER SYSTEM statement. |
| CLUSTER | Audit ADD CLUSTER and REMOVE CLUSTER statements. |
| CONTEXT | Audit CREATE CONTEXT, ALTER CONTEXT, and DROP CONTEXT statements. |
| INDEX | Audit CREATE INDEX, DROP INDEX, FLASHBACK INDEX, and PURGE INDEX statements. |
| NOT EXISTS | Audit operations that fail due to non-existent objects. |
| OUTLINE | Audit CREATE OUTLINE, ALTER OUTLINE, and DROP OUTLINE statements. |
| PROCEDURE | Audit CREATE PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, DROP FUNCTION, CREATE PACKAGE, and DROP PACKAGE statements. |
| PROFILE | Audit CREATE PROFILE, ALTER PROFILE, and DROP PROFILE statements. |
| SESSION | Audit login and logout operations. |
| SYSTEM AUDIT | Audit AUDIT and NOAUDIT statements. |
| SYSTEM GRANT | Audit GRANT and REVOKE statements. |
| TABLE | Audit CREATE TABLE, DROP TABLE, and TRUNCATE TABLE statements. |
| TABLESPACE | Audit CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. |
| TRIGGER | Audit CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER statements. |
| TYPE | Audit CREATE TYPE, DROP TYPE, CREATE TYPE BODY, and DROP TYPE BODY statements. |
| USER | Audit CREATE USER, ALTER USER, and DROP USER statements. |
| VIEW | Audit CREATE VIEW and DROP VIEW statements. |
The following table lists common object-level audit operations.
Audit operations |
Description |
|---|---|
| ALTER TABLE | Audit ALTER TABLE statement. |
| COMMENT TABLE | Audit COMMENT ON TABLE and COMMENT ON VIEW statements. |
| DELETE TABLE | Audit DELETE FROM TABLE and DELETE FROM VIEW statements. |
| EXECUTE PROCEDURE | Audit CALL statement. |
| GRANT PROCEDURE | Audit GRANT obj_privilege ON PROCEDURE | FUNCTION | PACKAGE and REVOKE obj_privilege ON PROCEDURE | FUNCTION | PACKAGE statements. |
| GRANT TABLE | Audit GRANT obj_privilege ON TABLE | VIEW and REVOKE obj_privilege ON TABLE | VIEW statements. |
| INSERT TABLE | Audit INSERT INTO TABLE and INSERT INTO VIEW statements. |
| SELECT TABLE | Audit SELECT TABLE and SELECT VIEW statements. |
| UPDATE TABLE | Audit UPDATE TABLE and UPDATE VIEW statements. |
Examples
Enable the audit feature
Enable the audit feature in the MySQL-compatible tenant and configure an audit log policy.
obclient> ALTER SYSTEM SET audit_log_enable = TRUE;
Configure statement auditing
Audit the successful operations of user
rooton the table.obclient> AUDIT TABLE BY root WHENEVER SUCCESSFUL;Audit the query operations of the
rootuser on tables.obclient> AUDIT SELECT TABLE BY root WHENEVER SUCCESSFUL;
Configure object auditing
Audit failed insert, update, and delete operations on the
test.t1table.obclient> AUDIT INSERT, UPDATE, DELETE ON test.t1 WHENEVER NOT SUCCESSFUL;Set default object audit options.
obclient> AUDIT ALL ON DEFAULT;
Cancel an audit
Cancel the audit of table operations on user
root.obclient> NOAUDIT TABLE BY root;Cancel object auditing for table
test.t1.obclient> NOAUDIT INSERT, UPDATE, DELETE ON test.t1;Remove the default object audit option.
obclient> NOAUDIT ALL ON DEFAULT;