Using Identify Based Connection Pooling With WebLogic Server

Steve Button, June 2009

The typical model of interaction an application server has with a database is through the use of datasources. 

A datasource is an entity that is configured with appropriate information to allow it to create and manage connections with a database, and to hand these out to applications when they request them.  A datasource typically creates pools of connections for effiency purposes and shares these amongst applications that make use of it.

In the typical datasource use case, all the connections the datasource creates use the same username and password.  This results in all access to the database being performed using the same database credentials.  In most cases this is perfectly acceptable and handled as part of the overall application architecture when needing to dealing with shared, partioned data.

For some rare cases, requirements can exist where it’s necessary to preserve the application user identity in some form all the way down to the database.  This could be for the purposes of restricting information access, establishing audit trails, resource scheduling based on user context, etc.
To support these two different usage requirements, WebLogic Server supports two different types of connection pools through its datasource implementation:

Homogeneous: Regardless of the current user of the application, all connections in the pool use the same security credentials to access the DBMS.

Heterogeneous: Allows applications to use a JDBC connection with a specific DBMS credential by pooling physical connections with different DBMS credentials.
To enable these different connection pool types to be created, WebLogic Server provides three methods for establishing the DBMS credentials for connection pool:
Username and Password: The username and password are stored statically in the datasource configuration.  All connections use the same credentials.

Set Client Identifier on Connection: Same as the Username and Password option.  Additionally, sets the database client identifier for the database connection to a value derived from the current WebLogic user.  All connections use the same credentials.

Identity-Based Connection Pooling: The credentials for the database connection are derived from the current WebLogic user. Provides a heterogeneous pool of connections, based on the identity of the WebLogic users.
This note will describe how to setup and test Identity-Based Connection Pooling.

Identity-Based Connection Pooling

According to the documentation, Identity-Based Connection Pooling Allows applications to use a JDBC connection with a specific DBMS credential by pooling physical connections with different DBMS credentials.
If the Enable Identity Based Connection Pooling attribute is enabled on a data source, when an application requests a database connection, the WebLogic Server instance selects an existing physical connection, or creates a new physical connection with requested DBMS identity based on a map of WebLogic user credentials and DBMS credentials.
Lets examine that definition in more detail.

Identity-Based Connection Pooling is defined as behavior of a datasource connection pool.  It controls how credentials are obtained and supplied to a connection when it is requested from the datasource.
To obtain the credentials for the physical database connection, a credential Map is consulted.  The current WebLogic identity is obtained and used as the key for the credential map, to retrieve the associated database credentials.  This implies that there is an indirection step between the WebLogic user and the actual database credentials used.

The credential mapping can be thought of as the simple association of WebLogic identities to corresponding database usernames and password. 

When a connection is requested from a datasource, the current WebLogic user is obtained from the server and the corresponding database credentials are obtained from the credential map.  A quick scan of the unreserved connections in the pool is done to determine if an existing connection with the database credentials is available for use.  If so, it is reserved and returned immediately to the application.  If no existing connection is available, a new physical connection is established using the database credentials, reserved and returned to the requesting application.

Step 1: Identify Required WebLogic and Database Identities

The credential mapping functionality in WebLogic Server is flexible and supports many types of associations between the user types.  For example, each WebLogic user can be mapped to a unique database user or sets of WebLogic users can be mapped to a specific database user, or even a direct 1-1 mapping between the set of application users and database users.

From the credential-mapping table shown above, appuser1 is mapped to database user dbuser1.  Similarly, appuser2 and appuser3 are mapped to dbuser2 and dbuser3 respectively.  Users acct1 and acct2 are mapped to the same database user accounts.  And finally, appadmin is mapped to the database user with the same name appadmin.

Once the application and database identities that are needed by the application have been discovered, the actual creation and configuration of the datasource can take place.

Step 2: Create a DataSource with Identity-Based Connection Pooling       

A datasource that uses identity-based connection pooling is just a normal datasource on which the “Enable Identity Based Connection Pooling” is set.
Create a normal datasource, point it at the target database and test it. 

A valid database username and password should be supplied to allow the datasource to be tested and monitored from the administration console.

Test the datasource to validate it connects to the target database.

With the datasource created, enter the configuration screen for the datasource and select the Identity Options tab.
Select the Enable Identity Based Connection Pooling option and save the configuration.

Step 3: Create the Credential Mapping for the DataSource

With the datasource created that uses identity-based connection pooling, the next task is to create the credential map and populate it with the relevant mappings for the datasource to use.

Select the Security tab and then the Credentials Mapping tab. Select New to create a new Credential Map.

Enter the credential mappings that were discovered earlier on. 

• The WLS User field identifies the WebLogic identity.
  
• The remote user and remote password fields identify the database credentials that are associated with the specified WLS User value.

Note: it is assumed that the relevant user accounts have already been created on the target database.  If not, stop now and create the required database accounts for users dbuser1, dbuser2, dbuser3, accounts and appadmin.

The new credential mapping entry will be shown in the credential mappings page.

Continue to enter all the credential mappings that were discovered in the investigation phase.

This concludes the datasource creation phase.

Step 4: Create WebLogic Identities

The set of application WebLogic identities and the group that the application will use need to be created before the application is deployed.

Using the application identities identified in step1, use the console screens to create the users.

Access the Security Realm myrealm in the console.

Select the Users and Groups then the Users tab.  This will display the current set of users.

Click the New button to add a new user.

Enter the details for the new users and save.

Repeat the task to add all the users that were identified in step 1.

This completes the user creation task.

Step 5: Deploy an Application with Protected Resources

To exercise and test identity-based connection pooling, an application needs to be configured to use authentication. 

For this example, a simple web application is used which has a JSP resource protected with a Java EE declarative authentication.  The authentication method is specified to use form-based authentication. The details of the declarative authentication are visible in the WEB-INF/web.xml descriptor file.

</security-role>

This stanza declares that all the resources behind the /secure/* URL pattern are protected.  To access the secured resources, the authenticated user must belong to the SECUREUSERS role. Upon deployment, the SECUREUSERS role is subsequently mapped to the set of WebLogic Server identities that were created. This is accomplished in this example using the weblogic.xml deployment descriptor.

<weblogic-web-app>

  <security-role-assignment>

    <role-name>SECUREUSERS</role-name>

    <principal-name>acct1</principal-name>

    <principal-name>acct2</principal-name>

    <principal-name>appuser1</principal-name>

    <principal-name>appuser2</principal-name>

    <principal-name>appuser3</principal-name>

    <principal-name>appadmin</principal-name>

  </security-role-assignment>

</weblogic-web-app>        

This stanza declares that resources protected by Java EE SECUREUSERS role can be accessed by any of the named WebLogic users.
On deployment, the application users will be mapped to the WebLogic users using a policy based on the mapping rules defined in weblogic.xml.

Step 5: Test the Application

The goal of testing the application is to observe the results of logging on as different application users and observing the resulting JDBC connection details.

After successful authentication, the application does a lookup of the datasource that is using identity-based connection pooling, executes a SQL statement that issues the query shown below to determine where it is connected and who the database user is, then displays the results.

String SQL = "select” +
"SYS_CONTEXT('USERENV','CURRENT_USER') current_user," +
"SYS_CONTEXT('USERENV','HOST') host," +
"SYS_CONTEXT('USERENV','DB_NAME') db_name
from dual";

To observe the identity-based connection pooling in action, different application users will result in different database users being used, based on the mappings provided in the credential map.

Case 1: Logon as appuser1, verify dbuser1
According to the credential map, appuser1 maps to the database user dbuser1.

Observe tha the WLS user is shown as appuser1, while the SQL query using the JDBC connection shows the database user as dbuser1.

Case 2: Logon as appuser3, verify dbuser3

According to the credential map, appuser1 maps to the database user dbuser1.

Observe tha the WLS user is shown as appuser2, while the SQL query using the JDBC connection shows the database user as dbuser2.

Case 3: Logon as acct1, verify accounts
According to the credential map, acct1 maps to the database user accounts.

Observe tha the WLS user is shown as acct1, while the SQL query using the JDBC connection shows the database user as accounts.

Case 4: Logon as acct2, verify accounts
According to the credential map, acct2 maps to the database user accounts.

Observe tha the WLS user is shown as acct2, while the SQL query using the JDBC connection shows the database user as accounts.

Note that the acct1 and acct2 users are both mapped to the database user accounts. 

Case 5: Logon as appadmin, verify appadmin
According to the credential map, appadmin maps to the database user appadmin.

Observe tha the WLS user is shown as appadmin, while the SQL query using the JDBC connection shows the database user as appadmin.
This demonstrates that a direct 1-1 mapping between a WLS user and a database user is possible.

Summary

This example demonstrates how the use of the Identity-Based Connection Pooling option on a WebLogic datasource enables the creation of hetereogenous connections in the datasource’s connection pool.  A credential map is used to specicy the mappings between the WLS users and the database users. At runtime the WebLogic user identity is used as the key to obtain the requisite database credentials, which are then used by the connection pool to establish a connection to the database.