RelationalDBDesign RelationalDBDesign

Auditing Features  «Prev  Next»
Lesson 7 Auditing access to schema objects
Objective Audit the Use of Database Objects in Oracle.

Audit the Use of Database Objects in Oracle

The final area that you can audit is the use of database objects. Auditing database objects in a particular schema is the most limited form of auditing, because you direct the audit process to a single object or group of objects. Auditing database objects is frequently used to analyze usage characteristics that may affect performance, as well as to watch for security violations.


The syntax for specifying auditing on schema objects is very similar to the syntax described for the other auditing options. To audit a specific type of statement on a particular object, use the syntax

AUDIT statement ON object_name

where statement is any SQL statement and object_name is the name of the object to be audited. If the object is in the current schema, you do not have to specify a schema name; if not, you will have to use the notation schema.object_name.
You can two of the same options for auditing database objects that you can for auditing other types of SQL statements, such as BY SESSION/ACCESS and WHENEVER [NOT] SUCCESSFUL. You cannot use the BY USER clause if you are auditing a specific database object.

Default auditing

There may be times when you want to specify a standard type of auditing for any objects that are created in a schema in the future. You can use the ON DEFAULT clause to indicate the statements you want to audit.
For instance, if you want to audit all INSERT, UPDATE, and DELETE statements issued against any objects created in the schema, use the command


This automatically turns on auditing on all INSERT, UPDATE, and DELETE statements for all objects subsequently created in the schema.
To change the audits on these new objects, you would have to issue an overriding AUDIT statement for the object or use the NOAUDIT command to remove the auditing option.
In the next lesson, you will learn how to get rid of audit records you no longer need.