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
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
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:
See Code Depot