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.

Did you have a look at SYSIBM.SYSPLANAUTH to verify that the user who wants to issue the REVOKE statement is actually the GRANTOR?

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.

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.

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.