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.

Troubleshooting ADM13001E IBMOSauthclient and db2secGetDefaultLoginContext

2012-03-14-21.59.22.121301+000 E1040722932E430     LEVEL: Severe
PID     : 26754                TID  : 182956718112 PROC : db2bp
INSTANCE: db2my               NODE : 000
FUNCTION: DB2 UDB, bsu security, sqlexGetDefaultLoginContext, probe:150
MESSAGE : ADM13001E  Plug-in "IBMOSauthclient" received error code "-2" from 
          the DB2 security plug-in API "db2secGetDefaultLoginContext" with the 
          error message " ".

 

Determines the user associated with the default login context, in other words, determines the DB2 authid of the user invoking a DB2 command without explicitly specifying a user ID

The db2secGetDefaultLoginContext API returns the user of the default login context.  In the situation , where a DB2 command is executed , but a user ID is not stated.

 

Notes

 1) The error occurs because either an authid or user ID (or both) are not returned .

2) These errors occur sporadically – and are considered severe by DB2.

3) This is an OS authentication issue – where some third party  is attempting to logon.

4) Typically, the issue by  adding the user to /etc/passwd  or some other list .

5) Investigate all scans occurring on the server , and map occurrence of  errors with scan

6) If Quest is installed , check  logs  for critical errors

 

 

See Also

Quest 3.5.2 crashing DB2 9.5

DIAGLEVEL db2

 

DB2 SET CURRENT SCHEMA

Within a database multiple SCHEMAS can exist. If  a table or view exists in a schema different from the default SCHEMA of the logon and the reference to the object does not include a qualified name , then the object can’t be found.

For example , you may want to do a db2 list tables and view the list pf tables from another schema.

Use the SET CURRENT SCHEMA method.

For example , If the default SCHEMA is DB2TEST   , and you want to view the tables of the schema DB2OTHER do:

db2 set current schema = DB2OTHER

 

 

See Also

ADMIN_DROP_SCHEMA to drop a schema

DB2 SET CURRENT SCHEMA

Within a database multiple SCHEMAS can exist. If  a table or view exists in a schema different from the default SCHEMA of the logon and the reference to the object does not include a qualified name , then the object can’t be found.

For example , you may want to do a db2 list tables and view the list pf tables from another schema.

Use the SET CURRENT SCHEMA method.

For example , If the default SCHEMA is DB2TEST   , and you want to view the tables of the schema DB2OTHER do:

db2 set current schema = DB2OTHER

See Also

ADMIN_DROP_SCHEMA to drop a schema

DB2 security audit

The SYSIBM.SYSDBAUTH maintains user privileges on databases

The SYSIBM.SYSPLANAUTH  maintains user privileges on plans

The SYSIBM.SYSUSERAUTH   maintain  user privileges on the system.

This shell script collects and records these recordsets. Useful for regular DB2 security audits.

 

working_dir=$PWD
logfile="AUTH_`date +%d%m%y`.log"
#export working_dir
#export logfile
touch $working_dir/$logfile
>$working_dir/$logfile
echo "Current working directory $working_dir" >> $working_dir/$logfile
echo "operatation began `date`" >> $working_dir/$logfile
for i in `db2 list db directory | grep 'Database name' | awk '{print $4}'`;
do
echo "=============================="  >> $working_dir/$logfile ;
echo "getting :DB AUTH,USER AUTH,PLAN AUTH for: $i"  >> $working_dir/$logfile ;
echo "=============================="  >> $working_dir/$logfile ;
echo "checking database state"  >> $working_dir/$logfile ;
state=$(db2 get db cfg for $i | grep 'HADR database role' | awk '{print $5}');
echo "Current state is $state"  >> $working_dir/$logfile ;

if [ $state = "STANDBY"  ]
then
echo "THIS DATABASE IS THE STANDBY, THIS OPERATION SHOULD BE PERFORMED AT THE PRIMARY"  >> $working_dir/$logfile
else
echo "command : db2 connect to $i "  >> $working_dir/$logfile ;
db2  connect to $i  >> $working_dir/$logfile ;

echo "command : select * from SYSIBM.SYSDBAUTH"  >> $working_dir/$logfile ;
db2 "select * from SYSIBM.SYSDBAUTH"  >> $working_dir/$logfile;

echo "command : select * from SYSIBM.SYSPLANAUTH"  >> $working_dir/$logfile ;
db2 "select * from SYSIBM.SYSPLANAUTH"  >> $working_dir/$logfile;

echo "command : select * from SYSIBM.SYSUSERAUTH"  >> $working_dir/$logfile ;
db2 "select * from SYSIBM.SYSUSERAUTH"  >> $working_dir/$logfile;

db2 terminate 

fi

echo "=============================="  >> $working_dir/$logfile ;
echo "";
done
echo "operatation ended `date`" >> $working_dir/$logfile

 

See Also

DB2 - A Security Primer

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