Duplicate Permission/Privileges in DB2 LUW 9

Accidentally I and my Co-DBA discovered this. In DB2 LUW V9, when 2 different id's grant certain privileges  to a user/id the privilege entry in *AUTH tables are multiplied for each GRANTOR. If you run db2look and script out all the privileges you see multiple grants statement for a the same object with same privileges.

How to reproduce this, login as user X and grant SELECT on some table named TEST for some user named  "sam" and then log-out and log back in as user Y and do the same. Now run db2look as follows.

"db2look -d SAMPLE -x -o db2look_grant.out"

examine the "db2look_grant.out" and you will see 2 lines with "grant select on table TEST to user SAM"

This is not a big issue but this will simply increase the size of your syscat.*auth tables. And in worst cases DB2 might be spending significant time in reading these tables. So, to keep this problem in check I came-up with following script. I have tested this in my environment - it worked and it did not cause any issues, but you use your own due diligence.

#!/bin/bash # # read from file and INSERT into DB table # clear if [ $# -le 0 ] ; then echo "Usage: grant_cleanup_v1.sh " exit fi DB=$1 echo "Connecting..." db2 connect to $DB > /dev/null 2>&1 db2 "CREATE TABLE GRANTS_CLEANUP (STMT VARCHAR(2000))" > /dev/null 2>&1 echo "Generating db2look output..." db2look -d $DB -x -o db2look_grant01.tmp > /dev/null 2>&1 grep "GRANT " db2look_grant01.tmp > db2look_grant02.tmp db2 "TRUNCATE TABLE GRANTS_CLEANUP REUSE STORAGE IMMEDIATE" > /dev/null 2>&1 echo "Inserting into table..." grep "GRANT " db2look_grant02.tmp | while read stmts do db2 "INSERT INTO GRANTS_CLEANUP VALUES ('$stmts')" > /dev/null 2>&1 done # -- generate grants db2 -x "SELECT TRIM(STMT) FROM GRANTS_CLEANUP GROUP BY STMT HAVING COUNT(1) > 1" > re-grants.dcl # -- generate REVOKEs db2 -x "SELECT REPLACE(REPLACE(REPLACE( STMT, 'GRANT ', 'REVOKE '), 'WITH REVOKE OPTION', '' ), ' TO ', ' FROM ') FROM GRANTS_CLEANUP GROUP BY STMT HAVING COUNT(1) > 1" > revokes.dcl echo "DCL scripts are ready for your review !" #rm db2look_grant*.tmp db2 "DROP TABLE GRANTS_CLEANUP" > /dev/null 2>&1 # end




This script will create 2 output files,

revokes.dcl - Review and run this to clean the duplicated privileges, caution - this will revoke all the privileges that are found to be duplicated and in the next step you re-grant then but till then users/applications will fail.

re-grants.dcl - Again, review and run this script to apply the privileges. But now there will be single set of privileges. You can verify this by running the db2look again.