DB2 security audit

10 January,2012 by Jack Vamvas

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)
Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on DB2 security audit

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