AUDIT|V4.3.5|OceanBase Database| docs|Distributed Database

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 SYSTEM system 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. If `ALL` is specified, all operations in Table 1 are audited. If `ALL STATEMENTS` is specified, all operations in `statement_operation` are audited.
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 user1 if the operations are successful.

obclient> AUDIT TABLE BY user1 WHENEVER SUCCESSFUL;
Query OK, 0 rows affected

Audit all INSERT, UPDATE, and DELETE operations on the tbl1 table of user user2.

obclient> AUDIT INSERT,UPDATE,DELETE ON user2.tbl1;
Query OK, 0 rows affected

OceanBase

Customer Stories

Documentation

AUDIT