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.
This example shows how you can replace groups by using roles.
Group | Users belonging to this group |
---|---|
DEVELOPER_G | BOB |
TESTER_G | ALICE, TOM |
SALES_G | ALICE, BOB |
CREATE ROLE DEVELOPER
CREATE ROLE TESTER
CREATE ROLE SALES
GRANT ROLE DEVELOPER TO USER BOB
GRANT ROLE TESTER TO USER ALICE, USER TOM
GRANT ROLE SALES TO USER BOB, USER ALICE
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.This example shows that user BOB can successfully create a trigger, TRG1, when he holds the necessary privilege through the role DEVELOPER.
CREATE TABLE WORKITEM (x int)
GRANT ALTER ON ALICE.WORKITEM TO ROLE DEVELOPER
CREATE TRIGGER TRG1 AFTER DELETE ON ALICE.WORKITEM
FOR EACH STATEMENT MODE DB2SQL INSERT INTO ALICE.WORKITEM VALUES (1)