DB2 – Audit Failed logons

Troubleshooting  DB2 failed logon attempts is an important part of  DB2 databases security management.  Beyond establishing security threats , such as hacking or viruses ,   the purpose of troubleshooting can extend to discovering application issues.

For example , there may be an application in the Development environment , that has JDBC connection pointing to a Production environment. This can have a disastrous impact on Production data. Ideally, an environment should have Development, Test,QA, Production servers split out into separate OUs , and there should be necessary blocks in place to  exclude connection attempts across the different domains. Nevertheless, it does happen and it is the DBAs responsibility to ensure steps are in place to discover this possibility.

Failed logon attempts are recorded in the db2diag.log file. This offers clues about the logon attempt , database attempted and referral source. This should be enough information to establish which DB2 instance requires auditing .

Setting up a DB2 audit is straightforward. The db2audit facility is specifically designed to help trace access to data, based on creating an audit trail of defined database events. Below is a sample of setting up a db2audit and recording some failed logon attempts.

Steps to audit Failed Logins

Note: For database-level authorization SECADM authority level is required . Read DB2 - A Security Primer  for more DB2 security details

#start the db2audit
db2audit start 

#define an auditing policy on the current server 
db2 "create audit policy fl categories validate status both error type normal"
db2 "audit database using policy fl"

#create some event 
db2 “connect to mydb user jackvamvas using evilpw".


Next was to flush the audit logs, archive and extract them:
#flush the audit logs
db2audit flush
#archive the audit logs
db2audit archive database mydb to /tmp
#extract the audit logs
db2audit extract delasc to /tmp from files /tmp/db2audit.db.mydb.log

The db2audit process created the file “ /tmp/validate.del”. 

Open it , and you should see entries with error -30082, related to failed logons.