Results 1 to 4 of 4

Thread: Revoke CONTROL

  1. #1
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557

    Unanswered: Revoke CONTROL

    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.
    Last edited by Cougar8000; 01-31-06 at 11:07.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Yes

    and

    Yes
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by sathyaram_s
    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
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •