DBA
SECURIT Y BY ARUP NANDA
Managing Audit Trails
Relocate the audit trail to a different tablespace
and set up an automatic purge process to keep
its size under control.
ORACLE DATABASE 11g RELEASE 2
One of the most significant aspects of database security involves setting up
auditing to record user activities. The very
knowledge that a user’s actions are being
recorded can act as a significant deterrent to
prevent wrongdoers from committing malicious acts.
When auditing is enabled, the audit output
is recorded in an audit trail, which is usually
stored in the database in a table under the SYS
schema called AUD$. It can also reside as files
in the file system, and the files can optionally
be stored in XML format. For more-precise
control, the Fine Grained Auditing feature of
Oracle Database 11 g provides granular control
of what to audit, based on a more detailed set
of policies. Fine Grained Auditing audits are
usually stored in another table, FGA_LOG$,
under the SYS schema.
These various audit trails can quickly
grow out of control when database activity
increases. As audit trails grow, two main
challenges must be addressed:
Trails need to be kept to a manageable
1.
size (and old records purged) if they are
to be used effectively in forensic analysis.
Because database-resident trails are typi-
2.
cally stored in the SYSTEM tablespace,
they can potentially fill it up—bringing
the database to a halt.
Fortunately, the new auditing features in
Oracle Database 11 g Release 2 can help
address these challenges. These capabilities,
implemented in a package called DBMS_
AUDIT_MGMT, enable you to move audit
trails from the S YSTEM tablespace to one of
your choice.
The new auditing features also let you set
up one-time and automated purge processes
for each of your audit trail types. Historically,
to purge an audit trail, you were generally
forced to stop auditing (which may have
required bouncing the database), truncate,
and then restart auditing (and bouncing the
database again).
RELOCATING THE AUDIT TRAIL TABLES
Let’s first examine how to relocate an audit
trail from the default SYSTEM tablespace to
a new one. In case you don’t already have a
suitable target tablespace, the code below
shows how to create one:
create tablespace audit_trail_ts
datafile '+DATA'
size 500M
segment space management auto
/
For moving an audit trail to the new
tablespace, Oracle Database 11 g Release
2 provides a procedure in DBMS_AUDI T_
MGMT called SET_AUDIT_TRAIL_LOCATION.
Listing 1 shows how to move a “standard”
audit trail, which is the Oracle Database
audit recorded in the AUD$ table.
This move operation can be performed
even when the database is up and an audit
trail is being written. The target tablespace
(AUDI T_ TRAIL_TS in this case) must be
available and online. If the tablespace is not
available, auditing will stop, also stopping
the database in the process. You should
therefore be very careful about where you
create the tablespace. The location should
be permanent (and not on a temporary file
system such as /tmp), and the underlying
hardware should be resilient against failures
(using RAID- 1, for example).
The procedure can also be used for Fine
Grained Auditing audit trails. To move a Fine
Grained Auditing audit trail, simply replace
the value of the audit_trail_type parameter
Table 1: Types of audit trails for audit_trail_type
Description
The standard AUD$ audit trail in the
database
audit_trail_fga_std The FGA_LOG$ table, for Fine Grained
audit_trail_db_std Both standard and FGA audit trails
audit_trail_os The OS audit trail
audit_trail_xml The XML audit trail
audit_trail_files Both OS and XML audit trails
audit_trail_all All of the above
Parameter
audit_trail_aud_std
in Listing 1 with dbms_audit_mgmt
.audit_trail_fga_std. If you want to move
both the standard and Fine Grained Auditing
audit trails to the new tablespace, use the
dbms_audit.audit_trail_db_std value as the
audit_trail_type parameter.
PURGING OLD DATA
Next, let’s examine how to purge audit trails.
The audit management package includes a
procedure that automatically performs the
purge for you. But before you can actually
use it, you must call a one-time initialization
procedure—INIT_CLEANUP—to set up the
audit management infrastructure. Listing 2
shows how to perform the initialization.
The INIT_CLEANUP procedure takes
two parameters, neither of which takes a
default value:
In addition to setting the default cleanup
frequency, the INIT_CLEANUP procedure
