ss

Oracle Tips by Burleson

Auditing the Database

Auditing is a method of recording database activity as part of database security. It allows the DBA to track user activity within the database. The audit records will provide information on who performed what database operation and when it was performed. Records are written to a SYS-owned table named AUD$. The SYS.AUD$ table is commonly referred to as the audit trail.

It is advisable to copy the AUD$ table into a separate tablespace from other SYS-owned objects. In some cases, the AUD$ table should be owned by a user other than SYS. There are three reasons for these two statements:

The AUD$ table, if auditing is used, may grow to a very large size, depending on the audit options selected.
In some situations, you may want to add a trigger to the AUD$ table to count logins, monitor for specific actions (prior to 8i), or perform other security-related functions (such as implement password-checking functionality prior to version 8). Remember, you can’t add triggers to catalog objects owned by SYS.
Since the table will grow and shrink and be high activity, it is advisable to move it from the SYSTEM tablespace to avoid fragmentation.

Auditing information is not collected without some impact on performance and database resources. How much of an impact auditing will have on your system depends largely on the type of auditing you enable. For example, setting high-level auditing such as connection activity will not have as much of a performance impact as tracking all SQL statements issued by all users. It is best to start out with high-level auditing and then refine additional auditing as needed.

You can audit all users with the exception of SYS and CONNECT INTERNAL. Auditing can only be performed for users connected directly to the database, not for actions on a remote database.

On some versions of Oracle7 (7.3.2 to 7.3.3) operating system-level auditing of SYS and INTERNAL logins was initiated automatically on install. Auditing at operating system level (EPC_DISABLE=FALSE) results in a small audit file being generated in an operating system-specific directory location for each use of SYS or INTERNAL. This system-level auditing also may result in longer and longer login times for all users as the files process.dat, regid.dat, and so on, in the $ORACLE_

HOME/otrace/admin directory on UNIX, or orant\otrave73\admin on NT, expand in size. This is disabled by dropping the *.dat files in the previously mentioned locations, shutting down the instance, typing otrccref from the command line to reinitiate the files, and then setting the environmental variable EPC_DISABLED to TRUE (EPC_DISABLED=TRUE (Borne shell), setenv EPC_DISABLED TRUE (C shell), and restarting the instance(s).

Auditing should be enabled if the following types of questionable activities are noted:

Unexplained changes in passwords, tablespace settings, or quotas appear.
Excessive deadlocks are encountered.
Records are being read, deleted, or changed without authorization.

There are three types of auditing:

Statement auditing
Privilege auditing
Object auditing

See Code Depot