Oracle7 Server Concepts

Object Auditing

Object auditing is the selective auditing of specific DML statements (including queries), and GRANT and REVOKE statements for specific schema objects. Object auditing audits the operations permitted by object privileges, such as SELECT or DELETE statements on a given table, as well as the GRANT and REVOKE statements that control those privileges.

You can audit statements that reference tables, views, sequences, standalone stored procedures and functions, and packages (procedures in packages cannot be audited individually). Notice that statements that reference clusters, database links, indexes, or synonyms are not audited directly.

You can, however, audit access to these objects indirectly by auditing the operations that affect the base table. Object audit options are always set for all users of the database; these options cannot be set for a specific list of users. Oracle provides a mechanism for setting default object audit options for all auditable schema objects.

Object Audit Options for Views and Procedures

Because views and procedures (including stored functions, packages, and triggers) reference underlying objects in their definition, auditing with respect to views and procedures has several unique characteristics. Several audit records can potentially be generated as the result of using a view or a procedure. Not only is the use of the view or procedure subject to enabled audit options, but the SQL statements issued as a result of using the view or procedure are subject to the enabled audit options of the base objects (including default audit options).

As an illustration of this situation, consider the following series of SQL statements:

AUDIT SELECT ON emp;

CREATE VIEW emp_dept AS

	SELECT empno, ename, dname

		FROM emp, dept

		WHERE emp.deptno = dept.deptno;

AUDIT SELECT ON emp_dept;

SELECT * FROM emp_dept;

As a result of the query on EMP_DEPT, two audit records are generated: one for the query on the EMP_DEPT view and one for the query on the base table EMP (indirectly via the EMP_DEPT view). The query on the base table DEPT does not generate an audit record because the SELECT audit option for this table is not enabled. All audit records pertain to the user that queried the EMP_DEPT view.

The audit options for a view or procedure are determined when the view or procedure is first used and placed in the shared pool. These audit options remain set until the view or procedure is flushed from, and subsequently replaced in, the shared pool. Auditing an object invalidates that object in the cache and causes it to be reloaded. Any changes to the audit options of base objects are not observed by views and procedures in the shared pool. Continuing with the above example, if auditing of SELECT statements is turned off for the EMP table, use of the EMP_DEPT view would no longer generate an audit record for the EMP table.