The SET SESSION AUTHORIZATION statement lets you change the user name under which database operations are performed in the current session.


2.1! 'user_identifier'
2.1 user_ID_variable
2.1 password
2.1 auth_variable
Element Description Restrictions Syntax
auth_variable Host variable that holds the valid password for the login name specified in user_identifier or user_ID_variable. Variable must be a fixed-length character data type. Its value has the same restrictions as password. Must conform to language-specific rules for variable names.
password Quoted string that is the password of the specified user. Must be the password of that user, and no more than 32 bytes Quoted String
user_identifier Quoted string that is a valid login name for the application. The quotation mark delimiters preserve the lettercase. Authorization identifier of no more than 32 bytes Quoted String
user_ID_variable The name of an ESQL/C host variable that holds the value of a user identifier. Variable must be a fixed-length character data type. Its value has the same restrictions as user_identifier. Must conform to language-specific rules for variable names.


This statement allows you to assume the identity of another user, including the discretionary access control (DAC) and label-based access control (LBAC) credentials. You can also use this statement in an API that supports HCL OneDB™ trusted contexts to switch the user ID on a trusted connection.

Both the DBA and SETSESSIONAUTH access privilege are required to execute this statement. Unless when you start the session you already hold the SETSESSIONAUTH privilege for PUBLIC (or for the user whose name you specify in the SET SESSION AUTHORIZATION statement), and you also hold the DBA privilege, this statement fails with an error.

If the database server has been converted from a legacy version that did not support label-based access control, users who held the DBA privilege are automatically granted the SETSESSIONAUTH access privilege for PUBLIC in the migration process. If the database server has been initialized as a version that supports LBAC security policies, users who hold the DBSECADM role can grant the SETSESSIONAUTH privilege to other users. Because the security credentials of each user determine what data rows can be accessed in protected tables, the DBSECADM should exercise care in granting the SETSESSIONAUTH privilege and in specifying its scope.

The new identity remains in effect in the current database until you execute SET SESSION AUTHORIZATION again, or until you close the current database. When you use this statement, the specified user must have the Connect privilege on the current database. In addition, the DBA cannot set the new authorization identifier to the PUBLIC group, nor to any existing role in the current database.

Setting a session to another user causes a change in a user name in the current active database server. The specified user, as far as this database server process is concerned, is completely dispossessed of any privileges while accessing the database server through some administrative utility. Additionally, the new session user is not able to initiate any administrative operation (execute a utility, for example) by virtue of the acquired identity.

After the SET SESSION AUTHORIZATION statement successfully executes, any role enabled by a previous user is relinquished. You must use the SET ROLE statement if you wish to assume a role that has been granted to the specified user. The database server does not enable the default role of user automatically.

After SET SESSION AUTHORIZATION successfully executes, the database server puts any owner-privileged UDRs that the DBA created while using the new authorization identifier in RESTRICTED mode, which can affect access privileges during operations of the UDR on objects in remote databases. For more information on RESTRICTED mode, see the sysprocedures system catalog table in the HCL OneDB Guide to SQL: Reference.

When you assume the identity of another user by executing the SET SESSION AUTHORIZATION statement, you can perform operations in the current database only. You cannot perform an operation on a database object outside the current database, such as a remote table. In addition, you cannot execute a DROP DATABASE or RENAME DATABASE statement, even if the database is owned by the real user or by the effective user.

You can use this statement either to obtain access to the data directly or to grant the database-level or table-level privileges needed for the database operation to proceed. The following example shows how to use the SET SESSION AUTHORIZATION statement to obtain table-level privileges:
GRANT ALL ON customer TO 'mary';
UPDATE customer SET fname = 'Carl' WHERE lname = 'Pauli';

If you enclose user in quotation marks, the name is case sensitive and is stored exactly as you typed it. In an ANSI-compliant database, if you do not use quotation marks as delimiters, the authorization identifier is stored in uppercase letters, unless the ANSIOWNER environment variable is set to prevent the conversion of lowercase letters to uppercase.

The following Open Database Connectivity (ODBC) API example enables user ID switching on a trusted connection with an authentication requirement:

SQLExecDirect(hstmt,"SET SESSION AUTHORIZATION TO 'zurbie' USING 'pass01'",SQL_NTS);
In the function call above,
  • 'zurbie' specifies the authorization identifier for subsequent operations in this session
  • pass01 must be the current password of user zurbie.

Except in a non-hostile environment, 'pass01' is not a recommended example of a login password, because in some locales it might be easy to guess.