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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 9, 2004

DB2 Tracking Database Changes

By Marin Komadina

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

$ 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


Configurable Online

Range 4K Pages



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 Archives