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 RL RLA
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 . . . . . . . SUNDARI >
BY
By . . . . . . . . BISVT >
INCLUDING DEPENDENT PRIVILEGES
Cascade revoke . . ___ (Yes/No)
Report Revoke Impacts . . . YES (Yes/No)
Report Dropped Synonyms & Aliases . . NO (Yes/No)
Note that in
the BY field, you can specify ALL if you want to revoke the
specified privileges from the specified users regardless of who granted it.
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
Note that in
the BY field, you can specify ALL if you want to revoke the
specified privileges from the specified users regardless of who granted it.