Roles compared to groups

Privileges and authorities granted to groups are not considered when creating views, materialized query tables (MQTs), SQL routines, triggers, and packages containing static SQL. Avoid this restriction by using roles instead of groups.

Roles allow users to create database objects using their privileges acquired through roles, which are controlled by the Db2® database system. Groups and users are controlled externally from the Db2 database system, for example, by an operating system or an LDAP server.

Example of replacing the use of groups with roles

This example shows how you can replace groups by using roles.

Assume that there are three groups, DEVELOPER_G, TESTER_G and SALES_G. The users BOB, ALICE, and TOM are members of these groups, as shown in the following table:
Table 1. Example groups and users
Group Users belonging to this group
DEVELOPER_G BOB
TESTER_G ALICE, TOM
SALES_G ALICE, BOB
  1. The security administrator creates the roles DEVELOPER, TESTER, and SALES to be used instead of the groups. 
    CREATE ROLE DEVELOPER
CREATE ROLE TESTER
CREATE ROLE SALES
  2. The security administrator grants membership in these roles to users (setting the membership of users in groups was the responsibility of the system administrator):
    GRANT  ROLE DEVELOPER TO USER BOB
GRANT  ROLE TESTER TO USER ALICE, USER TOM
GRANT  ROLE SALES TO USER BOB, USER ALICE
  3. The database administrator can grant to the roles similar privileges or authorities as were held by the groups, for example: 
    GRANT privilege ON object TO ROLE DEVELOPER
    The database administrator can then revoke these privileges from the groups, as well as ask the system administrator to remove the groups from the system.

Example of creating a trigger using privileges acquired through a role

This example shows that user BOB can successfully create a trigger, TRG1, when he holds the necessary privilege through the role DEVELOPER.

  1. First, user ALICE creates the table, WORKITEM:
    CREATE TABLE WORKITEM (x int)
  2. Then, the privilege to alter ALICE's table is granted to role DEVELOPER by the database administrator. 
    GRANT ALTER ON ALICE.WORKITEM TO ROLE DEVELOPER
  3. User BOB successfully creates the trigger, TRG1, because he is a member of the role, DEVELOPER.
    CREATE TRIGGER TRG1 AFTER DELETE ON ALICE.WORKITEM 
           FOR EACH STATEMENT MODE DB2SQL INSERT INTO ALICE.WORKITEM VALUES (1)

Notes

  • Roles that are granted to groups are not considered.