AUDIT|V4.2.0| docs|Distributed Database

AUDIT

Last Updated：2023-10-31 11:17:11 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 by specifying 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 auditing type. Valid values: ALL and ALL STATEMENTS. `ALL` specifies to audit all the statements described in Table 1. `ALL STATEMENTS` specifies to audit all the statements described in `statement_operation`.
object_operation	The object auditing 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	Specifies to audit a statement only when the execution fails.
WHENEVER SUCCESSFUL	Specifies 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 actions 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 actions.
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	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

Log on as the ORAAUDITOR user before the 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.XXX.XXX.XXX -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
```