How to use DB2 database level audi?

Body

How to enable DB2 database level audit feature to capture SQL30082 RC 24 (incorrect password) error?

You can follow the below demo  where I used database level auditing to demonstrate the SQL30082 Rc 24 using an incorrect password. Please check and share your feedback, if there are any questions.

Environment:

AIX

DB2 10.5 FP5

Instance level audting

$ db2audit describe
DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log audit events: "FAILURE"
Log checking events: "FAILURE"
Log object maintenance events: "FAILURE"
Log security maintenance events: "FAILURE"
Log system administrator events: "FAILURE"
Log validate events: "BOTH"
Log context events: "NONE"
Return SQLCA on audit error: "TRUE "
Audit Data Path: ""
Audit Archive Path: ""

AUD0000I  Operation succeeded.

$  ls -ltr /home/mkassey/sqllib/security/auditdata
total 0
$

Create an audit policy:

$ db2 "create audit policy validatepolicy categories validate status failure error type audit"
DB20000I  The SQL command completed successfully.

$ db2 "audit database using policy validatepolicy"
DB20000I  The SQL command completed successfully.

$ db2 connect to sample user mkassey using abcd123
SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID").  SQLSTATE=08001

$ db2audit configure scope validate status failure errortype audit

AUD0000I  Operation succeeded.
 

Configure db2audit.
$ db2audit start

AUD0000I  Operation succeeded.
$ 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: "FAILURE"
Log system administrator events: "FAILURE"
Log validate events: "FAILURE"
Log context events: "NONE"
Return SQLCA on audit error: "TRUE "
Audit Data Path: ""
Audit Archive Path: ""

AUD0000I  Operation succeeded.
$
$ ls -ltr /home/mkassey/sqllib/security/auditdata
total 0
 

Reproduce the error by entering SQL30082N RC 24 using an incorrect password:
$ db2 connect to sample user mkassey using abcd123
SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID").  SQLSTATE=08001
$
$ ls -ltr /home/mkassey/sqllib/security/auditdata
total 48
-rw-------    1 mkassey  build          8780 Mar 30 19:28 db2audit.instance.log.0
-rw-------    1 mkassey  build          8308 Mar 30 19:28 db2audit.db.SAMPLE.log.0
$
$ db2audit flush

AUD0000I  Operation succeeded.
$ db2audit stop

AUD0000I  Operation succeeded.
$
$ db2audit describe
DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log audit events: "FAILURE"
Log checking events: "FAILURE"
Log object maintenance events: "FAILURE"
Log security maintenance events: "FAILURE"
Log system administrator events: "FAILURE"
Log validate events: "FAILURE"
Log context events: "NONE"
Return SQLCA on audit error: "TRUE "
Audit Data Path: ""
Audit Archive Path: ""

AUD0000I  Operation succeeded.
$

$ ls -ltr /home/mkassey/sqllib/security/auditdata
total 48
-rw-------    1 mkassey  build          8780 Mar 30 19:28 db2audit.instance.log.0
-rw-------    1 mkassey  build          8308 Mar 30 19:28 db2audit.db.SAMPLE.log.0
$

$ db2audit archive database sample

Member   DB Partition   AUD      Archived or Interim Log File
Number   Number         Message
-------- -------------- -------- -------------------------------------------------
       0              0 AUD0000I db2audit.db.SAMPLE.log.0.20160330193053

AUD0000I  Operation succeeded.
$ ls -ltr /home/mkassey/sqllib/security/auditdata
total 48
-rw-------    1 mkassey  build          8780 Mar 30 19:28 db2audit.instance.log.0
-rw-------    1 mkassey  build          8308 Mar 30 19:30 db2audit.db.SAMPLE.log.0.20160330193053
$

AUD0000I  Operation succeeded.

$ db2audit extract file db.out from files db2audit.db.SAMPLE.log.0.20160330193053

AUD0000I  Operation succeeded.
$ ls -ltr /home/mkassey/sqllib/security/auditdata
total 56
-rw-------    1 mkassey  build          8780 Mar 30 19:28 db2audit.instance.log.0
-rw-------    1 mkassey  build          8308 Mar 30 19:30 db2audit.db.SAMPLE.log.0.20160330193053
-rw-rw-rw-    1 mkassey  build           381 Mar 30 19:32 db.out
$ cat db.out
timestamp=2016-03-30-19.28.38.225735;
  category=VALIDATE;
  audit event=AUTHENTICATION;
  event correlator=2;
  event status=-30082;
  database=SAMPLE;
  userid=mkassey;
  execution id=mkassey;
  application id=*LOCAL.mkassey.160330232838;
  application name=db2bp;
  auth type=SERVER;
  plugin name=IBMOSauthserver;
  instance name=mkassey;
  hostname=db2debug.torolab.ibm.com;

$$ db2audit archive

Member   DB Partition   AUD      Archived or Interim Log File
Number   Number         Message
-------- -------------- -------- -------------------------------------------------
       0              0 AUD0000I db2audit.instance.log.0.20160330193343

AUD0000I  Operation succeeded.
$ ls -ltr /home/mkassey/sqllib/security/auditdata
total 56
-rw-------    1 mkassey  build          8308 Mar 30 19:30 db2audit.db.SAMPLE.log.0.20160330193053
-rw-rw-rw-    1 mkassey  build           381 Mar 30 19:32 db.out
-rw-------    1 mkassey  build          8780 Mar 30 19:33 db2audit.instance.log.0.20160330193343
$ db2audit extract file instance.out from files db2audit.instance.log.0.20160330193343

AUD0000I  Operation succeeded.
$
$ ls -ltr /home/mkassey/sqllib/security/auditdata
total 64
-rw-------    1 mkassey  build          8308 Mar 30 19:30 db2audit.db.SAMPLE.log.0.20160330193053
-rw-rw-rw-    1 mkassey  build           381 Mar 30 19:32 db.out
-rw-------    1 mkassey  build          8780 Mar 30 19:33 db2audit.instance.log.0.20160330193343
-rw-rw-rw-    1 mkassey  build          2370 Mar 30 19:34 instance.out
$
$ cat instance.out
timestamp=2016-03-30-19.28.08.104014;
  category=CHECKING;
  audit event=CHECKING_FUNCTION;
  event correlator=0;
  event status=-1092;
  userid=mkassey;
  authid=MKASSEY;
  application id=*LOCAL_APPLICATION;
  application name=db2pd;
  object type=INSTANCE;
  access approval reason=DENIED;
  access attempted=MEMBER_OF_SYS_GROUP;
  instance name=mkassey;
  hostname=db2debug.torolab.ibm.com;

timestamp=2016-03-30-19.28.08.109928;
  category=CHECKING;
  audit event=CHECKING_FUNCTION;
  event correlator=0;
  event status=-1092;
  userid=mkassey;
  authid=MKASSEY;
  application id=*LOCAL_APPLICATION;
  application name=db2pd;
  object type=INSTANCE;
  access approval reason=DENIED;
  access attempted=MEMBER_OF_SYS_GROUP;
  instance name=mkassey;
  hostname=db2debug.torolab.ibm.com;

$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 10.5.5
 SQL authorization ID   = MKASSEY
 Local database alias   = SAMPLE

$
$ db2 "select substr(auditpolicyname,1,15)policyname, objecttype from syscat.audituse "

POLICYNAME      OBJECTTYPE
--------------- ----------
VALIDATEPOLICY

  1 record(s) selected.
 
Just as a test, I have removed database level auditing incase anyone is interested:

$ db2 "audit database remove policy"
DB20000I  The SQL command completed successfully.

$ db2 "select substr(auditpolicyname,1,15)policyname, objecttype from syscat.audituse "

POLICYNAME      OBJECTTYPE
--------------- ----------

  0 record(s) selected.

$ db2 "select * from SYSCAT.AUDITPOLICIES"

AUDITPOLICYNAME                                                                                                                  AUDITPOLICYID CREATE_TIME                ALTER_TIME                 AUDITSTATUS CONTEXTSTATUS VALIDATESTATUS CHECKINGSTATUS SECMAINTSTATUS OBJMAINTSTATUS SYSADMINSTATUS EXECUTESTATUS EXECUTEWITHDATA ERRORTYPE REMARKS                                                         
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------- -------------------------- ----------- ------------- -------------- -------------- -------------- -------------- -------------- ------------- --------------- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VALIDATEPOLICY                                                                                                                             100 2016-03-30-19.19.35.149493 2016-03-30-19.19.35.149493 N           N             F              N              N              N              N              N             N               A         -                                                               

  1 record(s) selected.

$
$ db2 drop audit policy VALIDATEPOLICY
DB20000I  The SQL command completed successfully.
$ db2 "select * from SYSCAT.AUDITPOLICIES"

AUDITPOLICYNAME                                                                                                                  AUDITPOLICYID CREATE_TIME                ALTER_TIME                 AUDITSTATUS CONTEXTSTATUS VALIDATESTATUS CHECKINGSTATUS SECMAINTSTATUS OBJMAINTSTATUS SYSADMINSTATUS EXECUTESTATUS EXECUTEWITHDATA ERRORTYPE REMARKS                                                         
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------- -------------------------- ----------- ------------- -------------- -------------- -------------- -------------- -------------- ------------- --------------- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  0 record(s) selected.

$