Groups
A group is an identifier that can be used to apply permissions to a list of users associated with the identifier.
A group allows multiple users to be referenced by a single name.
For example, a company has an accounting group to identify the accounting department employees as a whole, and a payroll group to identify the payroll department employees as a whole. To define these groups, the DBA creates the groups and adds all the users in the associated departments to their respective groups. The groups can be easily maintained by adding and dropping users as they join or leave the departments.
Note: A user can be a member of more than one group.
Working with Group Objects
You can perform the following basic operations on group objects:
• Create and alter group objects
• View existing group objects, including the detailed properties of each individual object
• Drop group objects
In SQL, you can accomplish these tasks with the CREATE GROUP, ALTER GROUP, and DROP GROUP statements when working in a session connected to the iidbdb database.
You can also work with group objects using Actian Director and VDBA.
Example: Creating, Altering, and Dropping a Group using SQL Statements
To create a new group, specify a user-defined group ID in a CREATE GROUP statement, and then list the users in the group on the WITH USERS clause. The group can be amended later by ADD USERS or DROP USERS clauses on the ALTER GROUP statement. The group can be deleted with the DROP GROUP statement.
Here are examples of using the CREATE GROUP, ALTER GROUP, and DROP GROUP statements:
1. Create a group identifier for a company’s telephone sales force and put the salespersons’ user IDs in the group user list:
CREATE GROUP tel_sales
WITH USERS = (harryk, joanb, jerryw, arlenep);
2. Create the group identifier and_muchmuchmore and reserve it for later use. This is done by omitting the WITH USERS clause:
CREATE GROUP and_muchmuchmore;
3. Add two users to the group tel_sales and drop three users.
The adding and dropping must be done in separate ALTER statements:
ALTER GROUP tel_sales
ADD USERS (dannyh, helent);
ALTER GROUP tel_sales
DROP USERS (harryk, joanb, arlenep);
4. Drop all users from the group researchers. Then drop the group. The DROP ALL option should be run prior to dropping a group, since a group cannot be dropped if its user list has any members.
ALTER GROUP researchers DROP ALL;
DROP GROUP researchers;
If a user is dropped from a group but is currently active in a session, that session continues to have the group’s permissions until it terminates. “Currently active” means that the user is currently connected to a database. If a group is dropped, all the permissions assigned to the group are dropped.
Groups and Permissions
After a group is created, you can associate permissions with it. When you grant permission to a group, you are, in effect, granting that same permission to each user in the group.
Groups are a convenient way to give the same permissions to many users at once.
Groups also make managing the permissions easy by allowing you to add users to (and remove users from) the group. For example, grant the payroll group insert, delete, and select permissions on the payroll tables, which gives all the users in the group those permissions. If an employee leaves the payroll department, or if a new employee joins, you simply have to drop or add a user from the group, without modifying the permissions. Similarly, if you find that the group needs fewer or more permissions, revoke or grant the permissions once, for the entire group, rather than individually for each member of the group.
Being a member of a group, however, does not automatically give a user the permissions granted to the group. Users must specifically identify themselves as part of a group to be allowed the associated permissions.
A user can be identified as part of a group in two ways:
• Specifying a group ID at session startup
• Specifying a default group for the user. A default group is specified for a user using the SQL statements CREATE USER or ALTER USER.
Specifying Group ID at Session Startup
When starting a session, a user can specify a group identifier, as follows:
• On the –G flag for many system commands. For details, see the Command Reference.
• With the CONNECT statement as part of an application.
• As part of the connection profile for an OpenROAD session. For more information, see online help for the Create Connection Profile dialog in OpenROAD.