Archiving the audit log moves the active
audit log to an archive directory while the server begins writing
to a new, active audit log. Later, you can extract data from the archived
log into delimited files and then load data from these files into DB2® database tables for analysis.
Configuring
the location of the audit logs allows you to place the audit logs
on a large, high-speed disk, with the option of having separate disks
for each member in a multiple member database environment, such as
a DB2 pureScale environment or a partitioned database environment.
In a multiple member database environment, the path for the active
audit log can be a directory that is unique to each member. Having
a unique directory for each member helps to avoid file contention,
because each member is writing to a different disk.
The default path for the audit logs on Windows operating systems is
instance\security\auditdata and
on Linux and UNIX operating systems is
instance/security/auditdata.
If you do not want to use the default location, you can choose different
directories (you can create new directories on your system to use
as alternative locations, if they do not already exist). To set the
path for the active audit log location and the archived audit log
location, use the
db2audit configure command with
the
datapath and
archivepath parameters,
as shown in this example:
db2audit configure datapath /auditlog archivepath /auditarchive
The
audit log storage locations you set using
db2audit apply
to all databases in the instance.
Note: If there are multiple instances on the server, then each instance
should each have separate data and archive paths.
The path for active audit logs (datapath)
in a multiple member database environment
In
a multiple member database environment, the same active audit log
location (set by the
datapath parameter) must
be used on each member. There are two ways to accomplish this:
- Use database member expressions when you specify the datapath parameter.
Using database member expressions allows the member number to be included
in the path of the audit log files and results in a different path
on each database member.
- Use a shared drive that is the same
on all members.
You can use database
member expressions anywhere within the value you specify for the
datapath parameter.
For example, on a three member system, where the database member number
is 10, the following command:
db2audit configure datapath '/pathForNode $N'
uses
the following paths:- /pathForMember10
- /pathForMember20
- /pathForMember30
Note: You cannot use database member expressions
to specify the archive log file path (archivepath parameter).
Archiving active audit logs
The
system administrator can use the db2audit tool
to archive both instance and database audit logs as well as to extract
audit data from archived logs of either type.
The security
administrator, or a user to whom the security administrator has granted
EXECUTE privilege on the audit routines, can archive the active audit
log by running the SYSPROC.AUDIT_ARCHIVE stored procedure. To extract
data from the log and load it into delimited files, they can use the
SYSPROC.AUDIT_DELIM_EXTRACT stored procedure.
These are the
steps to archive and extract the audit logs using the audit routines:
- Schedule an application to perform regular archives of the active
audit log using the stored procedure SYSPROC.AUDIT_ARCHIVE.
- Determine which archived log files are of interest. Use the SYSPROC.AUDIT_LIST_LOGS
table function to list all of the archived audit logs.
- Pass the file name as a parameter to the SYSPROC.AUDIT_DELIM_EXTRACT
stored procedure to extract data from the log and load it into delimited
files.
- Load the audit data into DB2 database
tables for analysis.
The archived log files do not need to be immediately loaded into
tables for analysis; they can be saved for future analysis. For example,
they may only need to be looked at when a corporate audit is taking
place.
If a problem occurs during archive, such as running out
of disk space in the archive path, or the archive path does not exist,
the archive process fails and an interim log file with the file extension .bk is
generated in the audit log data path, for example, db2audit.instance.log.0.20070508172043640941.bk.
After the problem is resolved (by allocating sufficient disk space
in the archive path, or by creating the archive path) you must move
this interim log to the archive path. Then, you can treat it in the
same way as a successfully archived log.
Archiving active audit logs in a multiple member database
environment
In a multiple
member database environment, if the archive command is issued while
the instance is running, the archive process automatically runs on
every member. The same timestamp is used in the archived log file
name on all members. For example, on a three member system, where
the database member number is 10, the following command:
db2audit archive to /auditarchive
creates
the following files:
- /auditarchive/db2audit.log.10.timestamp
- /auditarchive/db2audit.log.20.timestamp
- /auditarchive/db2audit.log.30.timestamp
If the archive
command is issued while the instance is not running, you can control
on which member the archive is run by one of the following methods:
- Use the node option
with the db2audit command to perform the archive
for the current member only.
- Use the db2_all command
to run the archive on all members.
For example:
db2_all db2audit archive node to /auditarchive
This
sets the
DB2NODE environment variable to indicate
on which members the command is invoked.
Alternatively, you can issue
an individual archive command on each member separately. For example:
- On member 10:
db2audit archive node 10 to /auditarchive
- On member 20:
db2audit archive node 20 to /auditarchive
- On member 30:
db2audit archive node 30 to /auditarchive
Note: When the instance is not
running, the timestamps in the archived audit log file names are not
the same on each member.
Note: It
is recommended that the archive path is shared across all members,
but it is not required.
Note: The
AUDIT_DELIM_EXTRACT stored procedure and AUDIT_LIST_LOGS table function
can only access the archived log files that are visible from the current
(coordinator) member.
Example of archiving a log and extracting data to
a table
To ensure their audit
data is captured and stored for future use, a company needs to create
a new audit log every six hours and archive the current audit log
to a WORM drive. The company schedules the following call to the SYSPROC.AUDIT_ARCHIVE
stored procedure to be issued every six hours by the security administrator,
or by a user to whom the security administrator has granted EXECUTE
privilege on the AUDIT_ARCHIVE stored procedure. The path to the archived
log is the default archive path,
/auditarchive,
and the archive runs on all members:
CALL SYSPROC.AUDIT_ARCHIVE( '/auditarchive', -2 )
As part of their security procedures, the company
has identified and defined a number of suspicious behaviors or disallowed
activities that it needs to watch for in the audit data. They want
to extract all the data from the one or more audit logs, place it
in a relational table, and then use SQL queries to look for these
activities. The company has decided on appropriate categories to audit
and has associated the necessary audit policies with the database
or other database objects.
For example,
they can call the SYSPROC.AUDIT_DELIM_EXTRACT stored procedure to
extract the archived audit logs for all categories from all members
that were created with a timestamp in April 2006, using the default
delimiter:
CALL SYSPROC.AUDIT_DELIM_EXTRACT(
'', '', '/auditarchive', 'db2audit.%.200604%', '' )
In
another example, they can call the SYSPROC.AUDIT_DELIM_EXTRACT stored
procedure to extract the archived audit records with success events
from the EXECUTE category and failure events from the CHECKING category,
from a file with the timestamp they are interested in:
CALL SYSPROC.AUDIT_DELIM_EXTRACT( '', '', '/auditarchive',
'db2audit.%.20060419034937', 'category
execute status success, checking status failure );