AUDIT |V3.2.4|OceanBase Database| docs|Distributed Database

AUDIT

Last Updated：2023-10-24 09:23:03 Updated

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 SYSTEM privilege and log on 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 through 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 audit type. Valid values: `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	The object audit 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	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	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 statements 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 logon and logoff statements.
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	Audit 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 on as the ORAAUDITOR user before audit.

/*Unlock the ORAAUDITOR user.*/
obclient> ALTER USER ORAAUDITOR ACCOUNT UNLOCK;
Query OK, 0 rows affected

/*Log on as the ORAAUDITOR user.*/
[admin@k08j13249.eu95sqa /home/admin]
$obclient -h10.10.10.10 -P2881 -uORAAUDITOR@Oracle -pORAAUDITOR

Audit the successful statements executed by user1 on all tables.

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

Audit the INSERT, UPDATE, and DELETE statements executed on the tbl1 table of the user2 user.
```
obclient> AUDIT INSERT,UPDATE,DELETE ON user2.tbl1;
Query OK, 0 rows affected
```