Alter Group

Valid in: SQL, ESQL

The Alter Group statement adds or drops user identifiers from the user list associated with a group identifier.

Syntax

The Alter Group statement has the following format:

[EXEC SQL] ALTER GROUP group_id {, group_id}

ADD USERS (user_id {, user_id}) | DROP USERS (user_id {, user_id}) | DROP ALL

• ALTER GROUP group_id {, group_id}

  Modifies the list of users associated with a group identifier (group_id). Individual users can be added or dropped, or the entire list can be dropped. An add and a drop operation cannot be performed in the same alter group statement.

  The group_id must be an existing group identifier. If a group_id that does not exist is specified, the DBMS Server issues a warning but continues processing any valid group_ids in the list. If a specific user_id occurs more than once in the user list, additional occurrences of the specified user_id are ignored. No errors are issued.

• ADD USERS (user_id {, user_id})

  Adds the specified users to the user list associated with the group_ids. The user_ids must exist when they are added to a group. If a specified user is not defined in the installation, the DBMS Server issues an error but processes the remaining user identifiers. If any of the specified users are already part of the group user list, the DBMS Server returns a warning but adds any other valid specified users to the list.

• DROP USERS (user_id {, user_id}) | DROP ALL

  Removes the specified users from the user list of the group identifier. If any of the specified users are not in the group's user list, the DBMS Server returns a warning but does not abort the statement, and any other valid specified users are dropped. A user cannot be dropped from a group if that group is the default group of the user. (Use the alter user statement to change a user's default group.)

  The drop all clause removes all users from the group's user list. A group cannot be dropped if it has any members in its user list. If any member of the specified group has that group as its default group, drop all results in an error. Use the alter user statement to change the user's default group before attempting to drop all.

  If a user is dropped from a group in a session that is associated with that group, the user retains the privileges of the group until the session terminates.

Embedded Usage

You cannot use host language variables in an embedded Alter Group statement.

Permissions

You must have maintain_users privileges and be working in a session connected with the iidbdb database.

Locking

The Alter Group statement locks pages in the iiusergroup catalog in the iidbdb. This can cause sessions attempting to connect to the server to be suspended until the alter group statement is completed.

Related Statements

Create Group

Drop Group

Examples: Alter Group

The following examples add and drop user identifiers from the user list associated with a group identifier:

1. Add users to the group, sales_clerks.

   exec sql alter group sales_clerks
   add users (dannyh, helent);

2. Drop three users from the group, tel_sales.

   exec sql alter group tel_sales
   drop users (harryk, joanb, elainet);

3. In an application, drop all users from the group, researchers.

   exec sql alter group researchers drop all;