A group can be thought of as a user ID with special permissions, such as the ability to have members. You grant and revoke permissions and authorities for a group in exactly the same manner as you do for users.

You can construct a hierarchy of groups where each group is a member of another group. Members, whether they be users or groups, inherit the authorities and permissions from its parent group. A user ID may belong to more than one group; the user-to-group relationship is many-to-many.

Just as with users, you can grant or revoke group permissions on a table, view, or procedure. When you do so, all members of the group inherit the change.

You can create a group without a password. This enables you to prevent users from connecting to the database using the group user ID. See Groups without passwords.

To administer authorities and permissions for a group, follow the same procedures that you do for administering permissions and authorities for users. See Managing user permissions and authorities overview.

To administer remote permissions for groups, see Granting and revoking remote permissions.

Special inheritance notes for groups

With the exception of the grant permission (GRANT ... WITH GRANT OPTION statement), users and groups inherit all permissions of the groups they are members of.

Members of a group can inherit only the following authorities set for the group they belong to.

• READCLIENTFILE
• READFILE
• WRITECLIENTFILE

Brief example

In the following example, two groups, group1 and group 2, are created. A user, bobsmith, is created and given membership in both groups. A table, table1, is created and group2 is given SELECT and INSERT permissions on the new table.

GRANT CONNECT, GROUP TO group1;
GRANT CONNECT, GROUP TO group2;
GRANT CONNECT TO bobsmith IDENTIFIED BY sql;
GRANT MEMBERSHIP IN GROUP group1 TO bobsmith;
GRANT MEMBERSHIP IN GROUP group2 TO bobsmith;

CREATE TABLE DBA.table1( column1 INT, modified_by VARCHAR(128) DEFAULT USER );
GRANT SELECT, INSERT ON DBA.table1 TO group2;

Because bobsmith is a member of group2, he inherits select and insert permissions on table1 and can insert values into it as shown below:

CONNECT USER bobsmith IDENTIFIED BY sql;
INSERT INTO DBA.table1(column1) VALUES(1);