Not sure if anyone is interested in this or not, but I figured I would post up.

Requirements. Revoke CONTROL.

Problem. Users having CONTROL access. Prior to 8.2 version for developers to be able to replace the data. Once on 8.2 CONTROL no longer needed S,I,U,D is enough to perform the replace. Problem arrises when you exsecute REVOKE it revokes control but grants INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES "with grant option" which now allows developers to grant access on the object to people that should not have access.

Solution. Revoke CONTROL followed by revoking the new given grants followed by granting correct grants.

This script is very primitive but work If you can provide a better solution please do so.

cat revoke_control.sh
#!/bin/ksh
#
# this script is to clean up the control access
#
#

dbname=$1
schema=$2

db2 connect to $dbname;

db2look -d $dbname -z $schema -x | grep -i control > zaza
#______________________________
#clean up the extra characters

sed 's/ "."/./g' zaza >zaza1

sed 's/"//g' zaza1 > zaza

################################################## ####
#Generate revoke and new grants
################################################## ####

sed 's/ GRANT / REVOKE /g' zaza >revoke
sed 's/ TO / FROM /g' revoke >revoke1

sed 's/ CONTROL / ALL /g' revoke1 > revoke2

sed 's/ CONTROL / SELECT, INSERT, UPDATE, DELETE /g' zaza >newgrant

rm revoke
mv revoke1 revoke

db2 -tvf revoke >revoke.out
db2 -tvf revoke2 >>revoke.out
db2 -tvf newgrant >>revoke.out

Have fun.

Thanks for sharing the info.

Do you mean REPLACE option using IMPORT or LOAD ?

An alternative to revoking CONTROL, SELECT etc is REVOKE ALL FROM TABLE1
ie
sed 's/ CONTROL / SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES /g' revoke1 > revoke2

can be

sed 's/ CONTROL / ALL /g' revoke1 > revoke2


HTH

Sathyaram

Yes

and

Yes

Sathyaram,

I have did more testing on this and it looks like your recomendation about using ALL will prevent some of the headache. It is specially comes into play when working with the views. Great catch.