Statement Audits

Statement audits are those that record particular statements issued in the database. This section shows how to audit for certain SQL statements.

Here’s the syntax of the AUDIT command for SQL statement, privilege-based, and session auditing:

AUDIT statement | privilege [,statement | privilege]

[BY username [,username] ]

[BY SESSION | ACCESS ]

[WHENEVER [NOT] SUCCESSFUL];

To audit table commands, such as CREATE TABLE, DROP TABLE, and TRUNCATE TABLE:

SQL> audit table

  2  by ryan;



Audit succeeded.

To audit SELECTs, INSERTs, and DELETEs of tables:

SQL> audit select table, insert table, delete table;



Audit succeeded.

Syntax of the NOAUDIT command for SQL statement, privilege-based, and session auditing:

NOAUDIT statement | privilege [,statement | privilege]

[BY username [,username] ]

[WHENEVER [NOT] SUCCESSFUL];

Example:

SQL> noaudit select table, insert table, delete table;



Noaudit succeeded.

Privilege Audits

Privilege audits are those in which records are generated in the audit trail according to SQL statements that are issued based on privileges granted to a user. The syntax is the same as that for statement auditing.

To audit statements executed using the SELECT ANY TABLE system privilege:

SQL> audit select any table;



Audit succeeded.

To audit ALTER USER statements:

SQL> audit alter user;



Audit succeeded.

To audit unsuccessful DELETEs executed by ryan and test for each attempt based on the DELETE ANY TABLE system privilege:

SQL> audit delete any table

by ryan, test

by access

whenever not successful;



Audit succeeded.

---

NOTE:  
To audit sessions, statements, and privileges, you must either be a DBA or have been granted the AUDIT SYSTEM system privilege.

---

Object Audits

Object-level audits are those in which audit records are generated for actions that occur against database objects.

Object audit options:

Here’s the syntax of the AUDIT command for object-level auditing:

AUDIT option [,option]

ON [schema.]object_name

[BY SESSION | ACCESS]

[WHENEVER [NOT] SUCCESSFUL];

To audit data manipulation commands on returns_tbl:

SQL> audit insert, update, delete on returns_tbl;



Audit succeeded.

To audit all actions performed against the projects table by access:

SQL> audit all on projects by access;



Audit succeeded.

To audit all deletes on the employees table by access:

SQL> audit delete on employees by access;



Audit succeeded.

Here’s the syntax of the NOAUDIT command for object-level auditing:

NOAUDIT option [,option]

ON [schema.]object_name

[WHENEVER [NOT] SUCCESSFUL];

To deactivate all auditing options on the projects table:

SQL> noaudit all on projects;



Noaudit succeeded.

---

NOTE:  
To audit objects, you must either be a DBA, own the objects being audited, or have been granted the AUDIT ANY system privilege.

---