REVOKE (SETSESSIONUSER privilege) statement

This form of the REVOKE statement revokes one or more SETSESSIONUSER privileges from one or more authorization IDs.

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

The privileges held by the authorization ID of the statement must include SECADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE SETSESSIONUSER ON ,USERsession-authorization-namePUBLIC FROM,USERGROUPauthorization-name

Description

SETSESSIONUSER ON
Revokes the privilege to assume the identity of a new authorization ID.
USER session-authorization-name
Specifies the authorization ID that the authorization-name is able to assume, using the SET SESSION AUTHORIZATION statement. The session-authorization-name must identify a user that the authorization-name can assume, not a group (SQLSTATE 42504).
PUBLIC
Specifies that all privileges to set the session authorization will be revoked.
FROM
Specifies from whom the privilege is revoked.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group name.
authorization-name
Lists the authorization IDs of one or more users or groups.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

Examples

  • Example 1: User PAUL holds the privilege to set the session authorization to WALID and therefore to execute SQL statements as user WALID. The following statement revokes that privilege. 
       REVOKE SETSESSIONUSER ON USER WALID
     FROM USER PAUL
  • Example 2: User GUYLAINE holds the privilege to set the session authorization to BOBBY, RICK, or KEVIN and therefore to execute SQL statements as BOBBY, RICK, or KEVIN. The following statement revokes the privilege to use two of those authorization IDs. After this statement executes, GUYLAINE will only be able to set the session authorization to KEVIN. 
       REVOKE SETSESSIONUSER ON USER BOBBY, USER RICK
     FROM USER GUYLAINE
  • Example 3: The group ACCTG and user WALID can set session authorization to any authorization ID. The following statement revokes that privilege from both ACCTG and WALID. 
       REVOKE SETSESSIONUSER ON PUBLIC
     FROM USER WALID, GROUP ACCTG