SET SESSION AUTHORIZATION

The SET SESSION AUTHORIZATION statement changes the value of the SESSION_USER and USER special registers. It also changes the name of the user profile associated with the current thread.

Invocation

This statement can be embedded within an application program or issued interactively. It is an executable statement that can be dynamically prepared. It must not be specified in REXX.

SET SESSION AUTHORIZATION is not allowed in an SQL trigger, SQL function, or SQL procedure.

Authorization

If the authorization name specified on the statement is different than the value in the SYSTEM_USER special register, the privileges held by the authorization ID of the statement must include the system authority of *ALLOBJ.

No authorization is required to execute this statement if the authorization name specified on the statement is the same as the SYSTEM_USER special register.

If a global variable is referenced in the statement, the privileges held by the authorization ID of the statement must include at least one of the following:

  • For the global variable identified in the statement,
    • The READ privilege on the global variable, and
    • The system authority *EXECUTE on the library containing the global variable
  • Start of changeDatabase administrator authorityEnd of change

Syntax

Read syntax diagramSkip visual syntax diagramSET SESSION AUTHORIZATIONSET SESSION_USER␠=␠authorization-nameSESSION_USERUSERCURRENT USERCURRENT_USERSYSTEM_USERvariablestring-constant

Description

authorization-name
Identifies an authorization ID that is to be used as the new value for the SESSION_USER special register and the runtime authorization ID.

The authorization ID must be a valid user profile or group user profile that exists at the current server. Several system user profiles that do not have a user profile handle may not be used. For more information, see the Get Profile Handle API.

Start of changeCURRENT USEREnd of change
Start of changeThe SESSION_USER special register and the runtime authorization ID are set to the CURRENT USER special register.End of change
SESSION_USER or USER
The SESSION_USER special register and the runtime authorization ID are set to the USER special register.
SYSTEM_USER
The SESSION_USER special register and the runtime authorization ID are set to the SYSTEM_USER special register.
variable
A variable which contains an authorization ID name. It can be a global variable if it is qualified with schema name.

The variable:

  • Must be a character-string variable.
  • If variable has an associated indicator variable, the value of that indicator variable must not indicate a null value
  • Must include an authorization ID that is left justified and must conform to the rules for forming an ordinary or delimited identifier.
  • Must be padded on the right with blanks.
  • Must not be the null value.
  • Start of changeMust not be the keyword USER, SESSION_USER, SYSTEM_USER, or CURRENT_USER.End of change
string-constant
A character constant with an authorization ID.

Notes

Other effects of SET SESSION AUTHORIZATION: SET SESSION AUTHORIZATION causes the following to occur:

  • All cursors that were opened during the unit of work are closed.
  • All LOB locators are freed.
  • All locks acquired under this unit of work's commitment definition are released.
  • All prepared statements are destroyed.
  • All SQL descriptor areas are deallocated.
  • All procedure result sets are cleared.
  • The encryption password is reset.
  • All open native database files and Integrated File System (IFS) files are closed, including sockets, NTC sessions, and memory maps.

Other resources are preserved when SET SESSION AUTHORIZATION is executed, including global variables and declared temporary tables. It is recommended that all declared temporary tables be dropped or cleared and global variables be cleared before executing the SET SESSION AUTHORIZATION statement.

SET SESSION AUTHORIZATION restrictions: This statement can only be issued as the first statement that results in work that might be backed out during the unit of work. The following executable statements may be issued prior to executing SET SESSION AUTHORIZATION:

  • All SQL transaction statements
  • All SQL connection statements
  • All SQL session statements
  • GET DIAGNOSTICS

SET SESSION AUTHORIZATION is not allowed if any connections other than the connection to the current server exist, including any local connections to a non-default activation group.

SET SESSION AUTHORIZATION is not allowed if any held cursors are open or any held locators exist.

SET SESSION AUTHORIZATION scope: The scope of the SET SESSION AUTHORIZATION is the current thread. Other threads in the application process are unaffected.

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