All Products
Search
Document Center

AUDIT

Last Updated: Jun 18, 2021

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 SYSTEM privilege 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)