Switching users on a trusted connection through CLI

You can switch users on an explicit trusted connection through the command line interface (CLI). For a description of what it means to switch users using a trusted connection see the topic in the related links.

The connection must have been successfully created as an explicit trusted connection.
The explicit trusted connection must not be in a transaction.
The trusted context that allowed the explicit trusted connection to be created must be configured to allow switching to the authorization ID you are switching to.

The examples in these instructions use the C language and assume that conn is a pointer to a connected explicit trusted connection. The variable rc is assumed to have a data type of SQLRETURN. The variable newuser is assumed to be a pointer to a character string holding the authorization ID of the user you want to switch to. The variable passwd is assumed to be a pointer to a character string containing the password for that authorization ID.

Call the SQLSetConnectAttr function to set the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute. Set it to the authorization ID you want to switch to.

rc = SQLSetConnectAttr(
    conn,     SQL_ATTR_TRUSTED_CONTEXT_USERID, newuser, SQL_NTS );
//Check for errors

Be sure to check for errors and warnings, especially those listed in table Table 1.

Table 1. Errors indicating failure to set a new authorization ID when switching users
SQLCODE	Meaning
CLI0106E	The connection is not connected.
CLI0197E	The connection is not a trusted connection.
CLI0124E	There is a problem with the value provided. Check that it is not null, or not too long, for example.
CLI0196E	The connection is involved in a unit of work that prevents it from switching users. To be able to switch users the connection must not be in a transaction.

(Optional unless the trusted context that allowed this trusted connection requires a password for the authorization ID you are switching to) Call the SQLSetConnectAttr function to set the SQL_ATTR_TRUSTED_CONTEXT_PASSWORD attribute. Set it to the password for the new authorization ID.
```
rc = SQLSetConnectAttr(
    conn,     SQL_ATTR_TRUSTED_CONTEXT_PASSWORD, passwd, SQL_NTS );
//Check for errors
```
Be sure to check for errors and warnings, both those listed in table Table 1 and those listed in table Table 2.
Table 2. Errors indicating failure to set a password when switching users
SQLCODE Meaning

CLI0198E The attribute SQL_ATTR_TRUSTED_CONTEXT_USERID has not yet been set.

Table 2. Errors indicating failure to set a password when switching users
SQLCODE	Meaning
CLI0198E	The attribute SQL_ATTR_TRUSTED_CONTEXT_USERID has not yet been set.

Proceed as with a regular connection. If you are using an XA transaction manager the user switch is attempted as part of the next request, otherwise the user switch is attempted just prior to initiating the next function call that accesses the database (SQLExecDirect for example). In either case, in addition to the errors and warnings you would normally check for, be sure to check for the errors listed in Table 3. The errors in Table 3 indicate that the user switch failed.

Table 3. Errors indicating failure to switch users
SQLCODE	Meaning
SQL1046N	The trusted context that allowed this trusted connection is not configured to allow switching to the authorization ID you are trying to switch to. You will not be able to switch to that authorization ID until the trusted context is changed.
SQL30082N	The password provided is not correct for the authorization ID you are switching to.
SQL0969N with a native error of -20361	There is some database level constraint that prevent you from switching to the user.

If the user switch fails the connection will be in an unconnected state until you successfully switch to another user. You can switch users on a trusted connection in an unconnected state but cannot access the database server with it. A connection in an unconnected state will remain in that state until you successfully switch users on it.

Notes:

Important: Switching users without supplying a password bypasses the database server's authentication. Your application must not allow a switch to an authorization ID without a password unless that application has already validated and authenticated that authorization ID. To do otherwise creates a security hole.
Specifying a NULL value for the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute is equivalent to specifying the trusted context system authorization ID (the user id used when the explicit trusted connection was created).
When you successfully set the value of the SQL_ATTR_TRUSTED_CONTEXT_USERID connection attribute on an explicit trusted connection the connection is immediately reset. The result of resetting is as if a new connection were created using the original connection attributes of that connection. This reset happens even if the value you set the connection attribute to is the system authorization ID or NULL or the same value that the attribute currently holds.
If the SQL_ATTR_TRUSTED_CONTEXT_PASSWORD attribute is set, the password will be authenticated during the switch user processing, even if the trusted context that allowed the trusted connection doesn't require authentication on a switch user for that authorization ID. This results in unnecessary overhead. This rule doesn't apply to the trusted context system authorization ID. If the trusted context system authorization ID doesn't require authentication when you switch to it then it is not authenticated even if a password is provided.

Parent topic: Trusted connections through DB2 Connect

Related concepts
Trusted connections through DB2 Connect