O'Reilly logo

Oracle Security by Marlene Theriault, William Heney

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

General command syntax

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required