Users and groups inherit all inheritable permissions of the groups they are members of. The permission to grant the same permission to other users (using the GRANT....WITH GRANT OPTION statement) is not inherited by the members of the group.

Members of a group can inherit only the following permissions for the group they belong to:

• ALL
• ALTER
• DELETE
• INSERT
• REFERENCES
• SELECT
• UPDATE

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

• READCLIENTFILE
• READFILE
• WRITECLIENTFILE

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);