To use the Group Authorization tool, you need to:
1. | Define Application Groups |
2. | Add (or Drop) objects to the Application Groups |
3. | Define User Groups |
4. | Add Users to the User Groups |
5. | Grant Authorities to the User Groups |
|In addition, you need to be able to:
|
6. | Drop a User |
7. | Drop a Group |
To reach the Group Authorization Menu shown in Figure 24:
Figure 24. Control Center Group Authorization Menu
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------- GROUP AUTHORIZATION MENU --------------------* | | | DATABASE => SQLDBA | | | | OPTION => | | | | | | | | 1 USER GROUP FUNCTION | | | | 2 USER FUNCTION | | | | 3 APPLICATION GROUP FUNCTION | | | | 4 GROUP AUTHORIZATIONS | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | *----------------------------------------------------------------- SQC19 ----* | |PRESS ENTER TO PROCESS | |ENTER F1=HELP F3=EXIT | +--------------------------------------------------------------------------------+
Before using the Group Authorization tool, you need to analyze your current database authorization structure. Start by grouping tables and views according to some common function or element(s). For instance, perhaps you want all payroll, personnel, or accounting tables in their own specific groups. Once you have decided this, you can define Application Groups for these various functions.
Let's say you have 5 tables and 3 views that belong in the personnel organization and you want to define three Application Groups for this organization. The Application Groups will contain the tables and views from the personnel organization.
The following steps show how to use the tool to define the application groups. Before starting, decide what tables will be in which group as shown in Application Group Definitions:
| 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 (PERSVIEW3). |
an Application Group, enter Option 3 from the Group Authorization Menu to get to the Application Group Menu.
Figure 25. Control Center Application Group Menu
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss| | *-------------------------- APPLICATION GROUP MENU ---------------------------*| | | DATABASE => SQLDBA || | | GROUP => AGROUP1 || | | OPTION => 1 || | | || | | 1 ADD GROUP || | | 2 DROP GROUP || | | 3 ADD/DROP OBJECTS IN A GROUP || | | 4 LIST GROUPS || | | 5 LIST OBJECTS IN A GROUP || | | 6 LIST GROUP AUTHORIZATIONS || | | || | | *************************************************************************** || | | || | | DESCRIPTION => Personnel tables || | | GROUP TYPE => T (T=TABLES AND VIEWS, P=PACKAGES) || | | || | | || | | || | *------------------------------------------------------------------ SQC26 ----*| | | | PRESS ENTER TO PROCESS | | ENTER F1=HELP F3=EXIT F12=RETURN | +--------------------------------------------------------------------------------+
Enter the name of the Application Group you are adding in the Group field, choose Option 1, Add Group, and enter the Group Type (use T for tables or views or P for packages). You can also choose to add a description of the group in the Group Description field. If you enter a Group Description, it is stored in the database record and you can view it in the Application Group list report. The database you are working with is shown at the top of the screen in the Database field. If you want to work with a different database, enter the name of the new database in the DATABASE field.
The example shown in Figure 25 shows how to add the group AGROUP1. The Application Group type is T for tables or views, and "Personnel tables" has been added as the Group DESCRIPTION.
Once you have defined the Application Group, you add the objects
(that is, tables and views, or packages) to the group. On the Application Group Menu Figure 25, choose Option 3 to add the objects to the Application Group. You add tables and views, or packages to the Application Group on the Object Functions menu. Figure 26 shows how to add the three personnel tables and a view to the AGROUP1 Application Group.
|If you later want to delete an object from an application group, use |this function (accessed by Group Authorization, main menu option 3) |to delete the object.
Figure 26. Adding Objects to Your Application Group
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- OBJECT FUNCTIONS --------------------------* | | | DATABASE => SQLDBA | | | | GROUP => AGROUP1 | | | | OPTION ===> 1 | | | | | | | | 1= ADD OBJECTS 2= DROP OBJECTS | | | | | | | | OWNER OBJECT NAME | | | | PERS PERSTAB1 | | | | PERS PERSTAB2 | | | | PERS PERSTAB3 | | | | PERS PERSVIEW1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | *----------------------------------------------------------------- SQC28 ----* | | | | PRESS ENTER TO PROCESS | | ENTER F1=HELP F3=EXIT F12=RETURN | +--------------------------------------------------------------------------------+
After you have established the Application Groups, you define the
users of your database to whom you want to grant privileges. For example, let's assume you have ten users whose IDs are USER1, USER2, and so on through USER10. You can now group these users by the criteria you use to determine authorization. Our examples use the following three 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. |
Use the User Group Menu to add or delete a User Group, to define the members of a User Group, and to list User Groups. To reach the User Group Menu: From the Group Authorization Menu, select Option 1 to reach the User Group Menu.
Figure 27 show how to add the UGROUP1 User Group.
Figure 27. Adding a User Group
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *------------------------- USER GROUP MENU ------------------------* | | | DATABASE => SQLDBA | | | | GROUP => UGROUP1 | | | | OPTION => 1 | | | | | | | | 1 ADD GROUP | | | | 2 DROP GROUP | | | | 3 ADD USERS TO A GROUP | | | | 4 LIST GROUPS | | | | 5 LIST USERS IN A GROUP | | | | 6 LIST GROUP AUTHORIZATIONS | | | | | | | | | | | | | | | | | | | | ***************************************************************************| | | | | | | | DESCRIPTION => Executives | | | | | | | | | | | *----------------------------------------------------------------- SQC20 ----* | | | | PRESS ENTER TO PROCESS | | ENTER F1=HELP F3=EXIT F12=RETURN | +--------------------------------------------------------------------------------+
|Figure 28 shows the result of using option 4 to |see what groups already exist.
Figure 28. Checking for Existing User Group
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *------------------------- USER GROUP LIST ---------------------------* | | | DATABASE => SQLDBA | | | | | | | | | | | | GROUP DESCRIPTION | | | | | | | | TESTGRP TEST GROUP 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | *----------------------------------------------------------------- SQC21 ----* | | | | PRESS ENTER TO PROCESS | | ENTER F1=HELP F3=EXIT F12=RETURN | +--------------------------------------------------------------------------------+
to add users to the User Group you have defined in Step 3. You enter the User Group member on the Add Users screen shown in Figure 29, or Option 2 from the Group Authorization Menu.
Figure 29. Adding Users to UGROUP1
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- ADD USERS ------------------------* | | | DATABASE => SQLDBA | | | | GROUP => UGROUP1 | | | | OPTION ===> 1 | | | | | | | | 1= ADD USER(S) | | | | | | | | USER | | | | User1 | | | | User2 | | | | User3 | | | | User4 | | | | User5 | | | | User6 | | | | | | | | | | | | | | | | | | | | | | | *-------------------------------------------------------------- SQC29 ----* | | | | PRESS ENTER TO PROCESS | | ENTER F1=HELP F3=EXIT F12=RETURN | +--------------------------------------------------------------------------------+
In this step, you grant object authority to the
User Group. Use the Authorization Menu to issue grants or revokes to the User Groups. Figure 30 shows how to grant Select and Update on AGROUP1 to UGROUP1.
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | | *-------------------------- AUTHORIZATIONS MENU --------------------------* | | | DATABASE => SQLDBA | | | | | | | | 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 ) | | | | | | | *----------------------------------------------------------------- SQC27 ----* | | | |PRESS ENTER TO PROCESS | |ENTER F1=HELP F3=EXIT F12=RETURN | +--------------------------------------------------------------------------------+
The choices made in the example show in Figure 30, grant Select and Update privileges on tables PERSTAB1, PERSTAB2, PERSTAB3, and PERSVIEW1 to user IDs USER1 through USER6. To grant multiple privileges at the same time, enter the number corresponding to each privilege separated by a space.
Let's assume you have also granted Select and Update privileges on tables PERSTAB1, PERSTAB2, PERSTAB3, and PERSVIEW1 to UGROUP2 (that contains user IDs |USER1, and USER7 through USER10). Note that you have given USER1 Select and Update privileges on the same tables in AGROUP1. If you revoked the Update privilege from UGROUP1, all members of UGROUP1 would lose their Update privilege except USER1. USER1 would keep the UPDATE privilege because it is also a member of UGROUP2 that still has the UPDATE privilege.