Friday, August 22, 2008

DB2 Audit Facility for Dummy

IT security auditors came and approached me, asking about DB2 user query activities, security events and blah blah. Let me see, o yeah, we got application specific audit trails. HR system got their own, ERP too and not forgetting that small little ETL program that IS people wrote. Of course, having domain specific audit facility is not a sin and they are one of the standard practice. However, DB2 provides an audit facility which is generic enough to capture almost any events happened in your databases.

Check out this DB2 program, located at <DB2_INSTALL_PATH>\bin

db2audit


Before you start to use db2audit, you should configure the AUDIT_BUF_SZ parameter in DBM configuration file. By the way, db2audit is controlled at Instance level. Setting AUDIT_BUF_SZ to non-Zero value indicates a multiple of 4KB. This is the buffer size before the audit records written to disk.

Then, you can check the current settings of db2audit, by using this command

db2audit describe


You will see something like below:



DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
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: "TRUE "
Return SQLCA on audit error: "TRUE "

AUD0000I Operation succeeded.




For example, if I just want to log audit events for authentication and authorization, both when success and failed, and rollback application if the system unable to generate the audit logs, perhaps due to system failure. Then I use this command:



db2audit configure scope checking,validate status both errortype audit


I will get the results below, when I submit "db2audit describe" again


DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
Log audit events: "FALSE "
Log checking events: "TRUE "
Log object maintenance events: "FALSE "
Log security maintenance events: "FALSE "
Log system administrator events: "FALSE "
Log validate events: "TRUE "
Log context events: "FALSE "
Return SQLCA on audit error: "TRUE "

AUD0000I Operation succeeded.


You can enumerate the list of possible values for SCOPE, STATUS and ERRORTYPE by just typing "db2audit" and submit.

After configuring the audit facility, to start the facility


db2audit start


To stop the facility


db2audit stop



And, whenever you set AUDIT_BUF_SZ to non-ZERO, you should also use


db2audit flush


for writing the buffer to disk.

2 output file formats are supported by the native extraction: Flat and Delimited.

Use "db2audit extract" command to extract the logs. For example, if I need the audit records for authentication and authorization events, in comma delimiter format for database PROD, then I use the command below:


db2audit extract delasc DELIMITER , category checking, validation database


This will generates a list of files with .DEL extention in <INSTANCE_DIRECTORY>\security folder. You can load the files into database by using LOAD or IMPORT utility.

Audit file will grows over time and you need to perform house keeping on it occasionally. To remove all audit records, use


db2audit prune all


Or more likely you will want to remove records prior to certain date.


db2audit prune date YYYYMMDDHH


E.g. db2audit prune date 2007050100 will delete all records where date prior to 1-May-2007.


Lastly, additional information you might need to aware of


  • Only SYSADM group members can perform auditing actions

  • Audit Configuration File located at <INSTANCE_DIR>\security\db2audit.cfg, and it is in binary format

  • Audit Log File located at <INSTANCE_DIR>\security\db2audit.log, and it is in binary format

  • By setting DB2INSTANCE environment variable, you can configure audit for different DB2 instances

  • 3 comments:

    Carlito said...

    Hi,
    I have db2 v9.7 on linux RH5.

    The command :
    db2audit extract delasc ';' , category checking, validation database

    return an error :

    AUD0002N Syntax error. Usage: ...
    AUD0001N Operation failed.

    miao said...

    The extract command now works only with archived log files. It is no longer possible to extract from the active log.

    CoolKiran said...

    Hi,
    I have archived the audit logs and imported to the audit tables created. But I am not able to get the output what I am requiring. I mean I am expecting the which user has logged in,from which ip, what query he/she has fired, is that insert,updated,delete etc.

    Can you please guide me.

    Regards,
    Kiran.M.K.