Oracle7 Server SQL Reference
AUDIT (Schema Objects)
Purpose
To choose a specific schema object for auditing. To choose particular SQL commands for auditing, use the AUDIT command (SQL Statements) described in the previous section of this chapter.
Prerequisites
The object you choose for auditing must be in your own schema or you must have AUDIT ANY system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the object's creation label or you must satisfy one of the following criteria:
- If the object's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges
- If the object's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.
If the object's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.
Syntax
Keywords and Parameters
object_opt specifies a particular operation for auditing. Table 4 - 9 shows each object option and the types of objects for which it applies.
schema
is the schema containing the object chosen for auditing. If you omit schema, Oracle7 assumes the object is in your own schema.
object
identifies the object chosen for auditing. The object must be one of the following types:
You can also specify a synonym for a table, view, sequence, procedure, stored function, package, or snapshot.
DEFAULT
establishes the specified object options as default object options for subsequently created objects.
If you omit both of the following options, Oracle7 audits by session.
BY SESSION means that Oracle7 writes a single record for all operations of the same type on the same object issued in the same session.
BY ACCESS
means that Oracle7 writes one record for each audited operation.
WHENEVER SUCCESSFUL
chooses auditing only for SQL statements that complete successfully.
NOT
chooses auditing only for statements that fail, or result in errors.
If you omit the WHENEVER clause entirely, Oracle7 audits all SQL statements, regardless of success or failure.
Auditing
Auditing keeps track of operations performed by database users. Fora brief conceptual overview of auditing including how to enable auditing, see the AUDIT command (SQL Statements) described ![[*]](jump.gif)
. Note that auditing options established by the AUDIT command (Schema Objects) apply to current sessions as well as to subsequent sessions.
Object Options
Table 4 - 9 shows the object options you can choose for each type of object.
| Object
Option
| Tables
| Views
| Sequences
| Procedures Functions Packages
| Snapshots
|
| ALTER
| 3
|
| 3
|
| 3
|
| AUDIT
| 3
| 3
| 3
| 3
| 3
|
| COMMENT
| 3
| 3
|
|
| 3
|
| DELETE
| 3
| 3
|
|
| 3
|
| EXECUTE
|
|
|
| 3
|
|
| GRANT
| 3
| 3
| 3
| 3
| 3
|
| INDEX
| 3
|
|
|
| 3
|
| INSERT
| 3
| 3
|
|
| 3
|
| LOCK
| 3
| 3
|
|
| 3
|
| RENAME
| 3
| 3
|
| 3
| 3
|
| SELECT
| 3
| 3
| 3
|
| 3
|
| UPDATE
| 3
| 3
|
|
| 3
|
Table 4 - 9. Object Auditing Options
The name of each object option specifies a command to be audited. For example, if you choose to audit a table with the ALTER option, Oracle7 audits all ALTER TABLE statements issued against the table. If you choose to audit a sequence with the SELECT option, Oracle7 audits all statements that use any of the sequence's values.
Short Cuts for Object Options
Oracle7 provides a short cut for specifying object auditing options:
ALL This short cut is equivalent to specifying all object options applicable for the type of object. You can use this short cut rather than explicitly specifying all options for an object.
Default Auditing
You can use the DEFAULT option of the AUDIT command to specify auditing options for objects that have not yet been created. Once you have established these default auditing options, any subsequently created object is automatically audited with those options. Note that the default auditing options for a view are always the union of the auditing options for the view's base tables.
If you change the default auditing options, the auditing options for previously-created objects remain the same. You can only change the auditing options for an existing object by specifying the object in the ON clause of the AUDIT command.
Example I
To choose auditing for every SQL statement that queries the EMP table in the schema SCOTT, issue the following statement:
AUDIT SELECT
ON scott.emp
To choose auditing for every statement that successfully queries the EMP table in the schema SCOTT, issue the following statement:
AUDIT SELECT
ON scott.emp
WHENEVER SUCCESSFUL
To choose auditing for every statement that queries the EMP table in the schema SCOTT and results in an Oracle7 error, issue the following statement:
AUDIT SELECT
ON scott.emp
WHENEVER NOT SUCCESSFUL
Example II
To choose auditing for every statement that inserts or updates a row in the DEPT table in the schema BLAKE, issue the following statement:
AUDIT INSERT, UPDATE
ON blake.dept
Example III
To choose auditing for every statement that performs any operation on the ORDER sequence in the schema ADAMS, issue the following statement:
AUDIT ALL
ON adams.order
The above statement uses the ALL short cut to choose auditing for the following statements that operate on the sequence:
- any statement that accesses the sequence's values using the pseudocolumns CURRVAL or NEXTVAL
Example IV
The following statement specifies default auditing options for objects created in the future:
AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE
ON DEFAULT
Any objects created later are automatically audited with the specified options that apply to them, provided that auditing has been enabled:
- If you create a table, Oracle7 automatically audits any ALTER, INSERT, UPDATE, or DELETE statements issued against the table.
- If you create a view, Oracle7 automatically audits any INSERT, UPDATE, or DELETE statements issued against the view.
- If you create a sequence, Oracle7 automatically audits any ALTER statements issued against the sequence.
- If you create a procedure, package, or function, Oracle7 automatically audits any ALTER statements issued against it.
Related Topics
AUDIT (SQL Statements) command
NOAUDIT (Schema Objects) command