DB2 10.5 for Linux, UNIX, and Windows

Creating and granting membership in roles

The security administrator holds the authority to create, drop, grant, revoke, and comment on a role. The security administrator uses the GRANT (Role) statement to grant membership in a role to an authorization ID and uses the REVOKE (Role) statement to revoke membership in a role from an authorization ID.

The security administrator can delegate the management of membership in a role to an authorization ID by granting the authorization ID membership in the role with the WITH ADMIN OPTION. The WITH ADMIN OPTION clause of the GRANT (Role) statement gives another user the ability to:

Grant roles to others.
Revoke roles from others.
Comment on the role.

The WITH ADMIN OPTION clause does not give the ability to:

Drop the role.
Revoke the WITH ADMIN OPTION for a role from an authorization ID.
Grant WITH ADMIN OPTION to someone else (if you do not hold SECADM authority).

After the security administrator has created a role, the database administrator can use the GRANT statement to assign authorities and privileges to the role. All DB2® privileges and authorities that can be granted within a database can be granted to a role. Instance level authorities, such as SYSADM authority, cannot be assigned to a role.

The security administrator, or any user who the security administrator has granted membership in a role with WITH ADMIN OPTION can use the GRANT (Role) statement to grant membership in that role to other users, groups, PUBLIC or roles. A user may have been granted membership in a role with WITH ADMIN OPTION either directly, or indirectly through PUBLIC, a group or a role.

All the roles assigned to a user are enabled when that user establishes a session. All the privileges and authorities associated with a user's roles are taken into account when the DB2 database system checks for authorization. Some database systems use the SET ROLE statement to activate a particular role. The DB2 database system supports SET ROLE to provide compatibility with other products using the SET ROLE statement. In a DB2 database system, the SET ROLE statement checks whether the session user is a member of the role and returns an error if they are not.

To revoke a user's membership in a role, the security administrator, or a user who holds WITH ADMIN OPTION privilege on the role, uses the REVOKE (Role) statement.

Example

A role has a certain set of privileges and a user who is granted membership in this role inherits those privileges. This inheritance of privileges eliminates managing individual privileges when reassigning the privileges of one user to another user. The only operations required when using roles is to revoke membership in the role from one user and grant membership in the role to the other user.

For example, the employees BOB and ALICE, working in department DEV, have the privilege to SELECT on the tables SERVER, CLIENT and TOOLS. One day, management decides to move them to a new department, QA, and the database administrator has to revoke their privilege to select on tables SERVER, CLIENT and TOOLS. Department DEV later hires a new employee, TOM, and the database administrator has to grant SELECT privilege on tables SERVER, CLIENT and TOOLS to TOM.

When using roles, the following steps occur:

The security administrator creates a role, DEVELOPER:
```
CREATE ROLE DEVELOPER
```

The database administrator (who holds DBADM authority) grants SELECT on tables SERVER, CLIENT, and TOOLS to role DEVELOPER:

GRANT  SELECT ON TABLE SERVER TO ROLE DEVELOPER
GRANT  SELECT ON TABLE CLIENT TO ROLE DEVELOPER
GRANT  SELECT ON TABLE TOOLS TO ROLE DEVELOPER

The security administrator grants the role DEVELOPER to the users in department DEV, BOB and ALICE:
```
GRANT ROLE DEVELOPER TO USER BOB, USER ALICE
```
When BOB and ALICE leave department DEV, the security administrator revokes the role DEVELOPER from users BOB and ALICE:
```
REVOKE ROLE DEVELOPER FROM USER BOB, USER ALICE
```
When TOM is hired in department DEV, the security administrator grants the role DEVELOPER to user TOM:
```
GRANT ROLE DEVELOPER TO USER TOM
```