Monday, April 23, 2012

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: " 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.

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.