Establishing an explicit trusted connection and switching the user ID

You can establish an explicit trusted connection by making a request within an application when a connection to a Db2® database is established. The security administrator must have previously defined a trusted context, using the CREATE TRUSTED CONTEXT statement, with attributes matching those of the connection you are establishing (see Step 1, later).

Before you begin

The API you use to request an explicit trusted connection when you establish a connection depends on the type of application you are using (see the table in Step 2).

After you have established an explicit trusted connection, the application can switch the user ID of the connection to a different user ID using the appropriate API for the type of application (see the table in Step 3).

Procedure

  1. The security administrator defines a trusted context in the server by using the CREATE TRUSTED CONTEXT statement.
    For example:
    CREATE TRUSTED CONTEXT MYTCX  
           BASED UPON CONNECTION USING SYSTEM AUTHID NEWTON  
           ATTRIBUTES (ADDRESS   '192.0.2.1')  
           WITH USE FOR PUBLIC WITHOUT AUTHENTICATION  
           ENABLE
  2. To establish a trusted connection, use one of the following APIs in your application:
    Option Description
    Application API
    CLI/ODBC SQLConnect, SQLSetConnectAttr
    XA CLI/ODBC Xa_open
    JAVA getDB2TrustedPooledConnection, getDB2TrustedXAConnection
  3. To switch to a different user, with or without authentication, use one of the following APIs in your application:
    Option Description
    Application API
    CLI/ODBC SQLSetConnectAttr
    XA CLI/ODBC SQLSetConnectAttr
    JAVA getDB2Connection, reuseDB2Connection
    .NET DB2Connection.ConnectionString keywords: TrustedContextSystemUserID and TrustedContextSystemPassword
    The switching can be done either with or without authenticating the new user ID, depending on the definition of the trusted context object associated with the explicit trusted connection. For example, suppose that the security administrator creates the following trusted context object:
    CREATE TRUSTED CONTEXT CTX1 
    	BASED UPON CONNECTION USING SYSTEM AUTHID USER1
    	ATTRIBUTES (ADDRESS   '192.0.2.1')      
      WITH USE FOR USER2 WITH AUTHENTICATION,   
                   USER3 WITHOUT AUTHENTICATION  
      ENABLE  
    Further, suppose that an explicit trusted connection is established. A request to switch the user ID on the trusted connection to USER3 without providing authentication information is allowed because USER3 is defined as a user of trusted context CTX1 for whom authentication is not required. However, a request to switch the user ID on the trusted connection to USER2 without providing authentication information will fail because USER2 is defined as a user of trusted context CTX1 for whom authentication information must be provided.

Example of establishing an explicit trusted connection and switching the user

In the following example, a middle-tier server needs to issue some database requests on behalf of an end-user, but does not have access to the end-user's credentials to establish a database connection on behalf of that end-user.

You can create a trusted context object on the database server that allows the middle-tier server to establish an explicit trusted connection to the database. After establishing an explicit trusted connection, the middle-tier server can switch the current user ID of the connection to a new user ID without the need to authenticate the new user ID at the database server. The following CLI code snippet demonstrates how to establish a trusted connection using the trusted context, MYTCX, defined in Step 1, earlier, and how to switch the user on the trusted connection without authentication.
int main(int argc, char *argv[])
{
	SQLHANDLE henv;		       /* environment handle */
	SQLHANDLE hdbc1;		     /* connection handle */
	char origUserid[10] = "newton";
  char password[10] = "test";
  	char switchUserid[10] = "zurbie";
	char dbName[10] = "testdb";
	 
// Allocate the handles 
SQLAllocHandle( SQL_HANDLE_ENV, &henv );
SQLAllocHandle( SQL_HANDLE_DBC, &hdbc1 );

// Set the trusted connection attribute
SQLSetConnectAttr( hdbc1, SQL_ATTR_USE_TRUSTED_CONTEXT, 
SQL_TRUE, SQL_IS_INTEGER );

// Establish a trusted connection
SQLConnect( hdbc1, dbName, SQL_NTS, origUserid, SQL_NTS, 
password, SQL_NTS );

//Perform some work under user ID "newton"
. . . . . . . . . . .

 // Commit the work
SQLEndTran(SQL_HANDLE_DBC, hdbc1, SQL_COMMIT); 


// Switch the user ID on the trusted connection
SQLSetConnectAttr( hdbc1, 
SQL_ATTR_TRUSTED_CONTEXT_USERID, switchUserid,  
SQL_IS_POINTER 
);

//Perform new work using user ID "zurbie"
. . . . . . . . . 

//Commit the work
SQLEndTranSQL_HANDLE_DBC, hdbc1, SQL_COMMIT); 

// Disconnect from database
SQLDisconnect( hdbc1 );

	return 0;

}  /* end of main */

What to do next

When does the user ID actually get switched?
After the command to switch the user on the trusted connection is issued, the switch user request is not performed until the next statement is sent to the server. This is demonstrated by the following example where the list applications command shows the original user ID until the next statement is issued.
  1. Establish an explicit trusted connection with USERID1.
  2. Issue the switch user command, such as getDB2Connection for USERID2.
  3. Run db2 list applications. It still shows that USERID1 is connected.
  4. Issue a statement on the trusted connection, such as executeQuery("values current sqlid"), to perform the switch user request at the server.
  5. Run db2 list applications again. It now shows that USERID2 is connected.