Revoking an authorization

You can revoke the authority that users have to grant privileges on Db2 objects. You can also revoke the privileges that users have to use those objects. Db2 Admin Tool guides you through the process without requiring you to know the syntax of the REVOKE SQL statements.

Procedure

To revoke authorizations:

  1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
  2. On the System Catalog (ADB21) panel, specify the AO command, and press Enter.
  3. On the System Catalog (ADB21) panel - Authorization options, specify the option for the object type for which you want to revoke authorizations.

    For example, specify option GVA for global variable authorizations, and press Enter.

  4. On the object authorizations panel, issue the R line command against the authorization that you want to revoke, and press Enter.

    For example, the following figure shows the Global Variable Authorizations (ADBPAGV) panel:

    Figure 1. Global Variable Authorizations (ADBPAGV) panel
    ADBPAGV n ----------- DD1A Global Variable Authorizations ---- Row 1 to 3 of 3 
 Command ===>                                                  Scroll ===> CSR  
 
 Commands: GRANT  REVOKE  RMIMPL                                                                                
 Line commands:                                                              
  GR - Grant  R - Revoke  I - Interpret  GV - Global Variable         W         
  ? - Show all line commands                                        R R         
                                                                    E I         
                          G                            H            A T         
 Select Grantor  Grantee  T Schema   Name              G Timestamp  D E         
        *        *        * *        *                 * *          * *         
 ------ -------- -------- - -------> ----------------- - ---------> - -         
        SYSADM   PUBLIC     SYSIBM   CLIENT_IPADDR       2012-12-21 Y           
        SYSADM   PUBLIC     SYSIBMAD GET_ARCHIVE         2012-12-21 Y           
        SYSADM   PUBLIC     SYSIBMAD MOVE_TO_ARCHIVE     2012-12-21 Y           
 ******************************* END OF DB2 DATA *******************************
    Restriction: You cannot revoke a privilege from a global variable if any of the following conditions exist:
    • A function that is owned by the revokee references (READ or WRITE privilege) the specified global variable.
    • A view that is owned by the revokee references (READ or WRITE privilege) the specified global variable.
    • A trigger that is owned by the revokee references (READ or WRITE privilege) the specified global variable.
    • A procedure that is owned by the revokee references (READ or WRITE privilege) the specified global variable.
  5. On the revoke object privileges panel, specify the privilege that you want to revoke and any associated information.

    For example, the following figure shows the Revoke Variable Privileges (ADBPRGV) panel:

    Figure 2. Revoke Variable Privileges (ADBPRGV) panel
    ADBPRGV n --------------- DD1A Revoke Variable Privileges --------------- 05:57
 Command ===> __________________________________________________________________
                                                                                
  REVOKE                                                  DB2 SQL ID: ADM001   
                                                                                
  Enter any character in front of the privilege to revoke it from the user:     
                                                                                
  _ ALL                                                                         
  Y READ                                                                        
  Y WRITE                                                                       
                                                                                
  ON VARIABLE                                                                   
    Schema . . . ADM001 >                                                     
    Name . . . . TEST               >                                           
  FROM                                                                          
    From . . . . X1                                                          >  
  BY                                                                            
    By . . . . . ___________________________________________________________ >  
  RESTRICT . . . ___  (Yes/No)                                                  
                                                                                
  Report Revoke Impacts . . . YES  (Yes/No)

    On this panel, specify the privilege that you want to revoke and the FROM, BY, and RESTRICT clause information. For more information about these clauses, see REVOKE (variable privileges) (Db2 12 for z/OS).

  6. Optional: Review the revoke impact report:

    This report helps you determine how the authorizations and database objects will be affected by revoking an authorization before you actually revoke it.

    1. In the Report Revoke Impacts field, specify Yes, and press Enter.

      If the following message is displayed, your user ID does not have the authority to execute the REVOKE statement:

      Revoker does not have SYSADM/SYSCTRL/SECADM/ACCESSCTRL

      Otherwise, the Revoke Impact Report (ADB2RIP) panel is displayed, as shown in the following figure.

      Figure 3. Revoke Impact report (ADB2RIP)
      ADB2RIP n -------------- DD1A Revoke Impact Report ---------------- Row 1 of 1 
 Command ===> ________________________________________________ Scroll ===> PAGE 
                                                                                
                                                                                
                                                                                
 Line commands:  I - Interpretation                                             
                                Owner/                                          
 S    Grantee  G Resource N/ O  Schema/  Grantor/ G H Privileges/               
   Lv          T Collection  T  P/K Name Binder   T G Effect                    
 - -- -------- - ----------- -- -------- -------- - - --------------------------
 _ 0  X1         TEST        GV ADM001   ADM001      YY                       
 ******************************* END OF DB2 DATA *******************************

      For information about any of these columns and their values, see the online help (PF1).

    2. Issue the I line command next to the object for which you want to display interpretation information, and press Enter.

      The Interpretation of revoked privileges (ADB2RIPI) panel is displayed, as shown in the following figure.

      Figure 4. Interpretation of revoked privileges (ADB2RIPI) panel
      ADB2RIPI  ---------- DD1A Interpretation of revoked privileges ---------- 07:34
 Command ===> __________________________________________________________________
                                                                                
 Variable privileges:                                                           
                                                                                
 Variable schema . . . : ADM001                                                
 Variable name . . . . : CH1                                                    
 Held by auth ID . . . : RIPA                                                   
 Granted by  . . . . . : ADM001                                                
 Grant timestamp . . . : 2013-04-08-04.28.07.407623                             
 Auth level of grantor :                                                        
                                                                                
 The following privileges are held by the grantee:                              
 READ variable  . . :     Grant:                                                
 WRITE variable . . : Yes Grant: No
    3. Exit back to the revoke object privileges panel
  7. In the Report Revoke Impacts field, specify No, and press Enter.
  8. Specify your preferences on any subsequent confirmation panels to run the REVOKE statement.