SET SESSION AUTHORIZATION statement

The SET SESSION AUTHORIZATION statement changes the value of the SESSION_USER special register.

The statement is not under transaction control. The SET SESSION AUTHORIZATION statement is intended to provide support for a single user assuming different authorization IDs on the same connection, and should not be used for scenarios in which different users reuse the same connection, commonly referred to as connection pooling.

Invocation

The statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include SETSESSIONUSER on the authorization ID value to which the special register is being set.

Syntax

Read syntax diagramSkip visual syntax diagramSET SESSION AUTHORIZATIONSESSION_USER =authorization-nameUSERCURRENT_USERSYSTEM_USERhost-variablestring-constantALLOW ADMINISTRATION

Description

authorization-name
Specifies the authorization ID that is to be used as the new value for the SESSION_USER special register.
USER
The value in the USER special register.
CURRENT_USER
The value in the CURRENT USER special register.
SYSTEM_USER
The value in the SYSTEM_USER special register.
host-variable
A variable of type CHAR or VARCHAR. The length of the contents of host-variable must not exceed 128 bytes (SQLSTATE 28000). It cannot be set to null. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 28000).

The characters of host-variable must be left-aligned. When specifying authorization-name with a host variable, all characters must be specified in uppercase, because there is no conversion to uppercase characters.

string-constant
A character string constant with a maximum length of 128 bytes.
ALLOW ADMINISTRATION
Specifies that SQL schema statements can be specified before this statement in the same unit of work.

Rules

  • The value specified for the SESSION_USER special register must conform to the rules for an authorization ID of type USER (SQLSTATE 42602).
  • The OWNER bind option specifies the authorization ID that is to be used for static SQL statements.
  • This statement can only be issued as the first statement (other than a SET special register statement) in a new unit of work without any open WITH HOLD cursors (SQLSTATE 25001). This restriction includes any PREPARE request for a statement other than a SET special register statement.
  • The value of the SESSION_USER special register is used as the authorization ID for all dynamic SQL statements in a package bound with the DYNAMICRULES(RUN) bind option. (This includes INVOKERUN and DEFINERUN when the package is not used by a routine). If a package is using owner, invoker, or definer authorization based on the DYNAMICRULES option, this statement has no effect on dynamic SQL statements issued from within that package.

Notes

  • The SET SESSION AUTHORIZATION statement lets you change the session authorization ID. The session authorization ID represents the current user of the connection and is the authorization ID that the database manager considers for all authorization checking relative to dynamic SQL within a DYNAMICRULES run package. The SESSION_USER special register can be used to see the current value of this session authorization ID.
  • The initial value of the SESSION_USER special register for a new connection is the same as the value of the SYSTEM_USER special register.
  • The group information for the session authorization ID specified in this statement is acquired at the time of statement execution.
  • Setting the SESSION_USER special register does not effect either the CURRENT SCHEMA or the CURRENT PATH special register.
  • If any error occurs during the setting of the SESSION_USER special register, the register reverts to its previous value.
  • This statement should not be used to allow multiple, different users to reuse the same connection, because each user will inherit the ability to change the value of the SESSION_USER special register that the original connection owner had. This statement is dependent upon the value of SYSTEM_USER for privileges checking, and the initial connection authorization ID is not changed by the SET SESSION AUTHORIZATION statement. Moreover, the following behaviors impacting connection reuse are not addressed by this statement:
    • The CONNECT privilege is not checked for the new authorization ID
    • The content of any updatable special register is not reset; in particular, the content of the ENCRYPTION PASSWORD special register is not modified and is available to the new authorization ID for encryption or decryption
    • The content of any declared global temporary table is not affected, and is accessible to the new authorization ID
    • Any existing links to remote servers are not reset
  • If the ALLOW ADMINISTRATION clause is specified, the following types of statements or operations can precede the SET SESSION AUTHORIZATION statement:
    • Data definition language (DDL), including the definition of savepoints and the declaration of global temporary tables, but not including SET INTEGRITY
    • GRANT and REVOKE statements
    • LOCK TABLE statement
    • COMMIT and ROLLBACK statements
    • SET of special registers
    • SET of global variables

Examples

  • Example 1: The following statement sets the SESSION_USER special register. 
       SET SESSION_USER = RAJIV
  • Example 2: Set the session authorization ID (the SESSION_USER special register) to be the value of the system authorization ID, which is the ID that established the connection on which the statement has been issued. 
       SET SESSION AUTHORIZATION SYSTEM_USER