DB2 – Behavior when users are dropped from OS level

This is to discuss the behavior of db2 when users (Who are granted database authorities) are dropped from OS level.

Check the current users and their authorities granted.

 

Add a new user and grant some authorities then drop the user

Now recheck the authorities still the user is defined in database and the authorities are there. In most cases this will not be an issue practically as default authentication can not happen since the user is dropped from OS level.

Anyways you will have to revoke the authorities manually if you want to remove them.

And if you ever needed to remove this type of entries from database (Happened to me.. Didn’t work saying “it will not affect anythingL”) below script will find those users and any objects that they have the ownership.

On UNIX, DB2 => 9

 . ~/.profile  
 cat /etc/passwd | awk 'BEGIN { FS = ":" } ; { print $1 }' | sort > os_user.out  
 ##function_1  
 db2 "list db directory"|grep -ip Indirect| grep -ivp toolsdb|awk '/Database alias/{print $NF}' > list  
 a=`cat list`  
 for i in $a  
 do  
 db2 "connect to $i"  
 db2 "export to auths_$i.tmp of del modified by nochardel select lcase(grantee) from syscat.dbauth"  
 cat auths_$i.tmp | awk '{print $1}' | sort > sort_auths_$i.csv  
 comm -2 -3 sort_auths_$i.csv os_user.out | awk '{print $1}' | sort > diff_$i.txt  
 b=`cat diff_$i.txt`  
 for j in $b  
 do  
 na=`db2 -x "SELECT rtrim(substr(current server,1,8)) FROM SYSIBM.SYSDUMMY1 WITH UR"`  
 db2 "SELECT SUBSTR(OWNER,1,10) AS OWNER, OWNERTYPE,SUBSTR(OBJECTNAME,1,30) AS OBJECTNAME,SUBSTR (OBJECTSCHEMA,1,10) AS OBJECTSCHEMA, OBJECTTYPE FROM SYSIBMADM.OBJECTOWNERS WHERE LCASE(OWNER)='$j'" > objects_$na$j.usr  
 done  
 done  
 rm -rf *.out *.tmp list