Control Center Operations Guide for VSE

Special Considerations

Using Control Center you grant authorizations to Groups, not to individual user ID's.

1. If you want to grant authorizations to an individual user ID, define a User Group with only one user ID in it. You can then grant authorizations to that user group which, in effect, let's you grant authorizations to a single user ID.
2. Authorizations can be granted to an individual table that is not a member of an Application Group. For example:

   Individual Tables
   GRANT Select on SQLDBA.ACTIVITY to UGROUP1

   This will grant the privilege to all users defined in UGROUP1. However, since the grant is to an individual database entity and not to an Application Group, the Group Authorization tool does not record the authorizations in its tables.

   If any user ID in UGROUP1 were to later have its privilege to SQLDBA.ACTIVITY revoked due to the table being defined in an Application Group, the privilege would be deleted from SYSTEM.SYSTABAUTH since the Group Authorization tool does not know about the individually granted authority. If you want the tool to keep a record of the individual authorization, define the table in its own group. Just as you can define a single user in a User Group, you can also define a single object in its own Application Group.

3. You can grant or revoke the following authorizations:
   • SELECT, INSERT, UPDATE, DELETE and ALL on Tables and Views. The ALL authorization is equivalent to the SELECT, INSERT, UPDATE, and DELETE privileges. It does NOT include ALTER, INDEX, or REFERENCES.
   • EXECUTE on Packages
   • Use the GRANT option on all the above authorizations:
4. All grants and revokes are executed with the SQLMSTR connect ID.
5. When granting EXECUTE on packages, the "SQLMSTR" ID must have the RUN privilege with GRANT option in order to do the GRANT.
6. You cannot grant referential constraint privileges.
7. The tool will not affect any current authorizations recorded in SYSTEM.SYSTABAUTH and SYSTEM.SYSPROGAUTH, since the "SQLMSTR" ID grants all privileges. As you build your authorization scheme and issue grants using the tool, remove old or non-SQLMSTR grants as appropriate.
8. The following is an example of a query you can use to obtain information from the Group Authorization tables. For example, if you want a list of those users (and their group IDs) that have SELECT authorization to all the objects in a particular Application Group, use:

   Figure 31. Query Using Join Statement
   

   SELECT A.APPL_GROUP_NAME, O.OBJECT_OWNER, O.OBJECT_NAME, G.S_AUTH, U.USERID, UG.GROUP_NAME FROM SQLMSTR.USERID_GROUP_TAB UG, SQLMSTR.USERID_TAB U, SQLMSTR.OBJECT_TAB O, SQLMSTR.GROUP_AUTH_TAB G, SQLMSTR.APPL_GROUP_TAB A WHERE A.APPL_GROUP_NAME = 'AGROUP5' AND O.APPL_GROUP_ID = G.APPL_GROUP_ID AND U.GROUP_ID = G.USERID_GROUP_ID AND A.APPL_GROUP_ID = G.APPL_GROUP_ID AND UG.GROUP_ID = U.GROUP_ID AND G.S_AUTH > ' ' ORDER BY 1,2,3,5

   Figure 32. Results of Join Query
   

   APPL GROUP OBJECT OBJECT GROUP NAME OWNER NAME S AUTH USERID NAME ---- ------ -------- ------ ------ ------ AGROUP5 M760595 TABERROR G TESTGP3 TESTGP3 AGROUP5 M760595 TABERROR Y TESTGP4 TESTGP4 AGROUP5 M760595 TABERROR G USER1 TESTGP3 AGROUP5 M760595 TABERROR Y USER1 TESTGP4 AGROUP5 M760595 TABERROR G USER2 TESTGP3 AGROUP5 M760595 TABERROR G USER3 TESTGP3 AGROUP5 M760595 TABERROR Y USER4 TESTGP4 AGROUP5 M760595 TABERROR Y USER5 TESTGP4 AGROUP5 SQLDBA ACTIVITY G TESTGP3 TESTGP3 AGROUP5 SQLDBA ACTIVITY Y TESTGP4 TESTGP4 AGROUP5 SQLDBA ACTIVITY G USER1 TESTGP3 AGROUP5 SQLDBA ACTIVITY Y USER1 TESTGP4

   Or, if you want to modify a group's description, you can simply update the GROUP_DESC field for the USERID_GROUP_TAB or the APPL_DESC field of the APPL_GROUP_TAB.