How to collect DB2 security audit information

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