Revoke WITH GRANT Option

Document ID : KB000056532
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

After creating a new table, authorization was granted to a user with  
the WITH GRANT option and the user would like to revoke the
WITH GRANT option and specify SELECT, INSERT, UPDATE or
DELETE access for the user only.

Resolution:

This is a multi-step process.

1) Issue the T/UA option in the product for the specific object and the user ID.

    This option will confirm if the Y or G has been authorized for the function.

2) Issue the U/AA option for the specified user ID.

    Locate the specified user and issue the LREVOKE line command next to the object.

    The following screen will display:

PTLGRN ------------------ Line Grant/Revoke ------------------ 2015/01/28 10:4
COMMAND ===>                                                  SCROLL ===> PAGE
                                                                             
REVOKE        _ SELECT     _ INSERT     _ DELETE     _ UPDATE                
                    _ INDEX      _ ALTER       _ REFERENCES _ TRIGGER               
                                                                             
ON TABLE      USER001.DEPT____________________________________________________
                                                                             
FROM  (IDs:)  ________ ________ ________ ________ ________ ________ ________ 
    (ROLEs:)  ________ ________ ________ ________ ________ ________ ________ 
                                                                             
                                                                             
              _ PUBLIC _ PUBLIC AT ALL LOCATIONS                             
                                                                             
              _ BY                                                           
              (IDs:)   ________ ________ ________ ________ ________ ________ 
              (ROLEs:) ________ ________ ________ ________ ________ ________ 
                                                                             
******************************* BOTTOM OF DATA ******************************* 

     Place an 'S' next to the privileges to Revoke, specify the User ID in a FROM IDs field, place an 'S' at the BY and then specify the User ID that granted the authorization.

     This will generate the Revoke statement to be processed.

3)  To GRANT authorization without the WITH GRANT option, issue the T/L command and specify the object name.

      Issue the LGRANT line command next to the object. The following screen will display:

PTLGRN ------------------ Line Grant/Revoke ------------------ 2015/01/28 10:57
COMMAND ===>                                                  SCROLL ===> PAGE
                                                                              
GRANT         _ SELECT     _ INSERT     _ DELETE     _ UPDATE                 
                   _ INDEX      _ ALTER      _ REFERENCES _ TRIGGER                
                                                                              
ON TABLE      USER001.DEPT____________________________________________________
                                                                              
TO    (IDs:)  ________ ________ ________ ________ ________ ________ ________  
    (ROLEs:)  ________ ________ ________ ________ ________ ________ ________  
                                                                              
                                                                              
              _ PUBLIC _ PUBLIC AT ALL LOCATIONS                              
                                                                              
              _ WITH GRANT OPTION                                             
                                                                              
******************************* BOTTOM OF DATA ********************************

Place an 'S' next to the privileges to Grant, specify the User ID in a TO IDs field. 

This will generate the Grant statement to be processed.