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.
Syntax
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
AUDIT INSERT, UPDATE, DELETE ON DEFAULT;
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.