DB2 Tracking Database Changes

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

This article covers:

  • Audit Theory

  • Audit

  • 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



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.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Latest Articles