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

Author: Jack Vamvas