DB2 – Audit Failed logons

13 July,2012 by Jack Vamvas

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.

Author: Jack Vamvas(http://www.dba-db2.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on DB2 – Audit Failed logons

Comments are moderated, and will not appear until the author has approved them.

dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright