Understand the trusted context feature in Informix

Secure connections with IBM Informix JDBC, ODBC and ESQL/C drivers

The use of connection pooling to data sources can lead to role-based security breaches. Support for trusted connections and trusted contexts in IBM® Informix® server gives the flexibility of connection reuse, granting of controlled privileges to the middle tier, and enhanced user accountability in a three-tier application without compromising database security, all while improving system performance. This article discusses trusted connections and trusted contexts, and shows how to manage trusted contexts using JDBC, ESQL/C and ODBC drivers.

Share:

Mamta Sharma (mamta.sharma@in.ibm.com), Software Engineer, IBM

Author photo of Mamta SharmaMamta Sharma works as a Software Engineer at IBM India Software Lab. She works actively toward maintaining and developing various features in Informix JDBC.



Tapan Kumar Ghosh (tapan.ghosh@in.ibm.com), Staff Software Engineer, IBM

Photo of author Tapan GhoshTapan Kumar Ghosh works at IBM India Lab, Bangalore. Tapan has more than nine years of IT experience. In his current role, Tapan is lead engineer for IBM Informix CSDK (Client Software Development Kit) at India Software Lab.



Kollol Kumar Misra (kolmisra@in.ibm.com), Senior Staff Software Engineer, IBM

Photo of author Kollol MisraKollol Kumar Misra works at IBM India Lab, Bangalore. Kollol has more than seven years of IT experience. In his current role, Kollol is lead engineer for IBM Informix CSDK (Client Software Development Kit) at India Software Lab.



10 May 2012

Also available in Chinese Russian

Introduction

In a traditional three-tier application model, you can interact with the database server only after successful authorization, checking, and auditing is performed by the middle tier. Even though there are multiple users logged into the middle tier, the middle tier servers use a single user ID to access the database, and privileges are granted on all resources. This model fails to retrieve the identity of the actual user trying to access database for access control. Also, if the middle tier user ID is ever compromised, this can lead to a serious security threat.

To enable a unique set of interactions between the middleware server and the database server, the concept of a trusted database connection was introduced in Informix. A trusted context is a database security object created on the database server that defines the properties of a trusted database connection. Trusted connection is established when the connection attributes match the attributes of a unique trusted context defined at the Informix server. Once a trusted connection is established by the middle tier server, the same physical connection can be reused without the need to re-authenticate the end-user at the database server.

From the perspective of an application developer, the importance of trusted connections lies in the fact that a single database connection can be shared with other users while maintaining the individual user ID of each application or user trying to access the database. Control of user privileges helps in appropriately granting or restricting user access to sensitive resources, thereby increasing system security and user accountability.

Managing trusted contexts using Informix client drivers

In this section, you'll learn how to create, alter, and drop a trusted context. To create and manage trusted contexts, your application must connect to the Informix server through TCP/IP. The following Informix client drivers support trusted connections by exposing relevant APIs discussed in later sections.

  • IBM Informix JDBC driver
  • IBM Informix ODBC driver
  • IBM Informix ESQL/C driver

Establish a trusted connection

Using the JDBC driver

To request a trusted connection in JDBC applications, include the TRUSTED_CONTEXT = TRUE; property in the database URL, as shown in Listing 1.

Listing 1. Connecting using JDBC driver
  jdbc:informix-sqli://hostname:portnumber/database_name:INFORMIXSERVER = server_name;
  TRUSTED_CONTEXT=TRUE;

Using the ODBC driver

The following are two ways that you can establish a trusted connection through ODBC, with an example shown in Listing 2.

Using SQLSetConnectAttr() API:
SQLSetConnectAttr(hdbc1,SQL_ATTR_USE_TRUSTED_CONTEXT,SQL_TRUE,SQL_IS_INTEGER));

Using the connection string parameter:
TRUSTEDCONTEXT=1; or TCTX=1;

Listing 2. Sample connection string of ODBC driver
  "Server=ol_informix_1170;Host=9.124.123.54;Service=ol_informix_1170;Protocol=onsoctcp;
  Database=stores7;TRUSTEDCONTEXT=1;"

Using the ESQL/C driver

In ESQL/C, a trusted connection is established using the TRUSTED keyword with the connect statement, along with user name and password, as shown in Listing 3.

Listing 3. Connecting using ESQL/C driver
  EXEC SQL CONNECT TO 'database_name' USER 'informix' USING :infx_passwd TRUSTED;

Creating trusted context

The security administrator (SECADM) creates a trusted context object.

The following statements, shown in Listings 4 through 7 for the JDBC, ODBC, and ESQL/C drivers, creates a trusted context TCX1 based upon a connection such that the trusted connection can be reused under two different user IDs. When reused under user ID "Joe", authentication is not required. However, authentication is required if the trusted connection is to be reused under user ID "Bob". Note also that the trusted context has a default role called "Manager." This implies that users working within the confines of this trusted context inherit the privileges associated with the role manager.

Listing 4. Sample SQL query
  CREATE TRUSTED CONTEXT TCX1
  BASED UPON CONNECTION USING SYSTEM AUTHID informix
  DEFAULT ROLE MANAGER
  ENABLE
  ATTRIBUTES (ADDRESS '9.26.113.204')
  WITH USE FOR Joe WITHOUT AUTHENTICATION,
  Bob WITH AUTHENTICATION
Listing 5. Creating a trusted context object using Informix JDBC driver
  public void createTrustedContext(){
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate("CREATE TRUSTED CONTEXT TCX1 "+
					"USER informix "+
					"DEFAULT ROLE MANAGER "+
					"ENABLE "+
					"ATTRIBUTES (ADDRESS '9.26.113.204') "+
					"WITH USE FOR Joe WITHOUT AUTHENTICATION, "+
					"Bob WITH AUTHENTICATION;"
			);
			System.out.println("Trusted context TCX1 created!");

		}
		catch (SQLException e){

			System.out.println("Trusted context Creation Failed");
		}
	}
Listing 6. Creating a trusted context object using Informix ODBC driver
  SQLExecDirect( hstmt, "Grant dbsecadm to Joe", SQL_NTS );
  SQLDriverConnect( hdbc, NULL, "dsn=MyDSN;uid=Joe;pwd=Big0data;",
  SQL_NTS,ConnStrOutp, 250, &pcbConnStrOut,SQL_DRIVER_NOPROMPT );
  SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 );
  gethostname(strbuf,128);
  addr =  gethostbyname(strbuf);
  sprintf( (char *)strbuf, "CREATE TRUSTED CONTEXT TCX1 BASED UPON CONNECTION"
  	" USING SYSTEM AUTHID informix ATTRIBUTES (ADDRESS '%s')"
  	" WITH USE FOR Joe WITHOUT AUTHENTICATION,Bob WITH AUTHENTICATION ENABLE",
  	inet_ntoa(*((struct in_addr **)addr->h_addr_list)[0]));
  SQLExecDirect( hstmt1, (unsigned char*)strbuf, SQL_NTS );
Listing 7. Creating trusted context object using Informix ESQL/C driver
  EXEC SQL BEGIN DECLARE SECTION;
  char   trusted_context[1024];
  char   joe_passwd[20];
  EXEC SQL END DECLARE SECTION;
  
  /* Retrieve the host name of computer */
  gethostname(strbuf,128);
  
  /* Retrieve IP address of computer */
  addr =  gethostbyname(strbuf);
  strcpy(joe_passwd,"Big0data");
  
  EXEC SQL GRANT DBSECADM TO Joe; 
  EXEC SQL CONNECT TO 'trusted' USER 'Joe' using :joe_passwd; 
  sprintf( (char *)trusted_context, "CREATE TRUSTED CONTEXT TCX1 BASED UPON  CONNECTION"
  	" USING SYSTEM AUTHID informix ATTRIBUTES (ADDRESS '%s')"
  	" WITH USE FOR Joe WITHOUT AUTHENTICATION, Bob WITH AUTHENTICATION ENABLE",
  	inet_ntoa(*((struct in_addr **)addr->h_addr_list)[0]));
  EXEC SQL PREPARE create_ctxt FROM :trusted_context;
  EXEC SQL EXECUTE create_ctxt;

Switching users on a trusted connection

Suppose that a trusted connection is established and that no unit of work has started. After the trusted connection has been established, the application can then switch the user to an allowed user defined in the trusted context. In example 1, a request to switch the user ID on the trusted connection to user Joe, without providing authentication information, will be allowed because user Joe 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 user Bob without providing authentication information, will fail because user Bob is defined as a user of trusted context CTX1 for whom authentication information must be provided.

You can switch user IDs after a trusted connection is established but you must ensure that at least one of the following statements is true.

  • The new user ID is the primary user ID defined in the trusted-context object.
  • The new user ID is explicitly defined as a user in the trusted-context object.
  • The trusted-context object is defined as WITH USE FOR PUBLIC.

During a user-ID switch, the database connection is maintained, but the switch results in a new connection environment. Objects such as temporary tables and WITH HOLD cursors are lost in the new environment.

To switch to a different user using one of the client drivers, the following commands are used in the application.

  • For a switch request without authentication requirements, use the SET SESSION AUTHORIZATION statement without a user password. SET SESSION AUTHORIZATION TO 'user_ID'.
  • For a switch request with authentication requirements, include the new user's password in the SET SESSION AUTHORIZATION statement. SET SESSION AUTHORIZATION TO 'user_ID' USING 'user_password'.

The trusted-context object used for a trusted connection must be enabled when a switch request is made. As shown in Listings 8 through 10 for the JDBC, ODBC, and ESQL/C drivers, if the trusted-context object associated with the trusted connection is dropped or disabled, and a switch request for that trusted connection is made, the system drops the connection and returns an error message with sqlcode -26456.

Listing 8. Switching user with Informix JDBC driver
 public void switchTrustedContext()
	{
		try{
			stmt.executeUpdate("SET SESSION AUTHORIZATION TO 'Joe'");
			System.out.println("Switched user to Joe");
		}
		catch (SQLException e){
    	System.out.println ("Error switching Joe");
		}

	}
Listing 9. Switching users with Informix ODBC driver
  SQLExecDirect( hstmt1, "SET SESSION AUTHORIZATION TO 'Joe'", SQL_NTS );
  Or
  SQLExecDirect( hstmt1, "SET SESSION AUTHORIZATION TO 'Bob' using 'pwd123'", SQL_NTS );

In ESQL/C, username can be a string or ESQL/C host variable, but password must be an ESQL/C host variable that holds the valid password for the login name specified in user_ID.

Listing 10. Switching users with Informix ESQL/C driver
  EXEC SQL SET SESSION AUTHORIZATION TO :username;
  Or
  EXEC SQL SET SESSION AUTHORIZATION TO 'Joe';
  Or
  EXEC SQL SET SESSION AUTHORIZATION TO 'Bob' USING :passwd;

Altering trusted context

The ALTER TRUSTED CONTEXT statement, as shown in Listing 11, modifies the definition of a trusted context at the current server.

Listing 11. Sample SQL query - ALTER TRUSTED CONTEXT
  ALTER TRUSTED CONTEXT TCX1 REPLACE USE FOR Bob WITHOUT AUTHENTICATION

Assume that trusted context TCX1 exists. Issue an ALTER statement, as shown in Listings 12 through 14 for the JDBC, ODBC, and ESQL/C drivers, to modify the existing user Joe to use the trusted context with authentication, and add everyone else to use it without authentication.

Listing 12. Altering trusted context object using Informix JDBC driver
 public void alterTrustedConnection(){
		try{

			stmt.executeUpdate("ALTER TRUSTED CONTEXT TCX1 " +
					"REPLACE USE FOR Bob WITHOUT AUTHENTICATION"
			);
			System.out.println("Altered TCX1");
		}
		catch (SQLException e)
		{
			System.out.println("Alter Trusted Context Failed");
		}
	}
Listing 13. Altering trusted context object using Informix ODBC driver
  SQLExecDirect( hstmt,
  		"ALTER TRUSTED CONTEXT TCX1 REPLACE USE FOR Bob WITHOUT AUTHENTICATION ",
  		SQL_NTS );
Listing 14. Altering trusted context object using Informix ESQL/C driver
  EXEC SQL ALTER TRUSTED CONTEXT TCX1 REPLACE USE FOR Bob WITHOUT AUTHENTICATION;

Renaming trusted contexts

The RENAME TRUSTED CONTEXT statement, as shown in Listings 15 through 17 for the JDBC, ODBC, and ESQL/C drivers, is used to change the name of a trusted-context object.

Listing 15. Renaming a trusted context object using Informix JDBC driver
  public void renameTrustedConnection() {
		try        {
			stmt = conn.createStatement();
			stmt.executeUpdate("RENAME TRUSTED CONTEXT TCX1 TO TCX2");
			System.out.println("Renamed TCX1 to TCX2");

		}
		catch (SQLException e)
		{
			System.out.println("Could not rename the Trusted Context");

		}
	}
Listing 16. Renaming a trusted context object using Informix ODBC driver
  SQLExecDirect( hstmt,
  		" RENAME TRUSTED CONTEXT TCX1 TO TCX2 ",
  		SQL_NTS );
Listing 17. Renaming a trusted context object using Informix ESQL/C driver
  EXEC SQL RENAME TRUSTED CONTEXT TCX1 TO TCX2;

Dropping a trusted context

The DROP TRUSTED CONTEXT statement, as shown in Listings 18 through 20 for the JDBC, ODBC, and ESQL/C drivers, is used to remove the trusted-context definition from the Informix system catalog.

Listing 18. Dropping trusted context object using Informix JDBC driver
public void dropTrustedContext(){
		try
		{
			stmt.executeUpdate("drop trusted context TCX2");
			System.out.println("Trusted Context TCX2 Dropped");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.out.println(e.getMessage());
		}

	}
Listing 19. Dropping trusted context object using Informix ODBC driver
  SQLExecDirect( hstmt,
  		" DROP TRUSTED CONTEXT TCX2",
  		SQL_NTS );
Listing 20. Dropping trusted context object using Informix ESQL/C driver
  EXEC SQL DROP TRUSTED CONTEXT TCX2;

Conclusion

This article focused on managing trusted connections from an application perspective. It demonstrated the use of the trusted context feature through simple JDBC, ODBC and ESQL/C examples. There are sample demo applications available for download that demonstrate how to use trusted context in JDBC,ODBC and ESQL/C.


Downloads

DescriptionNameSize
Sample JDBC demo appJDBCDemo.zip2KB
Sample ODBC demo appODBCDemo.zip2KB
Sample ESQL/C demo appESQLCDemo.zip2KB

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=813445
ArticleTitle=Understand the trusted context feature in Informix
publish-date=05102012