The Group Authorization Tool (SQLADMIN) assists DBAs in managing the access to database objects, simplifies the process of authorization, and shortens the amount of time needed to grant or revoke privileges. It gives DBAs the ability to issue authorizations to groups of users on groups of objects rather than one by one. Individual users can be associated with defined User Groups, and database objects (tables, views, and packages) can be associated with defined Application Groups. Then, GRANTs and REVOKEs can be issued specifying a User Group (grantee) and an Application Group (on objects) using the Group Authorization panel interface.
To use the Group Authorization Tool, an exec called SQMGAINS is supplied that installs five tables and creates the SQLADMIN ID in the database where the tool will be run. This exec must be submitted by an ID with DBA authority in the database. An SQLDBSU input file, SQMGAINS LOADBSU, used by SQMGAINS to create the tables and indexes, may need to be modified before execution. The steps to install are:
The five tables which SQLADMIN uses to manage the Group Authorization functions are listed below. Each one is owned by SQLADMIN. They are:
The Group Authorization Tool is a stand-alone utility which executes as a user application.
All authorizations are granted by the connect ID "SQLADMIN", and are recorded and maintained by the tool.
By using the LIST Functions the user can take advantage of the various reports that are available to help manage database access. These listings, or reports, will show Application Groups and the objects found within them (tables, views and packages); User Groups and user IDs associated with specific User Groups and authorities granted to User Groups.
Because all data about User and Application Groups, as well as authorization information, is kept in database tables (described in Group Authorization Tables), the DBA can also query these tables to obtain information about User and Application Groups and other authorization information. For example, if the DBA would like to see a list of objects belonging to an Application Group together with the specific authorizations on those objects, a query including a join statement could produce the desired result. Or, if the DBA chooses to modify a Group's description (see Figure 71), an update could be made to the APPL_GROUP_TAB or USERID_GROUB_TAB table to make that change.
The Group Authorization tool is designed such that a user can belong to more than one User Group. A user can have the same privilege granted to an object through multiple User Groups. If the user is dropped from one User Group, the user will NOT lose the privilege to the object since it still has authority through another User Group.
The same logic applies to an object which is in more than one Application Group: if that object is dropped from one group, but privileges on it exist through another application group, those privileges will NOT be revoked from users who have authority on it through another group.
Allows the DBA to create both User and Application Groups. The data created by this function will be maintained in the USER_GROUP_TAB and APPL_GROUP_TAB tables respectively (see Group Authorization Tables).
Deletes a group entry from the applicable tables (see Group Authorization Tables). In addition, if any privileges have been granted to a User Group which is being dropped, all privileges will be revoked from all users in that group. If an Application Group is dropped, all privileges which were granted on that group will be revoked from all users who were granted those privileges. There are two exceptions to this rule:
Permits the DBA to populate a group with user IDs (in the case of User Groups) or, tables and views, or packages (in the case of Application Groups). With Application Groups, the group is defined as either a table group (consisting of tables and views only), or a package group (consisting of package names only).
A user can belong to more than one User Group. An object can belong to more than one Application Group. Both group types could consist of only one entity.
When an object is added to an Application Group, if any User Groups have privileges on that group, those privileges will be GRANTED on the newly added object to all users in the User Groups authorized to that Application Group. When an object is dropped from an Application Group, all users holding privileges to that object will have them automatically REVOKED (see exceptions in DROP a Group).
When a user is added to a User Group, all authorities which the group currently has are GRANTED automatically to the added user. When a user is dropped from a group, all authorities which the group has are automatically REVOKED from the user (see exceptions in DROP a Group).
Provides the capability of granting or revoking privileges to User Groups on individual database objects or on Application Groups; on all objects defined in the group. The capability of granting column UPDATE privileges is not implemented. However, you can create a view with the column updates and then grant UPDATE privilege on the view.
The Authorization panel allows you to enter an individual object or a group of objects on which privileges are to be granted or revoked. An individual object is identified by its owner and object name. A group object is identified by its Application Group name. All authorizations are done under the connect ID of SQLADMIN.
For example, in the SYSTEM.SYSTABAUTH table, the grantor for group authorizations will always be SQLADMIN. An extra grant is done for the User Group name to facilitate the implementation of the tool. If SELECT privilege is given to User Group UGROUP1 on Application Group AGROUP1, one extra GRANT SELECT to UGROUP1 on each object in AGROUP1 is generated by the tool. Likewise, this privilege is revoked from the User Group when the REVOKE function is used.
These on-line listings, or reports, are available from within the Group Authorization Tool panel environment:
There are two ways to invoke the SQLADMIN tool:
Once the tool has been invoked, various Group Authorization functions are available as Figure 69 shows.
Figure 69. Control Center Group Authorization Utility Menu
+--------------------------------------------------------------------------------+ | mm/dd/yyyy Control Center hh:mm:ss | |*----------------------- Group Authorization Utility ------------------------* | || Database ===> SQLDBA CTRLID: MSTRSRV1 | | || Option =====> 5 NODE: VMSYSTM1 | | || | | || | | || | | || **OPTION** *****DESCRIPTION***** | | || | | || 1 USER GROUP FUNCTIONS Define/Drop/Alter Group | | || 2 USER FUNCTIONS Add/Drop Users from Group | | || 3 APPLICATION FUNCTIONS Define/Add/Drop Applications | | || 4 GROUP AUTHORIZATIONS Grant/Revoke Group Privileges | | || 5 CONNECT AUTHORIZATIONS Grant/Revoke Connect | | || | | || | | || | | || | | || | | || Select OPTION, and press ENTER to process | | || | | |*---------------------------------------------------------------SQMADMIN-----* | |PF: 1 HELP 3 QUIT 4 EXIT | | | +--------------------------------------------------------------------------------+
|The Group Authorization tool is used to define users to Control Center |and grant them selected authorizations and access to specified objects.
|When you specify 5 for the OPTION in panel SQMADMIN, a panel for CONNECT |authorizations is presented. Figure 70 is an example of the panel SQMADMCO.
|Figure 70. Group Authorization System Authorizations Menu
|+--------------------------------------------------------------------------------+ || mm/dd/yyyy Control Center hh:mm:ss | ||*----------------------- Group Authorization Utility ------------------------* | ||| Database ===> SQLDBA CTRLID: MSTRSRV1 | | ||| NODE: VMSYSTM1 | | ||| | | ||| OPTION => _ ( 1 GRANT 2 REVOKE ) | | ||| | | ||| PRIVILEGES => _ ( 1 CONNECT 2 RESOURCE 3 DBA ) | | ||| | | ||| TO or FROM | | ||| | | ||| USER GROUP NAME => ________ | | ||| INDIVIDUAL => ________ PASSWORD => ________ | | ||| ALLUSERS => _ ( X= YES) | | ||| | | ||| DISPLAY AUTHORITY => ________ ( CONNECT, RESOURCE, DBA, or userid ) | | ||| | | ||| | | ||| | | ||| Enter parms, select OPTION, press ENTER to process | | ||| | | ||*---------------------------------------------------------------SQMADMCO-----* | ||PF: 1 HELP 3 QUIT 4 EXIT | || | |+--------------------------------------------------------------------------------+
|The differences between granting connect authorizations for individual |users or groups of users are as follows: |
|All other functions are basically the same for either individual users or |groups:
These are some example steps to take when first using the Group Authorization tool:
To help you get started, we have provided the following suggestions and example scenarios.
Analyze your current database authorization structure. You can start by grouping tables and views according to some common function or element(s). For instance, perhaps you would like to have all payroll, personnel, or accounting tables in their own specific groups. You can then define Application Groups for these various functions.
Let's say you have 5 tables and 3 views that belong in the personnel organization. You might want to define three Application Groups for this organization. The Application Groups will contain the tables and views from the personnel organization.
| Application Group Definitions |
|---|
|
AGROUP1 contains PERSTAB1, PERSTAB2, PERSTAB3, PERSVIEW1 AGROUP2 contains PERSTAB1, PERSTAB4, PERSTAB5, PERSVIEW2 AGROUP3 contains PERSVIEW3 |
| Note: | PERSTAB1 is common to both AGROUP1 and AGROUP2, and AGROUP3 has only one view defined to it. |
To define (or add) an Application Group, select Option 3 from the Group Authorization Utility Menu (Figure 69). The next menu presented will be the Application Group Menu. Here you must enter the name of the Application Group, together with the option to Add a Group, and the Group Type.
Figure 71. Control Center Application Group Menu
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*----------------------- GROUP AUTHORIZATION Utility ----------------------* | || Database ===> SQLDBA CTRLID: MSTRSRV1| | || Group =====> (A) NODE: VMSYSTM1| | || Option =====> (B) | | || APPLICATION GROUP MENU | | || | | || 1 ADD GROUP | | || 2 DROP GROUP | | || 3 ADD/DROP OBJECTS IN GROUP | | || 4 LIST GROUPS | | || 5 LIST OBJECTS IN A GROUP | | || 6 LIST GROUP AUTHORIZATIONS | | || | | || | | || DESCRIPTION => (C)________________________ | | || GROUP TYPE => (D) (T or P) | | || | | || Choose OPTION, select DATABASE, press ENTER to process | | || | | |*---------------------------------------------------------------SQMADMAP---* | |ENTER F1= HELP F3= QUIT F4= EXIT | | | | | +--------------------------------------------------------------------------------+
The required, or optional, fields are:
After the Application Group has been added or defined, we need to add the objects (tables and views, or packages) to the group. From the Application Group Menu, select Option 3 to add the objects to AGROUP1.
The Object Functions Menu is where tables and views, or packages are added to the Application Group. In Figure 72 we have added the personnel tables and view to Application Group AGROUP1.
Figure 72. Add Objects to Your Application Group
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*----------------------- GROUP AUTHORIZATION Utility ------------------------* | || Database ===> SQLDBA CTRLID: MSTRSRV1 | | || Group =====> AGROUP1 NODE: WMAVM7 | | || Option =====> 1 | | || OBJECT FUNCTIONS | | || | | || 1 ADD OBJECTS | | || 2 DROP OBJECTS | | || | | || OBJECTS: OWNER OBJECT NAME OWNER OBJECT NAME | | || -------- ------------------ -------- ----------------- | | || PERS____ PERSTAB1__________ ________ _________________ | | || PERS____ PERSTAB2__________ ________ _________________ | | || PERS____ PERSTAB3__________ ________ _________________ | | || PERS____ PERSVIEW1_________ ________ _________________ | | || ________ __________________ ________ _________________ | | || ________ __________________ ________ _________________ | | || | | || Enter Object ids, select OPTION, press ENTER to process | | || | | |*---------------------------------------------------------------SQMADMOB-----* | |ENTER F1= HELP F3= QUIT F4= EXIT | | | +--------------------------------------------------------------------------------+
Once the Application Groups have been set up, define the users of your database to whom privileges must to be granted for the various personnel tables and view identified above.
Let's assume we have 10 users whose IDs are USER1 through USER10. You can now group these users by function or access needs, or whatever criteria you use to determine authorization.
In this case, we will define 3 User Groups (UGROUP1 through UGROUP3). Place the 10 users, User1 through User 10, into any of the 3 defined User Groups:
| User Group Definitions |
|---|
|
UGROUP1 consists of User1, User2, User3, User4, User5, User6 UGROUP2 consists of User1, User7, User8, User9, User10 UGROUP3 consists of User1 through User10 |
| Note: | User1 is in all 3 groups, all ten users are in UGROUP3. Any distribution of users is allowed, depending on your authorization needs. |
The User Group Menu is where you can add or delete a User Group, populate a User Group, and list User Groups.
Figure 73. Menu for User Group Functions
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*----------------------- GROUP AUTHORIZATION Utility ------------------------* | || Database ===> SQLDBA CTRLID: MSTRSRV1 | | || Group =====> ________ NODE: VMSYSTM1 | | || Option =====> ____ | | || USER GROUP MENU | | || | | || 1 ADD GROUP | | || 2 DROP GROUP | | || 3 ADD/DROP USERS IN A GROUP | | || 4 LIST GROUPS | | || 5 LIST USERS IN A GROUP | | || 6 LIST GROUP AUTHORIZATIONS | | || | | || | | || | | || DESCRIPTION => ______________________________________________ | | || | | || Select OPTION, enter GROUP NAME, press ENTER to process | | || | | |*---------------------------------------------------------------SQMADMGP-----* | |ENTER F1= HELP F3= QUIT F4= EXIT | | | | | +--------------------------------------------------------------------------------+
After the specific User Group has been added, you can populate the User Group by selecting Option 3 from the User Group Menu.
Figure 74. Users Added to UGROUP1
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*---------------------- GROUP AUTHORIZATION Utility -------------------------* | || Database ===> SQLDBA CTRLID: MSTRSRV1 | | || Group =====> UGROUP1 NODE: WMAVM7 | | || Option =====> 1 | | || USER MENU | | || | | || 1 ADD USERS | | || 2 DROP USERS | | || 3 LIST USERS | | || | | || USER LIST: NAME NAME NAME NAME NAME | | || User1___ ________ ________ ________ ________ | | || User2___ ________ ________ ________ ________ | | || User3___ ________ ________ ________ ________ | | || User4___ ________ ________ ________ ________ | | || User5___ ________ ________ ________ ________ | | || User6___ ________ ________ ________ ________ | | || ________ ________ ________ ________ ________ | | || | | || Select OPTION, enter USERS if needed, press ENTER to process | | || | | |*---------------------------------------------------------------SQMADMUS-----* | |ENTER F1= HELP F3= QUIT F4= EXIT | +--------------------------------------------------------------------------------+
Once the users have been added, we can grant object authority to the User Group UGROUP1.
Now issue grants or revokes to those User Groups associated with the six users who belong to UGROUP1. Using the Authorization Menu, grant SELECT and UPDATE on AGROUP1 to UGROUP1, as shown below.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*----------------------- GROUP AUTHORIZATION Utility -----------------------* | || Database ===> SQLDBA CTRLID: MSTRSRV1 | | || NODE: VMSYSTM1 | | || OPTION => 1 ( 1 GRANT 2 REVOKE ) | | || | | || PRIVILEGES => 1 4 ( 1 SELECT 2 INSERT 3 DELETE | | || 4 UPDATE 5 ALL 6 EXECUTE | | || ON | | || OBJECT OWNER => ________ | | || OBJECT NAME => __________________ | | || OR | | || APPLICATION GROUP NAME => AGROUP1 | | || | | || TO or FROM | | || USER GROUP => UGROUP1 | | || | | || WITH GRANT OPTION => _ ( 1 YES) | | || | | || Enter parms, select OPTION, press ENTER to process | | || | | |*---------------------------------------------------------------SQMADMPR----* | |ENTER F1= HELP F3= QUIT F4= EXIT | | | +--------------------------------------------------------------------------------+
This will have the effect of granting SELECT and UPDATE privileges on tables PERSTAB1, PERSTAB2, PERSTAB3, and PERSVIEW1 to user IDs User1 through User6.
To continue with this scenario, say you were to also enter:
| Grant to UGROUP2 |
|---|
|
GRANT SELECT, UPDATE on AGROUP1 to UGROUP2 |
This grants SELECT and UPDATE privileges on tables PERSTAB1, PERSTAB2, PERSTAB3, and PERSVIEW1 to user IDs User1, User7, User8, User9, and User10.
| Note: | User1 has been granted SELECT and UPDATE privileges to the same tables in AGROUP1 by virtue of belonging to two different User Groups. |
This means that if this revoke were done:
| Revoke from UGROUP1 |
|---|
|
REVOKE UPDATE on AGROUP1 from UGROUP1 |
User2, User3, User4, User5 and User 6 would lose Update privileges on the tables in AGROUP1. They would retain the SELECT privilege. However User1 would retain Update privilege because User1 still has it by belonging to UGROUP2.
This example is only one of very many which can be developed and maintained by using the Group Administration tool. All sorts of variations are possible, and the tool will maintain the structures and authorizations for you.
There are some performance considerations for doing on-line authorizations. However, none of the functions will take longer than if they were done individually by a DBA through ISQL or some other method. In fact, overall elapsed time should be substantially less when key strokes and preparation time are taken into consideration.
Another performance factor, though probably of little concern, is that two tables (USERID_GROUP_TAB and APPL_GROUP_TAB) have primary indexes which will be incremental in nature. These should be monitored by the DBA for reorganization; however, this should only be a problem in the event that very many groups are defined.
| 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 object belonging to an Application Group, this authorization is NOT recorded in the tool's tables.
Thus, if any user ID defined in UGROUP1 were later to have its privilege to SQLDBA.ACTIVITY revoked, because that table was defined in an Application Group, the privilege would be deleted from the SYSTEM.SYSTABAUTH table since the Group Authorization tool does NOT know about this individually granted authority.
The way around this limitation is to define the table in its own group. The same way a single user can be defined in a User Group, so too, a single table (or view, or package) can be defined in its own application group.
Figure 76. Query Using Join Statement
SELECT A.APPL_GROUP_NAME, O.OBJECT_OWNER, O.OBJECT_NAME, G.S_AUTH,
U.USERID, UG.GROUP_NAME
FROM SQLADMIN.USERID_GROUP_TAB UG, SQLADMIN.USERID_TAB U,
SQLADMIN.OBJECT_TAB O, SQLADMIN.GROUP_AUTH_TAB G,
SQLADMIN.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 77. 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.