SET ROLE statement

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

Read syntax diagramSkip visual syntax diagramSET 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