In
this article, I will explain how to manage audit trails in Oracle Database 11gR2
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 11g 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 size (and old records purged) if they are to be
used effectively in forensic analysis.
- Because
database-resident trails are typically stored in the SYSTEM tablespace,
they can potentially fill it up—bringing the database to a halt.
New
auditing features in Oracle Database 11g 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 SYSTEM tablespace to one of your
choice.
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_ts datafile '+DATA' size 1000M segment space management auto;
For
moving an audit trail to the new tablespace, Oracle 11g R2 provides a
procedure in DBMS_AUDIT_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.
Point
1: Relocating a standard audit
trail
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDIT_TS');
end;
/
This
move operation can be performed even when the database is up and an audit trail
is being written. The target tablespace (AUDIT_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 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.
Point
2: Initializing cleanup of audit
entries
begin
dbms_audit_mgmt.init_cleanup(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_db_std,
default_cleanup_interval => 24 );
end;
The
INIT_CLEANUP procedure takes two parameters, neither of which takes a default
value:
- audit_trail_type—designates the
type of audit trail being initialized. For instance, audit_trail_aud_std
indicates the standard database audit trail (the AUD$ table). Table 1
lists the possible values for this parameter and the audit trail types
they represent.
- default_cleanup_interval—designates the
default interval in hours between executions of automatic purge jobs (to
be discussed later in this article).
Parameter
Description
audit_trail_aud_std The standard
AUD$ audit trail in the database
audit_trail_fga_std The
FGA_LOG$ table, for Fine Grained Auditing
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
In
addition to setting the default cleanup frequency, the INIT_CLEANUP procedure
moves the audit trail out of the SYSTEM tablespace. If the FGA_LOG$ and AUD$
tables are in the SYSTEM tablespace, the procedure will move them to the SYSAUX
tablespace. Needless to say, you should ensure that the SYSAUX tablespace has
sufficient space to hold both of these tables. The process of moving data from
one tablespace to the other can have an impact on performance, so you should
avoid calling the procedure during peak hours.
If
you have already relocated these two tables to another tablespace (as described
in the previous section), they will stay in the new location and the procedure
will execute much more quickly.
After
calling the initialization procedure, you can perform the actual audit trail
cleanup, but you likely wouldn’t just remove an audit trail blindly. In most
cases, you would archive the trail first before performing a permanent purge.
When doing so, you can call another procedure—SET_LAST_ARCHIVE_TIMESTAMP—to let
the purge process know the time stamp up to which an audit trail has been
archived. This procedure accepts three parameters:
- audit_trail_type—the type of
audit trail you are about to purge.
- last_archive_time—the last time
the audit trail was archived for this type.
- rac_instance_number—with an Oracle
Real Application Clusters (Oracle RAC) database, OS audit trail files
exist on more than one server. It’s possible to archive these files at
different times, so this parameter tells the purge process the archive
time of each node (or instance number) of the cluster. This parameter is
applicable to Oracle RAC databases only; it has no significance for
single-instance databases. Furthermore, this parameter is irrelevant for
database audit trails, because they are common to all Oracle RAC
instances.
After
you set the archive time stamp, you can check its value from a data dictionary
view, DBA_AUDIT_MGMT_LAST_ARCH_TS. Below code shows how to set the cutoff time
stamp to Jan 01, 2014 at 10 a.m. and subsequently check its value from the
view.
Point
3: Setting the last archived time
begin
dbms_audit_mgmt.set_last_archive_timestamp(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
last_archive_time =>
to_timestamp('2014-01-01
10:00:00','YYYY-MM-DD HH24:MI:SS'),
rac_instance_number => null
);
end;
/
SQL> select * from
DBA_AUDIT_MGMT_LAST_ARCH_TS;
AUDIT_TRAIL RAC_INSTANCE
——————————————————————————————————
LAST_ARCHIVE_TS
——————————————————————————————————
STANDARD AUDIT TRAIL 0
01-JAN-14 10.00.00.000000
AM +00:00
Now
you can execute the purge. To do so, run the code shown in Point 4. The
CLEAN_AUDIT_TRAIL procedure in the listing accepts two parameters. The first
one is audit_trail_type. The second parameter—use_last_arch_timestamp—specifies
whether the purge should be performed, depending on the last archive time
stamp. If the parameter is set to TRUE (the default), the purge will delete the
records generated before the time stamp (Jan 01, 2014 at 10 a.m. in this case).
If it is set to FALSE, all audit trail records will be deleted.
Point
4:
Purging a standard database audit trail
begin
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_aud_std,
use_last_arch_timestamp => TRUE
);
end;
/
This
same procedure is also used to purge file-based audit trails such as OS file
audit trails and XML trails. To purge those trails, just specify the
appropriate value for the audit_trail_type parameter (as shown in Table 1).
However, note that for file-based audit trails, only the files in the current
audit directory (as specified by the audit_file_dest initialization parameter)
will be deleted. If you have audit trail files in a different directory from
the one specified in audit_file_dest, those files will not be deleted.
Note
that in Microsoft Windows, audit trails are entries in Windows Event Viewer and
not actual OS files. So purging OS-based audit trails on that platform will not
delete the trails.
Setting Up Automatic Purge
The foregoing process
is good for a one-time purge of audit trails. To ensure that audit trails do
not overwhelm their tablespace, you may want to institute an automatic purge
mechanism. The DBMS_AUDIT_MGMT package has another procedure—CREATE_PURGE_JOB—to
do just that. This procedure takes four parameters:
- audit_trail_type—the type of the
audit trail
- audit_trail_purge_interval—the duration,
in hours, between executions of the purge process
- audit_trail_purge_name—the name you
assign to this job
- use_last_arch_timestamp—an indication
of whether the job should delete audit trail records marked as archived.
The default is TRUE. If the parameter is set to FALSE, the procedure will
delete the entire trail.
Point
5 shows how to create a purge job that deletes standard audit trail records
every 24 hours. As with one-time purges, you can create different jobs for each
type of trail—such as standard, Fine Grained Auditing, OS files, and XML—simply
by specifying different values for audit_trail_type when calling
CREATE_PURGE_JOB. You can even set different purge intervals for each audit
trail type to suit your archival needs. For instance, you can use a simple
database-link-based script to pull database audit trail records to a different
database while using a third-party tool to pull the OS audit trails. The
execution time of each approach may be different, causing the database records
to be pulled every day while the OS files are being pulled every hour. As a
result, you might schedule purge jobs with an interval of 24 hours for
database-based trails and with an interval of one hour for OS-file-based
trails.
Point
5:
Creating a purge job for a standard audit trail
begin
dbms_audit_mgmt.create_purge_job (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_purge_interval => 24,
audit_trail_purge_name => 'std_audit_trail_purge_job',
use_last_arch_timestamp => TRUE
);
end;
/
You
can view information about automatic purge jobs by accessing the DBA_AUDIT_MGMT_CLEANUP_JOBS
data dictionary view. It shows all the important attributes of the job, such as
the name, the type of audit trail being cleaned, and the frequency.
Setting Audit Trail Properties
When setting up a
purge job, you should always remember one very important fact. It performs a
DELETE operation—not TRUNCATE—on database-based trails, so the purge operation
generates redo and undo records, which may be quite significant, depending on
the number of trail records deleted. A large deletion can potentially fill up
the undo tablespace. To reduce the redo size of a transaction, the purge job
deletes in batches of 1,000 and performs commits between them. If the database
is very large, it may be able to handle much more redo easily. You can change
the delete batch size by using the SET_AUDIT_TRAIL_PROPERTY procedure. Listing
6 shows how to set the delete batch size to 100,000.
Point
6:
Setting the deletion batch size
begin
dbms_audit_mgmt.set_audit_trail_property(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_property =>
dbms_audit_mgmt.db_delete_batch_size,
audit_trail_property_value => 100000);
end;
/
In
addition to the db_delete_batch_size property referenced in Listing 6, you can
use SET_AUDIT_TRAIL_PROPERTY to set several other important properties. They
include the following:
- file_delete_batch_size specifies how
many OS audit trail files will be deleted by the purge job in one batch.
- cleanup_interval specifies the
default interval, in hours, between executions of a purge job.
- os_file_max_age specifies how
many days an OS file or an XML file can be left open before a new file is
created.
- os_file_max_size specifies the
maximum size of an audit trail file (in kilobytes).
To
find the current value of a property, you can check the data dictionary view
DBA_AUDIT_MGMT_CONFIG_PARAMS.
Conclusion
Audit
trails establish accountability. In Oracle Database 11g, there are
several types of audit trails—standard, fine-grained, OS-file-based, and XML.
In this article, you learned how to relocate a database-based audit trail from
its default tablespace—SYSTEM—to another one designated only for audit trails.
You also learned how to purge audit trails of various types to keep them within
a manageable limit, and you finished by establishing an automatic purge
process.
