Revoking system authority from an SQLID
Before you revoke system authority from an SQLID, you can run a Revoke Impact Report.
Procedure
To revoke system authority from an SQLID:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
-
On the System Catalog (ADB21)
panel, in the
Grantee field, specify the authorization ID with which you want to revoke,
specify the AO command, and press Enter.
The authorization options are displayed on the System Catalog (ADB21) panel.
-
Specify the UA command, and press Enter.
A summary for the SQL ID is displayed on the User Authorizations Summary (ADB2AUS) panel.
-
Specify the AU line command, and press Enter:
Figure 1. User Authorizations Summary (ADB2AUS) panel ADB2AUS n ----------- DB2X User Authorizations Summary ----- Row 1 to Command ===> Scroll Authorities held by C222333% Authority includes SYSADM Commands: AU AP ALL AE AI Line commands: AU - User Only AP - All PUBLIC ALL - All Authorizat AE - Explicit to User AI - Implicit to User ? - Show all line commands Sel Type Explicit Implicit PUBLIC Total --- ---------------- ----------- ----------- ----------- ----------- AU System 2 0 1 3 Storage group 0 21 15 36 Database 0 306 57 363 Table space 1 0 105 106 Table 1 305 2768 3074 Column 0 3 0 3 Plan 4 47 220 271 Collection 0 0 2 2 Package 44 459 218 721 Function 0 4 1 5 Buffer pool 0 0 8 8 Data type 0 0 1 1 JAR 0 0 0 0 Stored procedure 0 4 41 45 Schema 0 0 2 2 Sequence 0 1 0 1 ******************************* END OF DB2 DATA ******************** -
On the System Privileges Authorizations
(ADB2AZ) panel, specify the
R line command and press Enter. This actions starts the revoke process and
its associated Revoke Impact Report.
Figure 2. System Privileges Authorizations (ADB2AZ) panel ADB2AZ in ------- DD1A System Privileges Authorizations on objects -- Row 1 to 5 of 5 Command ===> Scroll ===> CSR Commands: REVOKE GRANT SYSAUTH RMIMPL Line commands: R - Revoke GR - Grant B B CREATE : S B M M D E S S S S S D A I - Interpretation I S S T I O O E X Q Y Y Y Y A C RE - Grantee role N D A T E O N N N B P L S S S D T C RR - Grantor role D S L M C S D 1 2 U L A A C O B A E A D D I T U P A G A D D T P A A S G H D B B S A A R A G S I M M R R D C S Sel Grantor Grantee T Grant date G D A C G S B E C T E N L M C C * * * * * * * * * * * * * * * * * * * * * * * * * * --- -------- -------- - ---------- - - - - - - - - - - - - - - - - - - - - - - R BISVT SUNDARI 2008-02-13 S Y Y Y BISVT JSTEWART 2008-08-21 S Y G BISVT PATSHIM 2008-09-15 S Y G BISVT STEWART 2009-01-28 S Y Y Y Y BISVT PHOENIX 2009-03-13 S Y ******************************* END OF DB2 DATA ******************************* -
On the Revoke System Privileges
(ADB2RZ) panel, in the
Report Revoke Impacts field, specify YES, and press
Enter.
Figure 3. Revoke System Privileges (ADB2RZ) panel AADB2RZ in ---------------- DB2X Revoke System Privileges ---------------- 07:05 Command ===> __________________________________________________________________ REVOKE DB2 SQL ID: SMITHJ Enter any character in front of the privilege to revoke it from the user: _ SYSADM _ BSDS _ CREATESG _ STOPALL _ SYSOPR _ CREATEDBA _ DISPLAY _ STOSPACE _ BINDADD _ CREATEDBC _ RECOVER _ TRACE _ MONITOR1 _ MONITOR2 _ CREATEALIAS _ SYSCTRL _ BINDAGENT _ ARCHIVE _ CREATETMTAB _ DEBUGSESSION _ EXPLAIN _ SQLADM _ DBADM _ DATAACCESS Y ACCESSCTRL _ CREATE_SECURE_OBJECT FROM From . . . . . . . ACCESSCTRL > BY By . . . . . . . . ____________________________________________________ > INCLUDING DEPENDENT PRIVILEGES Cascade revoke . . ___ (Yes/No) Report Revoke Impacts . . . YES (Yes/No) Report Dropped Synonyms & Aliases . . NO (Yes/No) - Check the details on the Revoke Impact Report (ADB2RIP)
panel.
Figure 4. Revoke Impact Report (ADB2RIP) panel ADB2RIP n -------------- DB2X 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/ Lvl T Collection T P/K Name Binder T G Effect - --- -------- - ------------ - -------- -------- - - ------------------------- * 0 PACKADM Z VNDRG S Y ******************************* END OF DB2 DATA *******************************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