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:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, specify the AO command, and press Enter.
-
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.
-
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.
-
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).
- 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.
-
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).
-
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
- Exit back to the revoke object privileges panel
-
In the Report Revoke Impacts field, specify
Yes, and press Enter.
- In the Report Revoke Impacts field, specify No, and press Enter.
- Specify your preferences on any subsequent confirmation panels to run the REVOKE statement.