The DB2 UDB auditing facility is an important security tool in the DBA's toolbox. It generates and allows a DBA to maintain an audit trail for a series of predefined database events. It is capable of logging database events such as authorization checking, database object maintenance, security maintenance, system administration, and user validation. This article shows you how to effectively use this facility to keep track of and investigate suspicious system activities.
Auditing takes place at the instance level, meaning that once it is
started, it audits the activity for all databases in that instance.
The audit facility must be started and stopped separately. For example,
if you stop an instance using the
the audit facility does not automatically stop; it must be stopped
separately using the
db2audit stop command.
Only users with SYSADM authority can configure and use the audit facility.
Part 4 of this series for a description of the SYSADM authority level
and how it can be granted to groups of users. This information is also available
DB2 UDB documentation.
Figure 1 shows a high-level diagram of the audit facility and how it fits into the overall DB2 UDB architecture.
Figure 1. The DB2 audit facility within the DB2 UDB architecture
The blue outer oval in the diagram represents a DB2 database server. The inner red rectangle represents a DB2 instance. As the figure shows, the audit facility operates at the instance level servicing all databases in the instance. A configuration file is used to set the auditing options.
When the facility is started, generated audit records are written into a buffer area and then flushed to disk into an audit file. Once the auditing period is over, the audit file can be converted from its native, raw format into a readable text file. Audit records can also optionally be loaded into DB2 tables, providing the opportunity to query the data using SQL and produce customized reports.
The audit facility files are stored and maintained in the instance's security folder. Figure 2 shows the sample contents of this folder on a Windows server for the instance called DB2.
Figure 2. The security folder on a Windows server
The db2audit.cfg file stores the facility's
configuration information. This is a binary file that should only
be modified using the
db2audit command line syntax. The
db2audit.log file stores the raw
audit records. Audit records can be extracted from this file into a
text file that can be analyzed further. The
db2audit.log file does not
exist by default and will not exist until audit records are generated
or the audit buffer is flushed. When you create a new instance,
the appropriate read/write permissions are set on these files, and it
is recommended that you do not change these permissions.
There are two main aspects of configuring the audit facility. The first involves setting the size of the buffer area, while the second involves configuring the type of events to audit. The next two sections cover these configurations in more detail.
Configuring the audit buffer
Audit records are typically written into a memory buffer before being flushed to disk. The size of the buffer is determined by the AUDIT_BUF_SZ database manager configuration parameter. This parameter specifies the number of 4K pages to allocate to audit buffering. The choice of buffer size can greatly affect performance when auditing is started since many records may be written to the audit log.
When the size of the buffer is set to 0, synchronous log writing
occurs, and the audit buffer is not used. In this configuration,
the event generating the audit record must wait until the record is
written to disk before returning its status. The wait
associated with each record causes the performance of DB2 to decrease.
When the size of the buffer is greater than 0, audit records are written
asynchronously. That is, they remain in the audit buffer before being
flushed to disk. To prevent an extended period of buffering, DB2 forces
the writing of the audit records regularly. The audit buffer may
also be flushed manually using the
db2audit flush command.
With asynchronous log writing, the event generating the audit record
does not have to wait until the record is written to disk before returning
Suppose that you wanted to increase the size of the audit buffer to 40KB in order to promote asynchronous log writing and improve performance. You could issue the following commands to accomplish this:
update dbm cfg using audit_buf_sz 10
Note that after this parameter is updated, you must restart the instance for the parameter change to take effect.
An important factor to take into account when selecting between synchronous or asynchronous log writing is what happens when a database manager error occurs. If an error occurs when using asynchronous log writing, multiple audit records may be lost because they are buffered before being written to disk. With synchronous log writing, one record may be lost because the error could only prevent, at most, one audit record from being written.
Configuring the audit scope and status
The audit facility can monitor different types of database events.
It is useful to review these event types before looking at the exact
syntax of the
db2audit command used to configure the facility.
Table 1 lists the different types of database events that can be audited
and describes when an audit record is generated. The uppercase word in
parentheses that follows each event type is the corresponding keyword
that is used in the configuration clause in the
Table 1. Database event types that can be audited
For example, if you only needed to audit database connection and system
administration event types, you would only specify the
SYSADMIN event types when configuring the facility.
Status of events
Once you decide which types of events you want to audit, you can also specify whether only successful or failed events, or both, should be logged. Logging successful events significantly increases the amount of records generated, so use this option sparingly.
You can also configure the way you want the audit facility to
handle errors using the
ERRORTYPE clause in the
You have the ability to specify whether audit facility errors are
returned to the user (
AUDIT) or ignored (
NORMAL). In the former case,
all errors, including errors occurring within the audit facility, are
managed by DB2 and all negative SQLCODEs are reported back to the
application. In the latter case, any errors generated by audit facility
are ignored and only the SQLCODEs for the errors associated with the
operation being performed are returned to the application.
db2audit command syntax
Now that you have reviewed the various types of events that can be
monitored as well as the status and error handling options, let's look
at the actual command syntax for configuring and using the audit facility.
Listing 1 shows the syntax of the
db2audit command, which is
used to configure and operate the audit facility.
Listing 1. Syntax of the db2audit utility
>>-db2audit--+-configure--+-reset-------------------+--------------------------+->< | '-| Audit Configuration |-' | +-describe--------------------------------------------------------+ +-extract--| Audit Extraction |-----------------------------------+ +-flush-----------------------------------------------------------+ +-prune--+-all--------------------------------------------------+-+ | '-date--YYYYMMDDHH--+--------------------------------+-' | | '-pathname--Path_with_temp_space-' | +-start-----------------------------------------------------------+ '-stop------------------------------------------------------------' Audit Configuration: |--+-----------------------------+--+---------------------+-----> '-scope--+-all--------------+-' '-status--+-both----+-' | .-,------------. | +-success-+ | V | | '-failure-' '---+-audit----+-+-' +-checking-+ +-objmaint-+ +-secmaint-+ +-sysadmin-+ +-validate-+ '-context--' >--+-----------------------+------------------------------------| '-errortype--+-audit--+-' '-normal-' Audit Extraction: |--+-file--output-file---------------------+--------------------> '-delasc--+---------------------------+-' '-delimiter--load-delimiter-' >--+----------------------------+-------------------------------> | .-,------------. | | V | | '-category----+-audit----+-+-' +-checking-+ +-objmaint-+ +-secmaint-+ +-sysadmin-+ +-validate-+ '-context--' >--+-------------------------+--+---------------------+---------| '-database--database-name-' '-status--+-success-+-' '-failure-'
In order to gain a better understanding of how to use the command
syntax, this article presents several examples. In the first example, the audit
facility needs to be configured to only log failed
and use the
NORMAL error processing option. To accomplish this, issue the
db2audit configure scope audit, validate status failure errortype normal
To configure the audit facility to monitor all event types,
logging both successful and failed attempts, as well as using the
AUDIT error processing option, issue the following
db2audit configure scope all status both errortype audit
To view the current audit configuration settings and status, use the
The output of this command is shown in Listing 2:
Listing 2. Output of the db2audit describe command on Windows
C:\Program Files\IBM\SQLLIB\DB2\security>db2audit describe DB2 AUDIT SETTINGS: Audit active: "TRUE " Log errors: "TRUE " Log success: "FALSE " Log audit events: "FALSE " Log checking events: "TRUE " Log object maintenance events: "FALSE " Log security maintenance events: "FALSE " Log system administrator events: "FALSE " Log validate events: "FALSE " Log context events: "FALSE " Return SQLCA on audit error: "FALSE " AUD0000I Operation succeeded.
By examining the output in Listing 2, you can see what events are being
logged and whether the audit facility is started or not. To reset the
current audit configuration settings and revert them back to their initial
SCOPE is all of the categories, except
NORMAL, and the audit facility is
OFF), issue the following
db2audit configure reset
This command also creates a new audit configuration file if the original one has been lost or damaged.
Once you have configured the types of events you want to monitor, as well as the status and error handling options, you can start the audit facility. Recall that the audit facility is started and stopped independently of an instance. To start the facility, issue the following command:
When you are finished auditing and want to stop the audit facility, issue the following command:
Exporting audit records
The audit records in the db2audit.log file are stored in a raw format. You must extract all the audit records from it into a text file, which can then be analyzed. You also have the option to extract audit records into delimited ASCII files, which can then be loaded into DB2 relational tables for analysis and querying.
Before extracting audit records from the db2audit.log file, flush any remaining audit records in the buffer out to disk by issuing the following command:
To extract records from the db2audit.log file
into a text file, issue the following
db2audit extract file c:\temp\audit_01_22_2006.aud
where "c:\temp\audit_01_22_2006.aud" is the path and file name of the desired output file. If you do not specify a file name, records are written to the db2audit.out file in the security subdirectory of $INSTHOME/sqllib, where INSTHOME is the instance home directory. If no directory is specified, the output file is written to the current working directory.
To extract records from the db2audit.log file
into delimited ASCII files that you can then load into DB2 relational tables,
issue the following
db2audit extract delasc delimiter !
The output is placed into separate files (one for each type of event) in
security subdirectory of sqllib. The
filenames used are:
In the previous command, by including the
DELIMITER clause followed by a
new record field delimiter (in this case, an
!), you have the ability to
override the default audit field delimiter (
0xff) when extracting
from the audit log.
You also have the ability to limit the number and type of audit records
extracted by specifying the
CATEGORY clause allows you to specify which categories
of audit events are to be extracted from the audit log. If you do not
specify anything for this clause, all categories are eligible for
DATABASE clause allows you to limit record extraction to
a particular database. If you do not specify anything for this clause,
audit records from all databases in the instance are eligible for
STATUS clause allows you to specify whether you want to
extract the events that succeeded, failed, or both succeeded and failed.
If you do not specify anything for this option, all records are eligible
For example, suppose you wanted to only extract the
AUDIT event type
records into a delimited ASCII format using a
! as the field delimiter
character. Further, suppose you only wanted the records for a database
called SAMPLE and were only interested in events that had a FAILURE
status. Issue the following
db2audit command to
db2audit extract delasc delimiter ! category audit database sample status failure
To extract only the SYSADMIN audit events into a standard
text file called audit.db2 for a database called
SAMPLE, including events that had both SUCCESS and FAILURE statuses,
issue the following
db2audit extract file audit.db2 category sysadmin database sample
Pruning the db2audit.log file
The audit log file (db2audit.log) can grow very
quickly. It is a good idea to prune it on a regular basis. Pruning records
from the audit log that you have already extracted
into a text file also prevents you from extracting the same records a second
time. To prune the db2audit.log file, issue the
db2audit prune date YYYYMMDDHH
where YYYYMMDDHH is the current year, month, day, and hour. All records up to this date and time are pruned. Keep a record of this date value in a handy place if you are keeping the audit records in DB2 tables.
To prune the entire log, issue the following
db2audit prune all
Understanding the format of an audit record
The text file produced by the extraction process is composed of several
audit records, each separated by a blank line. Listing 3 shows a snippet of
audit records that were extracted using the
Listing 3. Snippet of audit records extracted using the FILE option
timestamp=2006-02-06-18.104.22.1683000;category=AUDIT;audit event=START; event correlator=0;event status=0; userid=tedwas;authid=TEDWAS; timestamp=2006-02-06-22.214.171.1244000;category=AUDIT;audit event=CONFIGURE; event correlator=0;event status=0; userid=tedwas;authid=TEDWAS; timestamp=2006-02-06-126.96.36.1991000;category=AUDIT;audit event=CONFIGURE; event correlator=0;event status=0; userid=tedwas;authid=TEDWAS; timestamp=2006-02-06-188.8.131.528000;category=AUDIT;audit event=FLUSH; event correlator=0;event status=0; userid=tedwas;authid=TEDWAS;
Looking at these records, you can see that the user 'TEDWAS' first started
the audit utility, then changed the configuration twice, then flushed the
audit buffer. All of these events happened around the same timeframe, as is
demonstrated by the close proximity of values in the
TIMESTAMP field. You
can also see that these events represent
AUDIT event type because of the
AUDIT value in the category field.
Listing 4 shows a snippet of the same audit records that were extracted from the
audit log, this time using the
DELASC option with a
; field delimiter.
The records were taken from the audit.del file
that was generated in the extraction process.
Listing 4. Snippet of audit records extracted using the DELASC option
;2006-02-06-184.108.40.2063000;,;AUDIT;,;START;,0,0,;tedwas;,;TEDWAS; ;2006-02-06-220.127.116.114000;,;AUDIT;,;CONFIGURE;,0,0,;tedwas;,;TEDWAS; ;2006-02-06-18.104.22.1681000;,;AUDIT;,;CONFIGURE;,0,0,;tedwas;,;TEDWAS; ;2006-02-06-22.214.171.1248000;,;AUDIT;,;FLUSH;,0,0,;tedwas;,;TEDWAS; ;2006-02-06-11.56.04.346000;,;AUDIT;,;EXTRACT;,0,0,;tedwas;,;TEDWAS;
Notice that an additional record appears at the end of Listing 4. This record was generated when the text file produced for Listing 3 was generated. This additional record was generated because the audit facility was configured to log all audit events at the time the extraction took place.
Table 2 summarizes the meaning of each of the fields in the audit
record for the
AUDIT event type shown in Listing 3 and Listing 4.
Table 2. A description of the AUDIT event type audit record format
|Timestamp||CHAR(26)||Date and time of the audit event.|
|Category||CHAR(8)||Category of audit event. Possible values are: AUDIT.|
|Audit event||CHAR(32)||A specific audit event. Possible values include: CONFIGURE, DB2AUD, EXTRACT, FLUSH, PRUNE, START, STOP, and UPDATE_ADMIN_CFG.|
|Event correlator||INTEGER||Correlation identifier for the operation being audited. Can be used to identify what audit records are associated with a single event.|
|Event status||INTEGER||Status of audit event, represented by an SQLCODE where:
|User ID||VARCHAR(1024)||User ID at time of audit event.|
|Authorization ID||VARCHAR(128)||Authorization ID at time of audit event.|
Consult the DB2 UDB documentation for a description of the audit record layouts for the other event types.
Loading the extracted audit records into DB2 tables
Once you have extracted the audit records into delimited ASCII files, you
can load the contents of the ASCII files into DB2 tables. You can then
issue SQL statements against the
tables and perform advanced analysis of the data. You must first create
the tables that hold the audit data. It is a best practice to create these
tables in a separate schema in order to isolate the data from unauthorized
users and for organizational purposes. Consult the
DB2 UDB documentation for the actual
CREATE TABLE statements you
Once you have created all the necessary tables, you can load them from the
delimited ASCII files that were created during the extraction process. Use
LOAD utility to load the data into the tables. For example, to load
the AUDIT table using the corresponding audit.del
file that was created in the previous extraction process, issue the
LOAD FROM audit.del OF del MODIFIED BY CHARDEL! INSERT INTO MYSCHEMA.AUDIT
MYSCHEMA is the schema that your
audit table resides in and
AUDIT is the name of the table. Note the
use of the
MODIFIED BY CHARDEL! clause.
Since the extracted delimited ASCII file was created
using a field delimiter character other than the default expected by the
LOAD utility (a comma), the
LOAD utility was configured to accept a
It is a good practice to delete any pruned rows from the table that you loaded in order to ensure that you do not load duplicate rows into the table at a later time. The extracted audit files potentially contain records that were written after the hour that the audit log was pruned, so these records should be removed from the tables in order not to affect your analysis. The following command deletes rows from the AUDIT table that have a timestamp greater than a specified timestamp used to prune the audit log file:
DELETE FROM MYSCHEMA.audit WHERE TIMESTAMP > TIMESTAMP('YYYYMMDDHH0000')
MYSCHEMA is the schema that your
audit table resides in,
AUDIT is the name of the table,
YYYYMMDDHH0000 is the value you
specified when you pruned the audit log.
You can load the tables for the other event types in a similar way. For more information, consult the DB2 documentation for the LOAD commands you can use.
Working with audit data in DB2 tables
Loading the audit data into tables has many benefits. While you could write your own program to analyze the contents of the extracted text file containing the audit records, it is typically much easier and faster to query the audit data when it resides in relational tables.
Suppose you configured the audit facility to audit both failed and successful attempts of re-configuring the audit facility. After the monitoring period finished, you flushed the audit buffer, extracted the audit records into delimited ASCII files, then loaded the files into DB2 tables. If, for example, you wanted to query the data to see the top five user IDs who had the most unsuccessful attempts in changing the audit configuration, you could issue the following query:
SELECT userid, COUNT(*) AS count FROM MYSCHEMA.audit WHERE status < 0 AND event='CONFIGURE' GROUP BY userid ORDER BY count FETCH FIRST 5 ROWS ONLY
You might also want to consider using data mining products to help you discover patterns in the audit data. The DB2 Intelligent Miner family of products is highly recommended for this type of analysis.
Putting it all together
Now that you have seen how to configure and use the audit facility, let's walk through a complete scenario that shows the entire process from start to finish.
Suppose that you get an anonymous tip from one of your application users that a user called SAM is attempting to gain access to database objects and tables that he is not supposed to have access to during his lunch break. You therefore decide to monitor the DB2 instance during the lunch hour for failed authorization checking attempts.
You begin by configuring the audit facility to audit the CHECKING event type, recording only failed attempts and using NORMAL error processing:
db2audit configure scope checking status failure errortype normal
You ensure that the audit facility is using asynchronous logging by setting
the size of the
AUDIT_BUF_SZ database manager parameter to 40:
update dbm cfg using audit_buf_sz 40
You wait until 12pm, and then start the audit facility:
During the auditing period, the user SAM walks over to the database server and logs in. He opens a command line window, connects to the SAMPLE database and unsuccessfully tries to update the employee salaries in the EMPLOYEE table. He issues the following SQL statements:
connect to sample user sam using bad123boy
update tedwas.employee set salary = salary * 1.5
Upon receiving the error message:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0551N "SAM" does not have the privilege to perform operation "UPDATE" on object "TEDWAS.EMPLOYEE". SQLSTATE=42501
indicating that he does not have permission to update that table, he quickly logs off the server and leaves, thinking that nobody saw anything.
An hour passes by and you decide to check the contents of the audit log. You flush the audit buffer to ensure any audit records still buffered in memory are written to disk:
You extract the records from the db2audit.log file into ASCII delimited files using the semicolon as the field delimiter character, while only extracting CHECKING events with a FAILURE status:
db2audit extract delasc delimiter ; category checking database sample status failure
Since the audit records are now available in text format, you decide to prune the audit log to keep it manageable and to prevent duplicate records:
db2audit prune date 2006020613
Having previously created the DB2 tables to hold the audit data, you load the extracted data from the checking.del file into the CHECKING table, using the following command:
LOAD FROM checking.del OF del MODIFIED BY CHARDEL; INSERT INTO audit.checking
To avoid analyzing duplicate records, you delete rows from the CHECKING table with a timestamp greater than the one you pruned the pruned the audit log with:
DELETE FROM audit.checking WHERE TIMESTAMP > TIMESTAMP('20060206130000')
You attempt to find out more information about the failed authorization attempt by querying the AUDIT.CHECKING table:
SELECT category, event, appid, appname, userid, authid FROM audit.checking
From the query results, shown in Listing 5, you can see that one audit record was generated for the failed update statement.
Listing 5. Result of querying the CHECKING table
SELECT category, event, appid, appname, userid, authid FROM audit.checking CATEGORY EVENT APPID APPNAME AUTHID ------------------------ ----------------------- ---------------- ------------ CHECKING CHECKING_OBJECT *LOCAL.DB2.060206220334 db2bp.exe SAM 1 record(s) selected.
You decide to end your auditing analysis for the day and stop the audit facility, using the following command, so that no more audit records are generated:
Now that your suspicions have been validated, you will continue to gather additional evidence to present to your management, so they can take corrective action.
Although this scenario is very simple, it is designed to lead you through the entire process. The analysis is very simple in this case. The purpose is to confirm that the user SAM is attempting to update tables he should not be able to. The analysis portion in your own environment may be very different, depending on what you are trying to investigate.
You should now feel confident to explore the audit facility in greater depth and create new auditing processes and procedures for your environment. While reactive monitoring to unforeseen events is inevitable, proactive auditing should also be an important component of your security plan.
- "DB2 UDB security series" (developerWorks): This series of articles examines the security features available in DB2 UDB V8.2.
- Quick Beginnings for DB2 Servers Version 8.2: DB2 UDB Documentation describing DB2 UDB server installations.
- DB2 Online Information Center: The most recent DB2 online (and searchable) documentation.
- "Understand how user and group accounts interact with DB2 UDB" (developerWorks, August 2005): Learn about the different user and group accounts needed to install and work with DB2 UDB.
- "Understand the DB2 Universal Database security plug-ins" (developerWorks, December 2005): Learn about the IBM DB2® Universal Database (DB2 UDB) security plug-ins, a new feature introduced in version 8.2. This article explains what the security plug-ins accomplish and teaches you how to enable and write your own security plug-ins.
- developerWorks Information Management zone: Find more resources for DB2 UDB developers and administrators.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- IBM DB2 Universal Database Enterprise Server Edition V8.2 for Linux, UNIX, and Windows: Download a trial version from developerWorks.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- developerWorks blogs: Get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.