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.

Before you begin

  • 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.

About this task

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.

If you are switching to a user based on a userid and password, follow steps 1 and 2 in the procedure below. If you are switching to a user based on an access token (available as an option starting from Db2® version 11.5.4), follow step 3.

Procedure

  1. 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.
  2. Optional: (This step is 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.
  3. Optional:
    Note: This feature described in this step is available starting from Db2 version 11.5.4.
    Call the SQLSetConnectAttr function to set the SQL_ATTR_TRUSTED_CONTEXT_ACCESSTOKEN attribute. Set it to the access token for the new authorization ID. Do not call SQLSetConnectAttr with the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute as the access token encapsulates both the new users identity and authentication credentials. 
    rc = SQLSetConnectAttr(
   conn,
   SQL_ATTR_TRUSTED_CONTEXT_ACCESSTOKEN, accesstoken, SQL_NTS
);
//Check for errors
    Table 3. Errors indicating failure to set a new authorization ID via an access token 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 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.
  4. 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 before 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 4. The errors in Table 4 indicate that the user switch failed.
    Table 4. 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.

What to do next

Note:
  1. Important: Switching users without supplying a password or access token bypasses the database server’s authentication. Your application must not allow a switch to an authorization ID without a password or access token unless that application has already validated and authenticated that authorization ID. To do otherwise creates a security vulnerability.
  2. 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 the original explicitly connection was established with TOKEN authentication a NULL value will only result in the system authorization ID being sent to the server to switch. This is only appropriate if the system authorization ID is allowed to switch to without authentication.
  3. Specifying a NULL value for the SQL_ATTR_TRUSTED_CONTEXT_ACCESSTOKEN attribute is equivalent to specifying the trusted context system authorization ID (the user id used when the explicit trusted connection was created) via the original access token. The access token will be authenticated by the server. Use SQL_ATTR_TRUSTED_CONTEXT_USERID if you wish to switch to the trusted context system authorization ID with a NULL value if you want to switch without authentication.
  4. When you successfully set the value of the SQL_ATTR_TRUSTED_CONTEXT_USERID or SQL_ATTR_TRUSTED_CONTEXT_ACCESSTOKEN 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.
  5. 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 does not require authentication on a switch user for that authorization ID. This results in unnecessary processing time. 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.
  6. If the SQL_ATTR_TRUSTED_CONTEXT_ACCESSTOKEN attribute is set, the access token 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 the authorization ID identified by the access token. This results in unnecessary processing time.
  7. The type of token specified for the SQL_ATTR_TRUSTED_CONTEXT_ACCESSTOKEN attribute must be the same as the type of access token used to establish the original explicit trusted connection for the system authorization ID.
  8. Do not specify both the SQL_ATTR_TRUSTED_CONTEXT_USERID and SQL_ATTR_TRUSTED_CONTEXT_ACCESSTOKEN attributes at the same time, or it will result in two switch users, as these attributes are independent.