Technical Blog Post
Abstract
DB2 Audit files for database are not getting generated or archived
Body
User wants to set up Db2 audit to monitor their database. Unfortunately, Db2 audit does not work as expected and does generate the expected output files.
Debugging the issue :
1> User has set up Db2 audit on system as follows:
$ db2audit describe
DB2 AUDIT SETTINGS:
Audit active: "TRUE "
Log audit events: "FAILURE"
Log checking events: "FAILURE"
Log object maintenance events: "FAILURE"
Log security maintenance events: "BOTH"
Log system administrator events: "BOTH"
Log validate events: "BOTH"
Log context events: "NONE"
Return SQLCA on audit error: "FALSE "
Audit Data Path: ""
Audit Archive Path: “"
The problem here is, when an audit event happens, like a log on with a wrong user or password, no audit file is written.
For Example,
1. Log on with wrong user and password:
$ db2 connect to SAMPLE user test using password
SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001
2. db2audit flush:
$ db2audit flush
AUD0000I Operation succeeded.
3. db2audit archive:
$ db2audit archive database SAMPLE to /db2/instance_path/sqllib/security/auditdata
Member DB Partition AUD Archived or Interim Log File Number Number Message
-------- -------------- -------- -------------------------------------------------
0 0 AUD0000I
AUD0000I Operation succeeded.
4. Check the contents of Audit archive path :
/db2/instance_path/sqllib/security/auditdata:
total 453024
-rwxrwxrwx 1 SAMPLE adm 229583646 Mar 16 09:28 db2audit.instance.log.0
-rw-rw-rw- 1 SAMPLE adm 186303 Feb 17 19:02 validate.del
drwxr-xr-x 2 SAMPLE adm 8192 Feb 17 19:00 .
-rw------- 1 SAMPLE adm 9329 Feb 17 19:00 db2audit.db.SAMPLE.log.0.20180217190049
-rw------- 1 SAMPLE adm 28709 Jan 29 13:57 db2audit.db.SAMPLE.log.0.20180129135753
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 checking.del
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 auditlobs
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 execute.del
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 context.del
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 sysadmin.del
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 audit.del
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 secmaint.del
-rw-rw-rw- 1 SAMPLE adm 0 Jan 15 10:40 objmaint.del
-rw-r--r-- 1 SAMPLE adm 0 Jan 14 19:58 db2audit
drwxr-xr-x 4 SAMPLE adm 8192 Jan 14 19:27 ..
RESULT : No new log file db2audit.db.SAMPLE.log.0 is created. Only the db2audit.instance.log.0 file is updated.
No errors are returned anywhere. If it was a permission issue on the archive path it would have been logged in db2diag.log and/or the prompt.
The normal behaviour would be:
1. Enable db2audit
2. The issue you want to monitor with db2audit happens.
3. A file called db2audit.db.<dbname>.log.0 is generated under sqllib/security/auditdata
4. The command "db2audit archive" generates the following files:
db2audit.db.<dbname>.log.0.<timestamp>
db2audit.instance.log.0
Cause :
In most cases, we found that the audit buffer was not set (0 is default).
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Solution :
Update AUDIT_BUF_SZ to a non-zero value. Recycle the instance for the value to take effect.
db2stop
db2start
UID
ibm11140124