oracle – Logbook

http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60security-17606…

TECHNOLOGY: Security
As Published In

November/December 2010

Managing Audit Trails
By Arup Nanda

Relocate the audit trail to a different tablespace and set up an
automatic purge process to keep its size under control.

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:

1.Trails need to be kept to a manageable size (and old records purged)
if they are to be used effectively in forensic analysis.
2.Because database-resident trails are typically 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 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.

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).

In this article, you will learn how to use the new features in Oracle
Database 11g Release 2 to manage your audit trails.

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 11g
Release 2 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.

Code Listing 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_TRAIL_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_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 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.

Code Listing 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

Table 1: Types of audit trails for audit_trail_type

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. Listing 3 shows how
to set the cutoff time stamp to September 30, 2009 at 10 a.m. and
subsequently check its value from the view.

Code Listing 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(‘2009-09-30 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
30-SEP-09 10.00.00.000000 AM +00:00
Now you can execute the purge. To do so, run the code shown in
Listing 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 (September 30, 2009 at 10 a.m.
in this case). If it is set to FALSE, all audit trail records will be
deleted.

Code Listing 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.

Listing 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.

Code Listing 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
Next Steps

READ more about
the audit management package
database security

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.

Code Listing 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.

——————————————————————————–
Arup Nanda (arup@proligence.com) has been an Oracle DBA for more than
14 years, handling all aspects of database administration, from
performance tuning to security and disaster recovery. He was Oracle
Magazine’s DBA of the Year in 2003.

Logbook

Category: oracle

Protected: notes

2013 Juni Kurs Oracle Performance in München

Rman Oracle

What I learned about shared pool management

rman DUPLICATE DATABASE BACKUP LOCATION

Audit Trail Management