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.