Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Duesseldorf - Germany - Europe - Earth - Galaxy
    Posts
    18

    Question Unanswered: DB2/zOS: REVOKE privs from PLAN...

    Hello,

    I've got a problem revoking privileges from a plan. Normally our programmers issue

    "SET CURRENT SQLID = 'myplanowner';"
    "GRANT EXECUTE ON PLAN myplan TO PUBLIC;"

    That works, they have created a privilege on 'myplan' for PUBLIC with 'myplanowner', a RACF-group, as grantee.

    When they want to this privilege it doesn't work,

    "REVOKE EXECUTE ON PLAN myplan FROM PUBLIC;" doesn't work, neither with SET CURRENT SQLID" nor without. Result = SQLCODE -556, ERROR: PUBLIC CANNOT HAVE THE EXECUTE PRIVILEGE ON PK7VTEST REVOKED BY KIRPVO BECAUSE THE REVOKEE DOES NOT POSSESS THE PRIVILEGE OR THE REVOKER DID NOT MAKE THE GRANT

    Certainly I can revoke with the "BY 'myplanowner'" keyword since I am SYSADM, but I don't unterstand why a "normal" user ('myplanowner') can't revoke a privilege he has granted on an object that belongs to him.

    Did I missunderstand the SQL Reference?

    Regards,
    Volker.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did you have a look at SYSIBM.SYSPLANAUTH to verify that the user who wants to issue the REVOKE statement is actually the GRANTOR?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2003
    Location
    Duesseldorf - Germany - Europe - Earth - Galaxy
    Posts
    18
    Yes, I used CA's RC/Query to see that grantor was 'myplanowner'. When I as SYSADM try to revoke the privilege I have to use the "BY 'myplanowner'" keyword.

    Regerds,
    Volker.

  4. #4
    Join Date
    May 2006
    Posts
    82
    Are you sure the GRANT statements executed earlier were successfull...? Do you find the entry in catalog..? The secondary id used to GRANT should be used while revoking. If am right the user issued the REVOKE statement is KIRPVO.
    Vinay,

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    just to ensure:
    the revoke was only done once ?

    if there is a:
    SET CURRENT SQLID = 'KIRPVO' ;
    REVOKE EXECUTE ON PLAN myplan FROM PUBLIC ;
    ( ... other statements ... )
    REVOKE EXECUTE ON PLAN myplan FROM PUBLIC ;

    the second statement will receive an SQLCODE -556
    and if a ROLLBACK is done because of that the first REVOKE is also undone.

Posting Permissions

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