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