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
.-=-.
>>-SET--+-SESSION AUTHORIZATION-+--+---+------------------------>
'-SESSION_USER----------'
>--+-authorization-name-+--+----------------------+------------><
+-USER---------------+ '-ALLOW ADMINISTRATION-'
+-CURRENT_USER-------+
+-SYSTEM_USER--------+
+-host-variable------+
'-string-constant----'
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