The SET ROLE statement verifies that the authorization
ID of the session is a member of a specific role. An authorization
ID acquires membership in a role when the role is granted to the authorization
ID, or to a group or role in which the authorization ID is a member.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
None required.
Syntax
.-=-.
>>-SET ROLE--+---+--role-name----------------------------------><
Description
- role-name
- Specifies a role in whose membership the authorization ID of the
session is to be verified. The role-name must identify
an existing role at the current server (SQLSTATE 42704). If the authorization
ID of the session is not a member of role-name,
an error is returned (SQLSTATE 42501).
Notes
- All roles that have been granted to an authorization ID are used
for authorization checking. The SET ROLE statement does not affect
which roles are used for this authorization checking. Use the GRANT
ROLE and REVOKE ROLE statements to change the roles in which an authorization
ID has membership.
Examples
- Example 1: User WALID has been granted the role EDITOR,
but not the role AUTHOR. Verify that WALID is a member of the EDITOR
role.
SET ROLE EDITOR
- Example 2: Verify that WALID is not a member of the AUTHOR
role. The following statement returns an error (SQLSTATE 42501).
SET ROLE AUTHOR