Oracle Security

Types of Auditing

There are several different forms of auditing that can be enabled. Each form covers a different area of interest within the database:

• Statement-level auditing

• System-level or privilege auditing

• Object-level auditing

The general command syntax used to enable any form of auditing is shown in Figure 10.1.

Figure 10-1. General command syntax

Statement-Level Auditing

Statement-level auditing falls into two categories: Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements. This type of auditing can be very broad or very specific. The statement-level audits are based on the type of SQL statement presented. An example of a statement-level audit would be to audit any action performed on tables, such as CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, etc. Another example of statement-level auditing would be to audit specific actions of one user on a session-by-session basis.

Enabling and viewing statement-level auditing

To view what statement-level auditing has been enabled in a database, you can issue the statement:

SQL> AUDIT SESSION BY mary;
Audit succeeded.
SQL> SELECT user_name, audit_option, success, failure 
   2   FROM sys.dba_stmt_audit_opts;
USER_NAME   AUDIT_OPTION                             SUCCESS    FAILURE
----------- ---------------------------------------- ---------- ----------
MARY        CREATE SESSION                           BY ACCESS  BY ACCESS

In this example, we see that mary