DB2 Version 10.1 for Linux, UNIX, and Windows

Storage and analysis of audit logs

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 environment, such as a DB2 pureScale environment or a partitioned database environment. In a multiple member 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 environment

In a multiple member 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 environment

In a multiple member 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 );