Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Using Privilege Auditing

Oracle Tips by Burleson

Privilege auditing involves the tracking of the SQL statements issued by users who have been granted the right to execute that statement through a system privilege. In order to enable or disable auditing on SQL statements, you must have the AUDIT SYSTEM privilege. Privilege audit options match the corresponding system privileges. For example, in order to audit the DELETE ANY TABLE system privilege, you would issue the following command:


Using Object Auditing

Object auditing involves the tracking of the specific SQL DML statements issued against objects by database users. In order to enable or disable auditing on objects, you must have the AUDIT ANY system privilege or own the object. The following objects can be audited: tables, views, sequences, packages, standalone stored procedures and standalone functions, directories, libraries, and types. Since views and procedures may contain statements that reference multiple objects, they may generate multiple records in the audit trail. You cannot limit object auditing with the BY USER clause; all users will be audited for the object.

Managing the Audit Trail

Audit records are written when the statement is executed. Even if a statement or transaction is rolled back, the audit trail record remains. Auditing BY SESSION will write only one record to the audit trail. Auditing BY ACCESS will write multiple records, one for each time the action is executed.

The audit trail grows according to the following factors:

* The number of audit options enabled

* The number of audited actions issued

You need to control the growth of the audit trail with the following methods:

* Enable and disable auditing options.

* Be selective in deciding which auditing to turn on.

* Control who can perform auditing.

In order to control the auditing of objects, Oracle recommends that all objects be owned by a separate schema that does not correspond to an actual user and that is not granted CONNECT SESSION. This will prevent anyone from connecting as the owner of these objects and turning on auditing for the objects. The AUDIT ANY system privilege should not be granted to anyone except the security administrator. An alternate method is to have all the objects owned by the security administrator.

If the audit trail record cannot be written, the audited action will not be executed and errors will be generated. If connections are being audited and the audit trail table (SYS.AUD$) is full, users will not be allowed to log in to the database. The DBA can CONNECT INTERNAL using Server Manager in either GUI or command-line mode and clean out the audit trail.

You should develop an auditing strategy by evaluating the purpose of the auditing and be conservative in the auditing performed. When auditing is being set to investigate possible problems, begin with general auditing and then narrow it down to specific actions. If the purpose of the audit is to provide a record of historical activity, remember to archive (to another table or an export file) and purge data on a regular basis. You can selectively insert records to another table if only a subset needs to be maintained.

Take these recommended steps to truncate the audit trail:

1.    Copy any subset of records you may need to keep into a temporary table, or export the entire SYS.AUD$ table.


3.    Truncate the SYS.AUD$ table.

4.    Insert records from the temporary table back into SYS.AUD$.

To protect the audit trail, AUDIT insert, update, and delete on SYS.AUD$ BY ACCESS (or in whichever user you have created the AUD$ table) so that records cannot be added, changed, or deleted without that action being audited. Even if a user is able to delete records from SYS.AUD$, he or she will not be able to delete the records generated by his or her actions without creating additional audit records. In addition, users should not be granted DELETE ANY TABLE system privilege. Always protect the audit trail (SYS.AUD$).

The maximum size allowed for an audit trail written to the database is determined at the time the database is created. By default, the size reflects the system tablespace default values. The sql.bsq script, which is executed when the database is created, sets the size of the SYS.AUD$ table. It is very important that the audit trail be cleaned up regularly. You should export the data and truncate the SYS.AUD$ table on a regular basis.

If you want to implement processes such as notification when a user logs on, checking of passwords, automated invalidation of passwords, and you don’t have Oracle8 or later, you will want to move the AUD$ table out of the SYS user so that triggers can be created against it. By creating an ON INSERT trigger that monitors for specific event codes in the AUD$ table, you can then use that trigger to implement specific database actions.

Follow these steps to create the AUD$ table in a different user:

1.    Turn off auditing.

2.    Export the current records in the AUD$ table.

3.    Drop the SYS.AUD$ table.

4.    Create the new AUD$ table either under SYS in a different tablespace or as a different user.

5.    If step 4 created an AUD$ table in a different user, grant all on that AUD$ to SYS user.

6.    If step 4 created an AUD$ table in a different user, create a private synonym, AUD$, pointing to that user’s AUD$ table in the SYS user.

Viewing Audit Trail Information

On most operating systems, special views on SYS.AUD$ are created when the catalog.sql script (which calls several scripts, including cataudit.sql) is executed. For other operating systems, you can create these views by executing the script cataudit.sql. To remove the audit views, you can execute the script catnoaud.sql.

The following notations are used in the audit trail views:

<NULL>. Audit is not set.

S. Audit by SESSION.

A. Audit by ACCESS.

/.  Separates the two settings. The first setting is for WHENEVER SUCCESSFUL; the second setting is for WHENEVER NOT SUCCESSFUL.


<NULL>/<NULL>. No auditing.







Several views contain a column called ses_actions. The ses_actions column is a summary for the actions included in that entry. The ses_actions column is an 11-character code, where the letter S means success, F means failure, B means both, and none indicates not audited. The character codes appear in alphabetical order:

Alter, Audit, Comment, Delete, Grant, Index, Insert, Lock, Rename, Select, Update

The audit views fall into several categories: general information on auditing performed, statement audit information, privilege audit information, and object audit information.

There are four general auditing views:

AUDIT_ACTIONS. Maps audit trail action types to codes.

STMT_AUDIT_OPTION_MAP. Maps auditing option types to codes.

DBA_AUDIT_TRAIL. All audit records in the system.

USER_AUDIT_TRAIL. Audit trail entries relevant to the user.

For statement auditing, the following views are useful:

DBA_STMT_AUDIT_OPTS. Information on current system-auditing options across the system and by user.

DBA_AUDIT_STATEMENT. Audit entries for statements with the GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands.

USER_AUDIT_STATEMENT. Audit entries for statements issued by the user.

DBA_AUDIT_SESSION. Audit entries for connections and disconnections.

USER_AUDIT_SESSION. Audit entries for connections and disconnections    for that user.

DBA_AUDIT_EXISTS. Audit entries created by the AUDIT EXISTS command.

For privilege auditing, the DBA_PRIV_AUDIT_OPTS view shows the privilege option audit entries. There is one entry for each audited privilege. For object auditing, the relevant views are:

DBA_OBJ_AUDIT_OPTS. Auditing options set for all tables and views.

USER_OBJ_AUDIT_OPTS. Auditing options for the owner’s tables and views (corresponds to the DBA_OBJ_AUDIT_OPTS view).

ALL_DEF_AUDIT_OPTS. Default audit options for objects being created.

DBA_AUDIT_OBJECT. Audit records for all objects in the system.

USER_AUDIT_OBJECT. Audit trail records for statements concerning objects owned by that user (corresponds to the DBA_AUDIT_OBJECT view).

Auditing by Database Triggers

Enabling auditing options may not always be sufficient to evaluate suspicious activity within your database. When you enable auditing, Oracle places records in the SYS.AUD$ table in accordance with the auditing options that you have specified. One limitation to this type of auditing is that SYS.AUD$ does not provide you with value-based information. You need to write triggers to record the before and after values on a per-row basis.

Auditing with Oracle supports DML and DDL statements on objects and structures. Triggers support DML statements issued against objects, and can be used to record the actual values before and after the statement.

In some facilities, audit commands are considered security audit utilities, while triggers are referred to as financial auditing. This is because triggers can provide a method to track actual changes to values in a table. Although, similar to the AUDIT command, you can use triggers to record information,  you should customize your auditing by using triggers only when you need more detailed audit information.

AFTER triggers are normally used to avoid unnecessary statement generation for actions that fail due to integrity constraints. AFTER triggers are executed only after all integrity constraints have been checked. AFTER ROW triggers provide value-based auditing for each row of the tables and support the use of “reason codes.” A reason for the statement or transaction, along with the user, sysdate, and old and new values, can be inserted into another table for auditing purposes.

Oracle auditing can be used for successful and unsuccessful actions, as well as connections, disconnections, and session I/O activities. With auditing, you can decide if the actions should be BY ACCESS or BY SESSION. Triggers can only audit successful actions against the table on which they are created. If auditing is being performed using a trigger, any rollback or unsuccessful action will not be recorded.

Auditing provides an easy, error-free method to tracking, with all the audit records stored in one place. Triggers are more difficult to create and maintain.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter