DB2 UDB security, Part 5: Understand the DB2 audit facility

One of the lesser known but powerful components of IBM® DB2® Universal Database™ for Linux®, UNIX®, and Windows® (DB2 UDB) is the audit facility. The audit facility is an invaluable resource for security-conscious DBAs that need to monitor database events such as failed access attempts, database object modifications, and user validation with minimal effort. Learn about the DB2 audit facility, its purpose, how to use and configure it with the db2audit command, and get tips for using it effectively.

Ted J. Wasserman (tedwas.ibm@gmail.com), Database Consultant, IBM

Ted J. Wasserman's photoTed J. Wasserman is a database consultant at the IBM Silicon Valley Laboratory in San Jose, California. Ted works on the DB2 Business Partner Technical Enablement team, where he specializes in helping IBM Business Partners migrate their applications and databases to DB2. Ted has a master's degree in computer science, as well as a bachelor's degree in computer science from Queen's University in Kingston, Ontario, Canada.



09 March 2006

Also available in Russian

Introduction

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 db2stop command, 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. Refer to 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 in the 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 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 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 its status.

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
db2stop
db2start

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 db2audit command.

Table 1. Database event types that can be audited
Event typeDescription
Audit
(AUDIT)
  • Generates records when audit settings are changed or when the audit log is accessed
Authorization checking
(CHECKING)
  • Generates records during authorization checking of attempts to access or manipulate DB2 objects or functions
Object maintenance
(OBJMAINT)
  • Generates records when creating or dropping data objects
Security maintenance
(SECMAINT)
  • Generates records when granting or revoking object or database privileges or DBADM authority
  • Records are also generated when the database manager security configuration parameters SYSADM_GROUP, SYSCTRL_GROUP, or SYSMAINT_GROUP are modified
System administration
(SYSADMIN)
  • Generates records when operations requiring SYSADM, SYSMAINT, or SYSCTRL authority are performed
User validation
(VALIDATE)
  • Generates records when authenticating users or retrieving system security information
Operation context
(CONTEXT)
  • Generates records to show the operation context when a database operation is performed
  • This category allows for better interpretation of the audit records. When used with the log's event correlator field, a group of events can be traced back to a single database operation that can provide needed context when analyzing audit results.

For example, if you only needed to audit database connection and system administration event types, you would only specify the VALIDATE and 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.

Error handling

You can also configure the way you want the audit facility to handle errors using the ERRORTYPE clause in the db2audit command. 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 AUDIT and VALIDATE events and use the NORMAL error processing option. To accomplish this, issue the following db2audit command:

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 command:

db2audit configure scope all status both errortype audit

To view the current audit configuration settings and status, use the following db2audit command:

db2audit describe

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 configuration (SCOPE is all of the categories, except CONTEXT, STATUS is FAILURE, ERRORTYPE is NORMAL, and the audit facility is OFF), issue the following db2audit command:

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:

db2audit start

When you are finished auditing and want to stop the audit facility, issue the following command:

db2audit stop

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:

db2audit flush

Customizing the location of db2audit files

Tip: To find out how you can control where the audit files are stored, refer to the DB2 technote " How do I control where the db2audit files go?"

To extract records from the db2audit.log file into a text file, issue the following db2audit command:

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 command:

db2audit extract delasc delimiter !

The output is placed into separate files (one for each type of event) in the security subdirectory of sqllib. The filenames used are:

  • audit.del
  • checking.del
  • objmaint.del
  • secmaint.del
  • sysadmin.del
  • validate.del
  • context.de

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, DATABASE, and STATUS clauses, respectively. 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 extraction. The 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 extraction. The 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 extraction.

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 accomplish this:

db2audit extract delasc delimiter ! category audit database sample status failure

Important fix

Prior to DB2 UDB V8.1 FixPak 10, the extract phase could consume a lot of CPU, This has been fixed in FixPak 10. See the APAR information for more details.

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 command:

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 following db2audit command:

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 command:

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 FILE option.

Listing 3. Snippet of audit records extracted using the FILE option
timestamp=2006-02-06-11.54.52.443000;category=AUDIT;audit event=START;
  event correlator=0;event status=0;
  userid=tedwas;authid=TEDWAS;

timestamp=2006-02-06-11.55.14.664000;category=AUDIT;audit event=CONFIGURE;
  event correlator=0;event status=0;
  userid=tedwas;authid=TEDWAS;

timestamp=2006-02-06-11.55.19.371000;category=AUDIT;audit event=CONFIGURE;
  event correlator=0;event status=0;
  userid=tedwas;authid=TEDWAS;

timestamp=2006-02-06-11.55.30.718000;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-11.54.52.443000;,;AUDIT;,;START;,0,0,;tedwas;,;TEDWAS;
;2006-02-06-11.55.14.664000;,;AUDIT;,;CONFIGURE;,0,0,;tedwas;,;TEDWAS;
;2006-02-06-11.55.19.371000;,;AUDIT;,;CONFIGURE;,0,0,;tedwas;,;TEDWAS;
;2006-02-06-11.55.30.718000;,;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
NameFormatDescription
TimestampCHAR(26)Date and time of the audit event.
CategoryCHAR(8)Category of audit event. Possible values are: AUDIT.
Audit eventCHAR(32)A specific audit event. Possible values include: CONFIGURE, DB2AUD, EXTRACT, FLUSH, PRUNE, START, STOP, and UPDATE_ADMIN_CFG.
Event correlatorINTEGERCorrelation identifier for the operation being audited. Can be used to identify what audit records are associated with a single event.
Event statusINTEGERStatus of audit event, represented by an SQLCODE where:
  • Successful event >= 0
  • Failed event < 0
User IDVARCHAR(1024)User ID at time of audit event.
Authorization IDVARCHAR(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 should use.

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 the 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 following LOAD command:

LOAD FROM audit.del OF del MODIFIED BY CHARDEL! INSERT INTO MYSCHEMA.AUDIT

where 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 different delimiter.

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

where MYSCHEMA is the schema that your audit table resides in, AUDIT is the name of the table, and 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
db2stop
db2start

You wait until 12pm, and then start the audit facility:

db2audit start

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:

db2audit flush

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:

db2audit stop

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.


Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=105353
ArticleTitle=DB2 UDB security, Part 5: Understand the DB2 audit facility
publish-date=03092006