DB2 Tracking Database Changes

Thursday Dec 9th 2004 by Marin Komadina
Share:

Part one of this series explained the DB2 Audit utility, and how to use it for tracking and documenting database schema modifications. This second installment demonstrates a working example of creating a schema modification log.

Database security is no longer something that should be implemented, but is something that every serious company must have, to bring a certain level of reassurance to their clients. A DB2 DBA should know what is going on in his database. In addition, he should have a log of actions performed inside the DB2 database, for later tracking and problem solving. The DB2 audit trace record is a unique source of information, which can be retroactively queried if fraud is suspected or if individuals wish to know how their information was used. Another reason for beginning audit practices are common DB2 database security vulnerabilities that most attackers are familiar with, such as default DB2 usernames and passwords, default authentication settings or security holes inside the latest DB2 Fix Packs. In part one of this series, I explain the DB2 Audit utility, which I have been using for tracking and documenting database schema modifications. The second article will demonstrate a working example of creating a schema modification log.

This article covers:

  • Audit Theory
  • Audit Parameters
  • DB2 Audit Management Tool
  • Audit Configuration and Maintains
  • Conclusion

Audit Theory

The database audit facility is used for recording and tracking all of the regular database changes and usages or attempts of database misuse. The audit information is collected in the database audit trail (audit log), according to predefined database events. The audit facility acts on the instance level, collecting audit information about the instance and the database. In DB2 multipartition (UDB EEE) environments, all the local audit events are collected separately at the acting partition and afterwards merged in the global instance level audit log, "db2audit.log." The audit configuration parameters are saved in the db2audit.cfg file, located in the instance security directory, "security."

$ pwd
/home/db2inst1/sqllib/security

$ ls -lart
-r-x--s--x   1 db2inst1 db2admin   19388 Feb  6  2003 db2flacc
-r-s--x--x   1 root     db2admin   10732 Feb  6  2003 db2ckpw
lrwxrwxrwx   1 root     other         34 Feb  6  2003 db2chkau -> /opt/IBMdb2/V7.1/security/db2chkau
-rw-r--r--   1 db2inst1 db2admin    4096 Feb  6  2003 db2audit.cfg
-r-s--x--x   1 db2inst1 db2admin   15056 Feb  6  2003 db2aud
Listing 1:Audit files location

The instance security directory is created together with the DB2 instance and the instance owner has read/write permissions.


Picture 1:Audit system structure

The audit information is collected by the audit service in the audit buffer and then regularly flushed out to the disk. The audit buffering mechanism is, by default, disabled so that the audit records are written directly to the disk. The audit engine is implemented as a regular UNIX process and is independent from the database instance activity. The audit facility is fully independent of the DB2 instance status. Stopping or starting the DB2 instance does not change the audit status. The audit facility is running continually, collecting data in the audit log. The resulting log file, (db2audit.log), is managed by the db2audit administrative tool and a user with SYSADM authority/privileges.

Audit Parameters

AUDIT_BUF_SZ - audit buffer size

>> db2 get dbm cfg | grep AUDIT

Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
Listing 2: DBM Parameter -audit buffer size

Parameter

Configurable Online

Range 4K Pages

audit_buf_sz

No

0 - 65000

The AUDIT_BUF_SZ is the only audit parameter, and is used to audit the buffer size definition. The audit buffer is used for the accumulation of audit entries, from which the audit record is periodically flushed out to the disk. The default audit buffer size is 0KB, forcing the DB2 engine to write audit data directly to disk without buffering.



DB2 Audit Management Tool



The DB2 audit facility is completely managed and controlled by the DB2 system command, "db2audit." This command has several options and parameters. The four main groups are:



  • audit command
  • audit configuration
  • error handling
  • audit extraction


db2audit-> 
 -> "audit command" ->
 -> "audit configuration" (scope, status) ->
 -> "error handling"      (errortype)     ->
 -> "audit extraction"   (formatted data) 

Listing 3: db2audit parameter groups

Audit command


The audit command has the following options:



|>- db2audit  |- configure - audit configuration change 
              |- describe  - current audit settings 
              |- extract   - extracting audit data from audit file 
              |- flush     - flushing audit data from memory to the audit file 
              |- prune     - clean audit data from audit file 
              |- start     - start audit session
              |- stop      - stop audit session

Listing 4: db2audit - "audit command" option

IBM has provided a variety of options for controlling the instance audit facility, leaving no reason to manually hack the instance audit log.

Audit configuration (Scope)

A scope option is used to limit DB2 instance audit data collection. It is very important to plan the audit scope carefully, before implementation, to prevent overloading the system and database. IBM provides the following audit scopes (categories):

  |- scope --- all ---------|  |- status --- both ----|
            |  V- , ----|   |             |- success -|
            |--- audit -----|             |- failure -|
            |--- checking --|
            |--- objmaint --|
            |--- secmaint --|
            |--- sysadmin --|
            |--- validate --|
            |--- context   -|
Listing 5: db2audit - "audit configuration" option

a.) ALL scope - includes all scopes (AUDIT, CHECKING, OBJMAINT, SECMAINT, SYSADMIN, VALIDATE, CONTEX)

b.) AUDIT scope - includes changes in auditing configuration and audit log administration

c.) CHECKING scope - includes events corresponding to authority checking

d.) OBJMAINT (object maintenance) scope - includes the creation and deletion of DB2 objects and databases

e.) SECMAINT (security maintenance) scope - includes changes in database security (GRANT, REVOKE...)

f.) SYSADMIN (sysadmin events) scope - includes changes with SYSADM authority

g.) VALIDATE (validate events) scope - includes user validation processing

h.) CONTEXT (context events) scope - includes SQL statements and operational context

>> db2audit describe
DB2 AUDIT SETTINGS:
 
Audit active: "FALSE "
Log errors: "TRUE "
Log success: "FALSE "
Log audit events: "TRUE "
Log checking events: "TRUE "
Log object maintenance events: "TRUE "
Log security maintenance events: "TRUE "
Log system administrator events: "TRUE "
Log validate events: "TRUE "
Log context events: "FALSE "
Return SQLCA on audit error: "FALSE "
 
AUD0000I  Operation succeeded.
Listing 6: db2audit default audit configuration

The default scope includes all categories except the CONTEXT category. For high level auditing, CONTEXT would be used to get a complete trace of
 the executed command.

Error handling

>----------------------------|
  |- errortype --- audit --|
                |- normal -|
Listing 7: db2audit - "error handlig" option

The ERRORTYPE parameter defines errors that either are returned to the user or are
ignored. The following options are defined for the ERRORTYPE option:

  • AUDIT - the transaction will succeed only if the appropriate audit record is written to the audit log.

  • NORMAL - the transaction will succeed regardless of the audit status, e.g. the application will continue with normal processing and programmatically defined termination.

Audit Extraction

Audit Extraction:
|----------------------------------------------------------------------->
  |- file FILE NAME --------------------|  |            V- , ------|  |
  |- delasc ----------------------------|  |- category --- audit -----|
            |- delimiter LOAD DELIMITER-|               |- checking --|
                                                        |- objmaint --|
                                                        |- secmaint --|
                                                        |- sysadmin --|
                                                        |- validate --|
                                                        |- context   -|
Listing 8: db2audit - "audit extraction" option

The instance audit records can be extracted in different formats from the audit log. By default, extraction creates separate files, with predefined names, for each audit category, regardless of the actual audit settings.

Example of the generated files after extraction:

>> db2audit extract delasc 
 
AUD0000I  Operation succeeded.

$ ls -lrt | grep *.del
-rw-rw-rw-   1 db2inst1  db2admin     11403 Oct  7 18:50 validate.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 sysadmin.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 secmaint.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 objmaint.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 context.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 checking.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 audit.del
Listing 9: Audit data extracted in the ASCII format

In this example, the audit facility has been activated only for collecting "validate type" audit records.



Audit Configuration and Maintains



To exploit the db2audit command options fully, I created a test environment on SUN Solaris, DB2 UDB V8 Fix Pack 5 database software, DB2 instance ARTIST, with instance owner db2inst1 and DB2 database ARTIST. My audit configuration was stored in the audit configuration file, db2audit.cfg, located by default in the instance security subdirectory,
/home/db2inst1/sqllib/security.



Example of default audit files structure:

Click for full code

Listing 10: db2audit.cfg - audit configuration file structure

To explain and collect the necessary audit information for the "all database schema modifications" request, the following steps need to be taken:


a.) Start recording auditable events within the DB2 instance, example:

$ db2audit start
 
AUD0000I  Operation succeeded.

$ ls -alrt
-r-x--s--x   1 db2inst1  db2admin     25440 Jun 29 11:10 db2flacc
-r-s--x--x   1 root     db2admin     17916 Jun 29 11:10 db2ckpw
lrwxrwxrwx   1 root     other         35 Jun 29 11:10 db2chkau 
  -> /opt/IBM/db2/V8.1/security/db2chkau
-r-s--x--x   1 db2inst1  db2admin     21520 Jun 29 11:10 db2aud
-rw-r--r--   1 db2inst1  db2admin      4096 Oct  7 18:49 db2audit.cfg
-rw-------   1 db2inst1  db2admin      4251 Oct  7 18:49 db2audit.log
Listing 11: Starting DB2 instance auditing

Starting the audit facility generates the audit log file, db2audit.log, in the default audit directory.

b.) Configure the behavior of the audit facility

$ db2audit configure scope objmaint,secmaint status both errortype normal
 
AUD0000I  Operation succeeded.
Listing 12: DB2 audit configuration an example

The selected configuration must reflect the organization's needs and strategy already defined. In our case, it was defined with the "tracking and documenting all database schema modifications" initial request.

c.) Display a description of the current audit configuration:

$ db2audit describe
DB2 AUDIT SETTINGS:
 
Audit active: "TRUE "
Log errors: "TRUE "
Log success: "FALSE "
Log audit events: "TRUE "
Log checking events: "TRUE "
Log object maintenance events: "TRUE "
Log security maintenance events: "TRUE "
Log system administrator events: "TRUE "
Log validate events: "TRUE "
Log context events: "FALSE "
Return SQLCA on audit error: "FALSE "
 
AUD0000I  Operation succeeded.
Listing 13: Starting DB2 instance auditing

The default audit SCOPE, as shown, was activated for all categories except CONTEXT. This configuration produces a huge audit log, expending machine power and should be activated only when it is really needed. Nevertheless, to make additional loads to yours UNIX machine, you could try full auditing using the "scope all status both" option.

d.) Flush any Pending audit records from the instance and write them to the audit log.

$ db2audit flush 
 
AUD0000I  Operation succeeded.
Listing 14: Flushing DB2 audit buffer

This step is necessary to force writing of all records from the audit buffer, AUD_BUF_SZ, to the disk.

e.) Extract database audit records from the instance audit file into the ASCI delimited files:

$ db2audit extract delasc 

AUD0000I  Operation succeeded.
$ pwd 
/home/db2inst1/sqllib/security
>> ls -alrt
-r-x--s--x   1 db2inst1  db2admin     25440 Jun 29 11:10 db2flacc
-r-s--x--x   1 root     db2admin     17916 Jun 29 11:10 db2ckpw
lrwxrwxrwx   1 root     other         35 Jun 29 11:10 db2chkau 
  -> /opt/IBM/db2/V8.1/security/db2chkau
-r-s--x--x   1 db2inst1  db2admin     21520 Jun 29 11:10 db2aud
-rw-r--r--   1 db2inst1  db2admin      4096 Oct  7 18:49 db2audit.cfg
-rw-rw-rw-   1 db2inst1  db2admin     11403 Oct  7 18:50 validate.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 sysadmin.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 secmaint.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 objmaint.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 context.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 checking.del
-rw-rw-rw-   1 db2inst1  db2admin         0 Oct  7 18:50 audit.del
-rw-------   1 db2inst1  db2admin      7521 Oct  7 18:50 db2audit.log

Listing 15: Extracting DB2 instance audit data into ASCII format

f.) Prune (truncate) the current audit logs file:

$ ls -lrt
-rw-------   1 db2inst1  db2admin      1938 Oct 14 17:06 db2audit.log
>> db2audit prune all

AUD0000I  Operation succeeded.
$ ls -rlt
-rw-------   1 db2inst1  db2admin         0 Oct 28 14:00 db2audit.log
Listing 16: Pruning DB2 instance audit log

The prune command will permanently clear audit log content.

g.) Stop recording auditable events within the DB2 instance:

$ db2audit stop

AUD0000I  Operation succeeded.

$ db2audit stop
AUD0027I  A request to stop the DB2 audit facility has been processed. Note that audit may 
have already been stopped on the instance.
 
AUD0000I  Operation succeeded.
Listing 17: Stopping DB2 instance auditing

The db2audit system is a very finely developed system command with integrated software controls. One of them is displayed, where repeatedly executed stop commands generate status messages about the audit condition.

Conclusion

DB2 is a complex relational database system. An average DB2 DBA typically has to spend a fair amount of time reading and researching before configuring an effective company auditing policy. Until now, a comprehensive DB2 audit guideline was not available to the public. The default settings are too offensive for regular usage and most of the systems need only audit objectives that cover critical and important areas of DB2 security, connectivity, backup and recovery. In the future, I hope that IBM will produce at least one "DB2 audit guideline" to help companies with no high skilled technical staff to implement an appropriate level of security measures.

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved