Rules for switching the user ID on an explicit trusted connection

On an explicit trusted connection, you can switch the user ID of the connection to a different user ID. Certain rules apply.

Trusted contexts vs trusted connections

A trusted context is a database object that defines a trust relationship for a connection between the database and an external entity such as an application server. With a trusted connection, the connection initiator can get additional capabilities that might not be available outside the scope of the trusted connection. These additional capabilities vary, depending on whether the trusted connection was explicitly requested, or implicitly obtained. For more information, see Trusted contexts and trusted connections.

Rules to follow for a successful user ID switch on an explicit trusted connection

Failure to follow the rules listed in Table 1 can result in errors being returned when switching the user ID on an explicit trusted connection:
Table 1. Rules when switching user IDs on an explicit trusted connection
Rule Outcome if rule not followed Error message returned
Initiate the switch request from an explicit trusted connection. If the switch request is not made from an explicit trusted connection, and the switch request is sent to the server for processing, the connection is shut down and an error message is returned. SQLSTATE 08001, SQLCODE -30082 with reason code 41
Ensure that the transaction is active when the requested switch is made. If the switch request is not made when the transaction is active, then the transaction is rolled back. The switch request is then sent to the server for processing, the connection is put into an unconnected state, and an error message is returned. SQLSTATE 58009, SQLCODE -30020
Do not initiate the switch request from within a stored procedure. If the switch request is made from within a stored procedure, an error message is returned, indicating this is an illegal operation in this environment. The connection state is maintained and the connection is not placed into an unconnected state. Subsequent requests can be processed. SQLCODE -30090, reason code 29
Do not initiate the switch request when attaching to an instance. If the switch request is delivered to the server on an instance attach, rather than a database connection, the attachment is shut down and an error message is returned. SQLCODE -30005
Use an ID that is authorized on the trusted connection. If the switch request is made with an authorization ID that is not allowed on the trusted connection, an error is returned and the connection is put in an unconnected state. SQLSTATE 42517, SQLCODE -20361
Use an approved authentication token, if needed. If the switch request is made with an authorization ID that is allowed on the trusted connection WITH AUTHENTICATION, but the appropriate authentication token is not provided, an error is returned, and the connection is put in an unconnected state. SQLSTATE 42517, SQLCODE -20361
Ensure the trusted context object is enabled for your trusted connection. If the trusted context object associated with the trusted connection is disabled, and a switch request for that trusted connection is made, an error is returned and the connection is put in an unconnected state. 1 SQLSTATE 42517, SQLCODE -20361
Do not change the system authorization ID of the trusted context object your trusted connection. The trusted context object that is associated with your trusted connection has a system authorization ID value. Changing this value and then requesting user ID switch on your trusted connection returns an error, and the connection is put in an unconnected state. SQLSTATE 42517, SQLCODE -20361
Do not drop the trusted context object that is associated with your trusted connection. If the trusted context object associated with the trusted connection is dropped, and a switch request for that trusted connection is made, an error is returned and the connection is put in an unconnected state. 2 SQLSTATE 42517, SQLCODE -20361
Ensure that any user that is allowed to use the trusted connection holds the CONNECT privilege on the target Db2® database. If the switch request is made with a user ID allowed on the trusted connection, but that user ID does not hold CONNECT privilege on the database, the connection is put in an unconnected state and an error message is returned. SQLSTATE 08004, SQLCODE -1060
Include the trusted context system authorization ID in the WITH USE FOR clause of the trusted connection. If the trusted context system authorization ID appears in the WITH USE FOR clause, the Db2 database system honors the authentication setting for the system authorization ID, and the switch user request switches back to the system authorization ID. If the trusted context system authorization ID does not appear in the WITH USE FOR clause, then a switch user request to switch back to the system authorization ID is always allowed even without authentication. NA

Usage notes

Consider the following when attempting to request a user ID switch on an explicit trusted connection:
  • When a connection is put in an unconnected state, only a small set of requests are accepted:
    • A switch user request
    • A COMMIT or ROLLBACK statement
    • A DISCONNECT, CONNECT RESET or CONNECT request
    Any other request of an unconnected connection returns the following error (SQLCODE -900):
    The application state is in error. A database connection does not exist
  • When the user ID on the trusted connection is switched to a new user ID, all traces of the connection environment under the old user are removed from the system. This switch results in a connection environment that is identical to a new connection environment. For example, switching to a new user ID has the following results:
    • The connection is associated with the default SYSTEM tenant, and any prior association with a user-defined tenant is lost.
    • Any temporary tables are dropped.
    • Any WITH HOLD cursors are closed.
  • Java trusted connections do not have an unconnected state. If the switch user operation fails, Java throws an exception and the connection is disconnected.
1 In this case, the only switch user request that is accepted is one that specifies the user ID that established the trusted connection or the NULL user ID. If a switch to the user ID that established the trusted connection is made, this user ID does not inherit any trusted context role. Neither the trusted context default role nor the trusted context user-specific role.
2 In this case, the only switch user request that is accepted is one that specifies the user ID that established the trusted connection or the NULL user ID. If a switch to the user ID that established the trusted connection is made, this user ID does not inherit any trusted context role (neither the trusted context default role nor the trusted context user-specific role).