Granting a set of authorizations to a user
When managing authorizations, you might want to give all the authorizations that are held by one user (either those held directly or those granted explicitly) to another user or a list of users.
Procedure
To grant all the authorizations that are held by one user to another user:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, specify option AO and press Enter.
- On the System Catalog (ADB21) panel - Authorization options, in the Grantee field, specify the name of the user from whom to copy authorizations.
- Specify option UA, and press Enter.
-
On the User Authorizations Summary
(ADB2AUS) panel, issue the AU
or AE command to display the authorizations that are held by the grantee that you
specified.
AU shows the authorizations that the specified grantee holds directly, and AE shows the authorizations that the specified grantee was granted explicitly.
Figure 1. User Authorizations Summary (ADB2AUS) panel ADB2AUS n ----------- DB2X User Authorizations Summary ------------------------ Authorities held by VNDSHL1% Authority includes SYSADM Commands: AU AP ALL AE AI Line commands: AU - User Only AP - All PUBLIC ALL - All Authorizations AE - Explicit to User AI - Implicit to User ? - Show all line commands Sel Type Explicit Implicit PUBLIC Total --- ---------------- ----------- ----------- ----------- ----------- System 1 0 1 2 Storage group 0 0 3 3 Database 0 0 10 10 Table space 0 0 30 30 Table 0 2 735 737 Column 0 0 0 0 Plan 0 0 79 79 Collection 0 0 15 15 Package 0 0 235 235 Function 0 0 54 54 Buffer pool 0 0 6 6 -
On the User Authorizations
(ADB2AUD) panel, ensure that all of
the values in the Grantee column are the same.
If needed, filter on the Grantee column; see Filtering data on panels.
Figure 2. User Authorizations (ADB2AUD) panel ADB2AUD n --------------- DB2X User Authorizations ---------------------------- Commands: REVOKE GRANT Line commands: A - Auth I - Interpret R - Revoke GR - Grant S Grantor Grantee T Name Authority Date WGO * * * * * * * -- -------- -------- - --------------------------- ----------------- ------ --- R148286 VNDSHL1 Z (SYSTEM) SYSADM 030113 YES VNDSHL1 VNDSHL1 D SHLIMR1 DBADM 030929 YES VNDSHL1 VNDSHL1 D DBSHL DBADM 031003 YES VNDSHL2 VNDSHL1 D DBSHL2 DBADM 031201 NO VNDSHL2 VNDSHL1 D DBSHL2 DBCTRL 031201 NO VNDSHL1 VNDSHL1 T VNDSHL1.VDEPT111 ALL 031202 YES K351156 VNDSHL1 T K351156.GROUPCONFIG ALL 030220 NO VNDSHL1 VNDSHL1 T VNDSHL1.VDEPT1 ALL 030115 YES - Issue the GRANT primary command, and press Enter.
-
On the Grant Privileges
(ADB2AUG) panel, specify the users
to whom you would like to grant authorizations in the Grantees
field, adjust the selected privileges as needed, and press Enter:
Figure 3. Grant Privileges (ADB2AUG) panel ADB2AUG ----------------- DB2X Grant Privileges -------------------- 18:20 Command ===> Specify grantees to use for all the GRANT statements. An "S" preceding the listed privilege indicates the privilege exists in the list of authorizations shown on the previous panel. Replace "S" with null to avoid granting the privilege. GRANT S SYSADM SYSCTRL SYSOPR DBADM DBCTRL DBMAINT PACKADM TO Grantees ===> > With GRANT option ===> YES - retains option for each GRANT statement NO - removes option for all GRANT statementsThe SQL is generated and executed if the size of the generated SQL is less than 32K. Otherwise, the Statement Execution Prompt (ADB2PSTM) panel is displayed, and you can choose to create a batch job with the statements or add the statements to a work statement list (WSL).