Revoking system authority from an SQL ID
Before you revoke system authority from an SQL ID, 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, specify the
AO command, and press Enter.
The authorization options are displayed on the System Catalog (ADB21) panel - Authorization options.
- In the Grantee field, specify the authorization ID from which you want to revoke authorizations.
-
Specify option UA, and press Enter.
A summary for the SQL ID is displayed on the User Authorizations Summary (ADB2AUS) panel.
-
Specify the AU line command next to
System, 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 next to the authorization that you want to revoke,
and press Enter.
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 ******************************* - To continue with the revoke operation, exit (PF3) back to the Revoke System Privileges (ADB2RZ) panel, specify NO in the Report Revoke Impacts field, and press Enter.
-
Specify your preferences on any subsequent confirmation panels to run the REVOKE
statement.
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