 |
|
Oracle Tips by Burleson |
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:
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 Remote 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.
2. CONNECT INTERNAL.
3. Truncate the SYS.AUD$ table.
4. Insert records from the
temporary table back into SYS.AUD$.
See Code Depot
www.oracle-script.com |
