A glance at DB2 Audit

The buzz word for IT security is now audit.  They want to audit activities in database for various compliance reason.  HIPPA, PCI, FDA, SOX , SAS70 and so on requires various levels of audit.  Most of the database software vendors provide certain level of audit facilities within their database engine.  The big three : IBM DB2, Oracle and SQL Server comes with audit features as part of database.  DB2 has DB2 Audit, Oracle has fine grain audit , SQL Server 2008 has SQL audit.  They all do one thing in common – generate i/o and lots of them !  So be careful on what all you audit and how you audit it.

In here, I am going to write about DB2 Audit.  IBM has been developing the audit service over the years and have made it pretty robust and detailed.  They have made the setup and process more easy and intrusive. Starting with DB2 V9.5, IBM has changed the security aspect around db2audit . They have the old db2audit process that runs at the instance level and now have one at the database level as well.

The db2audit at the instance level audits what is happening under the instance and db2audit at the database level is more focused on activities at the particular database. The security requirements are different for instance and database level audits.  Instance level audits can be run by a SYSADM and database level audit can be run by a SECADM.
To configure instance level audit use the db2audit command .  All the audit administration requests are done using the db2audit command.  If you want to audit database level activities you need to get SECADM authority and then create audit policy which defines what type of events you need to monitor. Then you associates what objects or users you want to audit.

Lets say for SOX or PCI, you been asked to monitor your credit card ,password table and monitor the activities of your DBAs. Here is what I you would need to do :

1. For best practices,  I suggest you create a separate mount or directory called  auditfiles, then create three subdirectories underneath it :  auditdata, auditarchives, auditextracts.  The reason why I suggest this is because by default the audit files go to the instance directory and if you are not careful then you will end up filling your instance’s home directory.

2. Now lets configure db2 audit to send the files to the directory we created. Using a sysadm user id issue :
db2audit configure datapath /auditfiles/auditdata
db2audit configure archivepath /auditfiles/auditarchives

Verify your configurations using  db2audit describe

3.  Connect to the database you want to audit and create the audit tables :
db2 -svtf /home/db2inst1/sqllib/misc/db2audit.ddl

4.  Connect to the database you want to audit using a SECADM userid  and create audit policies and then associate the policies to objects.
create audit policy sensitivedatapolicy
categories execute status both error type audit ;
audit table  creditcards using policy sensitivedatapolicy;
audit table  userid_password using policy sensitivedatapolicy;
create audit policy dbaspolicy
categories
audit status both,
sysadmin status both,
secmaint status failure,
objmaint status both,
checking status failure,
execute status both,
validate status failure
error type normal;
audit sysadm,dbadm,secadm,group dbadmins using policy dbaspolicy;

5. Its time to start the audit :
 db2audit start

Now the database is audited, you can verify by going to /auditfiles/auditdata/ . You should start seeing a file grow as database activities grow.

6. Schedule a process to periodically flush and archive the auditfiles :
db2audit flush
db2audit archive database rajudb

The db2audit archive database command moves the files to the /auditfiles/auditarchives directory you configured in step2.

7. According to your requirements schedule another process to extract the files and load the data into the audit tables:
db2audit extract delasc to /auditfiles/auditextracts from files /auditfiles/auditarchives/db2audit.db.rajudb.log.0.*

This will create all the  delimited files on the auditextracts directory that is need to load the data to the audit tables.

8. Load the delimited files to the audit tables :
load from /auditfiles/auditextracts/execute.del of del modified by delprioritychar lobsinfile insert into execute;
load from /auditfiles/auditextracts/audit.del of del modified by delprioritychar lobsinfile insert into audit;
load from /auditfiles/auditextracts/sysadmin.del of del modified by delprioritychar lobsinfile insert into sysadmin;
load from /auditfiles/auditextracts/secmaint.del of del modified by delprioritychar lobsinfile insert into secmaint;
load from /auditfiles/auditextracts/objmaint.del of del modified by delprioritychar lobsinfile insert into objmaint;
load from /auditfiles/auditextracts/checking.del of del modified by delprioritychar lobsinfile insert into checking;
load from /auditfiles/auditextracts/validate.del of del modified by delprioritychar lobsinfile insert into validate;

Once you can remove the files on the archive and extract directory.  You can change or drop audit policy by using the DROP POLICY or ALTER POLICY command.  Detailed information regarding db2audit can be found at IBM DB2 Information Center.  To stop the audit process use the command :  db2audit stop

Even though DB2 Audit can be setup relatively easily, be careful on what you monitor as it can generate lot of data. Be very selective on what you want to monitor.