SETSESSIONAUTH Clause

The REVOKE SETSESSIONAUTH statement revokes the SETSESSIONAUTH privilege from one or more users or roles. The SETSESSIONAUTH privilege allows users who also hold the DBA privilege to use the SET SESSION AUTHORIZATION statement to set the session authorization to one of a set of specified users.

Syntax

SETSESSIONAUTH Clause

1   SETSESSIONAUTH ON
2.1   PUBLIC
2.1 + ,?  USER
2.2.1 user
2.2.1  " user "   FROM
2.1+ ,
2.1 ?  USER
2.2.1 user
2.2.1  " user "
2.1 ?  ROLE
2.2.1 role
2.2.1  " role "
Element Description Restrictions Syntax
role Role from which the privilege is to be revoked Must be the authorization identifier of a role Owner name
user After the FROM keyword, a user from whom the privilege is to be revoked. After the ON keyword, a user whose identity the grantee can specify in the SET AUTHORIZATION statement. Must be the authorization identifier of a user Owner name

Only a user who holds the DBSECADM role can revoke the SETSESSIONAUTH privilege.

The user or PUBLIC specification that follows the ON keyword specifies whose identity the grantee of the SETSESSIONAUTH privilege is no longer able to assume while using the SET SESSION AUTHORIZATION statement. This can be a user or PUBLIC, but not a role. If PUBLIC is specified, then the grantee of the privilege no longer has the ability to assume the identity of an arbitrary database user.

The USER and ROLE keywords that can follow the FROM keyword are optional. Neither the user nor the role can be the holder of the DBSECADM role who issues the REVOKE SETSESSIONAUTH statement. The FROM clause cannot specify PUBLIC.

Examples of the REVOKE SETSESSIONAUTH statement

Suppose that GRANT SETSESSIONAUTH statements issued by a user who holds the DBSECADM role had made the following assignments of the SETSESSIONAUTH privilege in the current database:
GRANT SETSESSIONAUTH ON lynette, manoj TO sam; 
GRANT SETSESSIONAUTH ON PUBLIC TO lynette;
  • The first example above enables user sam to set the session authorization to users lynette and manoj.
  • The second example enables user lynette to set the session authorization to the PUBLIC group, or to set it to the authorization identifier of any user (but not to any role).
Both the SETSESSIONAUTH privilege and the DBA privilege are required to execute the SET AUTHORIZATION statement.
If user sam holds the DNA and SETSESSIONAUTH privileges, he could issue the following statement:
SET SESSION AUTHORIZATION TO 'lynette';
Now sam has assumed the identity of user lynette, including the discretionary access control (DAC) and label-based access control (LBAC) credentials of user lynette. User sam can also use this SET SESSION AUTHORIZATION statement in an API that supports HCL OneDB™ trusted contexts to switch the user ID on a trusted connection. Because a previous example enabled user lynette to set the session authorization to any user, that is now a capability of sam during this session, where sam has assumed the identity of lynette.
The following example, however, revokes from user sam the ability to set the session authorization to users lynette and manoj: 
REVOKE SETSESSIONAUTH ON lynette, manoj FROM sam;
Now the previous SET SESSION AUTHORIZATION example would fail, because this REVOKE SETSESSIONAUTH statement excludes lynette and manoj from the authorization identifiers that user sam can assume.
The next example revokes from user lynette the ability to set the session authorization to PUBLIC: 
REVOKE SETSESSIONAUTH ON PUBLIC FROM USER lynette;
The PUBLIC scope of the SETSESSIONAUTH privilege that this example revokes had enabled user lynette (and user sam under the login name of lynette in the previous SET SESSION AUTHORIZATION example) to assume the access privileges and security credentials of any user specified by lynette in the SET SESSION AUTHORIZATION statement.

Delimiting user and role identifiers

If you enclose user or role in double ( " ) or single ( ' ) quotation marks, the identifier is case sensitive, and the database server stores it in the system catalog exactly as you enter it in the REVOKE statement. The following REVOKE statement has the same effect as the previous example for user lynette whose authorization identifier includes no uppercase characters:
REVOKE SETSESSIONAUTH ON PUBLIC FROM USER "lynette";
Suppose that another user also holds the SETSESSIONAUTH privilege on the PUBLIC group, and that her authorization identifier is Lynette, with an initial uppercase character. Because her authorization identifier does not match the case sensitive FROM USER "lynette" specification, this example has no effect on her SETSESSIONAUTH privilege on the identifier of any user in the PUBLIC group. The undelimited FROM lynette previous example, however, revokes SETSESSIONAUTH from both lynette and Lynette.