Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Dec 9, 2004

DB2 Tracking Database Changes - Page 3

By Marin Komadina



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



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 



















Thanks for your registration, follow us on our social networks to keep up-to-date