DB2 Tracking Database Changes - Page 3

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

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

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.

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.

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.

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

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

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.

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