SET SESSION AUTHORIZATION statement

Syntax

1  SET SESSION AUTHORIZATION
TO 
2.1! 'user_identifier'
2.1 user_ID_variable
1?  USING
2.1 password
2.1 auth_variable

Usage

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.

SET SESSION AUTHORIZATION TO 'cathl';
GRANT ALL ON customer TO 'mary';
SET SESSION AUTHORIZATION 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);