IBM WebSphere Developer Technical Journal: Database identity propagation in WebSphere Application Server V6

Learn how to get major security benefits by developing code that lets your J2EE™ applications transparently send identity information to your database. Get the benefits of J2EE, including CMP beans, and still leverage the power of your database security.

Keys Botzum, Senior Consulting I/T Specialist, IBM Software Services for WebSphere

Keys Botzum is a senior consultant with IBM Software Services for WebSphere. He has over 10 years of experience in large scale distributed system design, and additionally specializes in security. Mr. Botzum has worked with a variety of distributed technologies, including Sun RPC, DCE, CORBA, AFS, and DFS. Recently, he has been focusing on J2EE and related technologies. He holds a Masters degree in Computer Science from Stanford University, and a B.S. in Applied Mathematics/Computer Science from Carnegie Mellon University. Mr. Botzum has published numerous papers on WebSphere and WebSphere security. Additional articles and presentations by Keys Botzum can be found at http://www.keysbotzum.com, as well as on IBM developerWorks WebSphere. He is also an author of IBM WebSphere: Deployment and Advanced Configuration.


developerWorks Professional author
        level

Vinod Jessani (jessani@us.ibm.com), Senior Software Engineer, IBM Software Services for WebSphere

Vinod Jessani is a Senior Software Engineer with the IBM WebSphere Enablement Team in San Diego with over 10 years experience in distributed systems and online transaction processing systems. As a senior member of the WebSphere Enablement team, Vinod leads pre-sales type of activities including pilots, proofs-of-concept, and technical presentations that involve WebSphere Application Server and WebSphere development tools. He has a Masters degree in Computer Applications from the University of Poona, India.



Soloman Barghouthi (soloman@us.ibm.com), Advisory Software Engineer, IBM Rochester Lab

Soloman Barghouthi is an Advisory Software Engineer at the IBM Rochester Lab. Soloman started his career at IBM working as a developer on the San Francisco project, and is currently a developer on the WebSphere Connection Management team. Soloman is a database expert, the Oracle resident, and has deep knowledge in DB2. Soloman is responsible for how Cloudscape and Oracle operate in WebSphere.



15 June 2005

Also available in Chinese

Introduction

One of the key challenges of enterprise systems is properly implementing strong system security controls. In a nutshell, critical data needs to be protected with appropriate authorizations. What makes this challenge particularly difficult with multi-tier J2EE systems where J2EE application code contacts a database (using JDBC, SQLJ, or CMP beans) is that, traditionally, the end user's identity information is lost. By "end user" we mean the user on whose behalf the application is running -- that is, if Bob authenticates to the application using standard J2EE security, then Bob is the end user.

This loss of end-to-end user identity represents a significant problem, since many organizations already have elaborate procedures in place for protecting their data within the database. These procedures often involve database audit logs indicating who did what and when, as well as strict authorization rules on the data itself.

In typical J2EE-based systems, the container maintains a pool of authenticated connections. While each end user authenticates to the application server (using one of several J2EE authentication mechanisms), the user's identity information is not available to the database. This is because all database access is performed using one of several common shared connections from the connection pool. Historically, this has resulted in applications having to reinvent existing database level authorization and auditing functions inside the application layer. This is wasteful when done properly and most likely insecure when done poorly.

Fortunately, many databases provide a way to send hints to the database that can alter the authorization and auditing identity associated with an existing connection. The problem has been leveraging this from within an application server. Of course, one can insert database-specific code in the application before every database access, but this is cumbersome and expensive -- and, it doesn't work when the container itself handles the database access, as it does with CMP beans. With the release of IBM® WebSphere® Application Server V6, however, we now have a solution to this problem.

WebSphere Application Server V6 introduces some changes to the database access infrastructure that make it possible to plug in custom code that affects connections before they are used within a transaction. Now, simply by developing a small piece of database-specific code, your J2EE applications can transparently send identity information to the database, which the native database authorization and auditing functions can then leverage. You can now gain the benefits of J2EE, including CMP beans, and still leverage the power of your database security.

This article describes how this can be done today using WebSphere Application Server V6.


The approach

We hinted above at the basic approach. The key to making this succeed is relying on a couple of closely related elements:

  • We will leverage the Java™ 2 Connector Architecture (J2C) specification feature known as reauthentication, which means that an existing connection to a resource can have the identity associated with it changed. While not explicit in the specification, for reauthentication to be useful, it needs to be efficient (for example, faster than opening a new connection).
  • We rely on the fact that WebSphere Application Server relational database access is built using the J2C connectors. In fact, WebSphere Application Server explicitly uses what is referred to as the RelationalResourceAdapter. This adapter is J2C compliant and is used by the WebSphere Application Server runtime to access relational databases. As of Version 6, the adapter has been enhanced to support reauthentication.

Now let's discuss our approach in a bit more detail.

J2C and reauthentication

To understand our solution, you first need to understand a bit about J2C, which is part of J2EE, and enables accessing enterprise systems from J2EE applications. J2C does this by providing the concept of a pool of connections to a resource. Resource-specific functions (for example, how to open connections, how to communicate, how to flow security information, and so on) are provided by resource adapters.

While not directly relevant here, it's worth noting that the IBM IMS™ and CICS® J2C adapters also support reauthentication.

What may not be obvious is that the WebSphere Application Server database infrastructure is in fact implemented using the J2C framework and, therefore, can support reauthentication to the database if the resource adapter supports reauthentication. In WebSphere Application Server V6, the RelationalResourceAdapter that is used for all relational database access has been enhanced to support reauthentication. Therefore, if the underlying database supports reauthentication of existing connections, then the WebSphere Application Server infrastructure can leverage it. WebSphere Application Server makes it possible for clients to provide custom code to leverage database reauthentication features if they are available. More on that later. This custom code is isolated from application code and does not affect the application programming model.

DataStoreHelper and J2C reauthentication

Reauthentication must first be enabled. This is done in WebSphere Application Server simply by selecting the Enable database reauthentication check box in the data source properties panel (Figure 1).

Figure 1. Data source properties
Figure 1. Data source properties

Before we explain how reauthentication works, you should know that reauthentication is relevant only if container-managed authentication/signon is enabled (that is, the res-auth J2EE application deployment descriptor is set to Container). When container-managed authentication is enabled, WebSphere Application Server uses the configured principal mapping module to create a JAAS Subject instance that contains a user ID and password for the target Enterprise Information System (EIS), such as a database. This gives you the opportunity to provide your own custom login module.

Reauthentication will typically not go into effect until after the connection pool reaches its maximum number of connections, which is a configurable connection pool property.

When the maximum number of connections is reached and a new request comes in to the connection pool for a connection whose credentials do not match any of the existing connections in the pool, the WebSphere pool manager will select any of the connections in its pool, mark it as "needing-reauthentication", and return the connection back to the RelationalResourcesAdapter (RRA). (Credentials of two connections match if the privateCredentials and publicCredentials of their subjects match. This equality is determined using the equals method against the subject's private and public credentials.)

The RRA will then call the doConnectionSetupPerTransaction() datastore helper method and pass a Boolean to indicate that reauthentication is required. doConnectionSetupPerTransaction() is a DataStoreHelper method that users must implement to provide the needed logic for the reauthentication. This method takes the following parameters:

  • Subject -- contains the credentials of the new connection request.
  • User -- the username of the new connection request.
  • Boolean -- indicates if reauthentication is required.
  • Current java.sql.Connection object.
  • Object -- reserved for future use.

The user will only have a non-null value if the subject is null (which happens if the authentication used is application (not container)). Also, the circumstances by which the doConnectionSetupPerTransaction() datastore helper method is called depend on the way the connections are configured; if the connections are configured to be shareable (that is, res-sharing-scope J2EE application deployment descriptor is set to shareable), then doConnectionSetupPerTransaction is only called on the first connection handle creation and before any transactions begin on that connection. If, on the other hand, the res-sharing-scope is set to unshareable, then every getConnection request will result in the doConnectionSetupPerTransaction() method being called (before any transactions begin on that connection, of course).

Putting it all together

With these key building blocks, let' look at how this all works together to achieve our objective.

Our total approach is essentially this:

  1. Create a custom login module that adds custom user information to the Subject.
  2. Create a custom login configuration that leverages our login module and the IBM-provided default database authentication login module. We do this because we still want to leverage the WebSphere Application Server database authentication infrastructure.
  3. Create a custom DataStoreHelper with methods that alter the database connection identity information when needed.
  4. Configure a DataSource to use the custom DataStoreHelper.
  5. Configure the application resource references during application installation to use the DataSource in conjunction with our custom login configuration.

Once the steps above have been completed:

  • Whenever a piece of application code (or the WebSphere Application Server CMP engine) performs a getConnection() call without specifying a user ID or password, the WebSphere Application Server J2C engine is activated.
  • The J2C engine then executes our custom login configuration, which executes the IBM default authentication login module, and then our custom login module.
  • Our custom login module simply alters the Subject to include the current user's identity.
  • The J2C engine then looks for a connection associated with that Subject. If one is found, it is used. If one is not found, an existing connection is selected and reauthentication occurs. (Careful readers may realize this isn't quite optimal. In a WebSphere Application Server V6 PTF, the algorithm will be enhanced to reauthenticate an existing connection only if the pool is full; it will grow the pool if the pool is not yet full.)
  • During reauthentication, our custom DataStoreHelper alters the connection.
  • The J2C layer then returns this connection to the caller and updates the connection pool to reflect the new Subject, which is now associated with the connection.

Authentication aliases and the default principal mapping
There is only one minor "trick" in these steps. The IBM-provided default principal mapping login module assumes that a J2C authentication alias is associated with the resource reference. Unfortunately, when you configure a resource reference to use custom authentication, you can't specify an alias in the admin user interface. Instead, you specify the alias information as a property on the resource reference. This property is com.ibm.mapping.authDataAlias. Then, when the default principal mapping module is invoked before our custom module, the resource reference will find the property it is looking for and then setup the database authentication information (a user ID and password) as usual by using the WebSphere Application Server J2C aliases. We will show how to configure this later.


What the databases support

We have implemented the approach outlined above with both DB2® and Oracle® by leveraging native database-specific function. We will briefly summarize this here. You should refer to your database documentation for details (see Resources).

Essentially, to support the approach described in this paper, your database must:

  • Support altering the authorization and auditing identity associated with an existing database connection.
  • Alter the connection identity efficiently, as it will occur frequently.

Given that the connection identity can be altered, the database may require the use of specific features or components. With DB2, there are no special requirements. With Oracle, the approach we will use requires the use of the Oracle Virtual Private Database feature; the information returned from the database is filtered using special SQL code that the VPD definer provides to Oracle. We will show an example of this later.

With DB2, we take advantage of the recently introduced Java API resetConnection(). Be aware that the version of this API that we are using is only available with DB2 V8.2 FP 9 and later. We also require DB2 APAR IY71420. This example will not work with previous versions of DB2.

With Oracle, we follow the advice in Leveraging Oracle Database Security with J2EE Container Managed Persistence (see Resources) and use the OracleConnection.setClientIdentifier() method, which sets auditing and authorization information on an existing connection.

Although we have not examined other databases, this approach is not database-specific. If your database supports the key concepts we require, you can likely implement something similar.


Actual implementation information

Knowing the basic issues and how WebSphere Application Server provides the needed plug points, you should be able to implement this solution yourself. However, to make things easier, we will provide a sample solution for DB2 and Oracle in source code form.

The sample code provided here is just that: a sample. We make no guarantees about the suitability for its use in a production environment. You should work with your database administrator and other experts to create an appropriate set of helpers that is right for your environment. These helpers should, of course, be subjected to rigorous performance and error testing before deploying them to production.

Our sample solution implements a custom DataStoreHelper for DB2 and Oracle, as well as a common login module that handles the J2C identity information. While implementing this, we realized that much of the control logic of the DataStoreHelper is common to all databases, so we did some refactoring to share common code. The result is the diagram shown in Figure 2.

Notice that there are two database-specific DataStoreHelpers: AssertionOracleDataStoreHelper and AssertionDB2DataStoreHelper. Both inherit from the existing WebSphere Application Server helpers, OracleDataStoreHelper and DB2DataStoreHelper, respectively. However, both use the common AssertionGenericHelper class, which contains most of the control logic that is database independent. The database-specific code that implements particular actions is in the database-specific helpers.

To support the reauthentication and identity projection model we've defined here, each database must support three key operations:

  • getUserOnConnection() -- Returns the current user associated with the connection. This is not a critical method, as this is used only for debugging purposes.
  • setUserOnConnection() -- Sets the connection identity to a particular user. It is assumed that we are setting both auditing and authorization information.
  • clearUserOnConnection() -- Returns the connection to its default state by removing any identity information. This means that the connection will revert to performing auditing and authorization based upon the connection pool identity (the common user ID and password you configure). This method is used when a connection is taken from the pool by an anonymous user.

To actually provide the user's identity information to the WebSphere Application Server J2C runtime, we implement a login module known as the AssertionLoginModule and configure it into a login configuration, which we will show in a moment. This login module determines the user's current J2EE identity and then adds it as a special principal on the Subject. This is an important step because it accomplishes two things:

  • The helpers learn the user's identity.
  • More importantly, by altering the Subject we have informed the WebSphere Application Server J2C runtime that this connection is now associated with a particular user. The WebSphere Application Server J2C cache will use that information to improve performance (by trying to use the connection with the same user) and to inform our helper when reauthentication is required (whenever the Subject associated with a connection needs to change).

Finally, like any well written piece of WebSphere Application Server code, our code is fully traced and uses the WebSphere Application Server JRAS trace infrastructure. (Tracing can be controlled using the standard WebSphere Application Server administrative tools.)

Figure 2. Class diagram
Figure 2. Class diagram

The next sections will explain the implementation of each class in a bit more detail. Refer to the source code included in the download file for more details.

AssertionLoginModule

The login module is fairly straightforward. As expected, it implements the standard Java LoginModule interface, but only the commit method does any real work. When the commit() method is invoked by the runtime, the login module obtains the current user's identity and then stuffs it into a PrincipalInformation object. Then, like any well written login module, it checks to make sure that the Subject doesn't already contain this PrincipalInformation object; if, however, it is already there, the existing Principals of the same type are removed and then this one is added. This is important because the helpers assume there is only one Principal of the PrincipalInformation type.

It's worth pointing out that the actual user ID of the current user is determined by the static method computeUserBasedOnCurrentUser(). This is done because this same computation is also performed by each helper. If, for some reason, your system requires a non-trivial mapping of J2EE user IDs to database user IDs, you can satisfy this requirement by changing this one method.

AssertionWithPasswordLoginModule
This module is simply a subclass of the AssertionLoginModule, with one key difference: it contains a new implementation of the computeUserBasedOnCurrentUser() method which populates the PrincipalInformation with both a user ID and a password. This is done because some databases (such as DB2) require a password for the new target user in order to perform reauthentication. Our implementation of this password management is quite trivial; we simply include a hard coded password that is plainly visible in AssertionWithPasswordLoginModule.computeUserBasedOnCurrentUser(). We opted for this clearly unrealistic approach for our example simply because any good approach depends heavily on the environment. Therefore, you will want to design an approach that makes sense in your environment. You have several choices:

  • If users authenticate directly to WebSphere Application Server, you can capture the user's password in a custom TAI or login module and place it in the user's Subject. Of course, this will require that you then enable Subject propagation.
  • You can look up each user's password in some shared property file as needed.
  • You can obtain a user's password from an identity mapping product such as IBM Tivoli® Access Manager.
  • You could configure the database such that all users have the same password, thus eliminating any password management issues. Of course, you'll have to then make sure that no one has direct access to the database over the network. This is the approach we've chosen here for simplicity.

To run the examples later, you will likely need to change the hardcoded password computation to something more appropriate to your environment. (This applies to DB2.)

AssertionGenericHelper

When you see the implementations of the database-specific helpers, you will notice that they defer all control logic to the AssertionGenericHelper class. Essentially, these implementations of the IBM-defined DataStoreHelper interface simply call this class. AssertionGenericHelper provides implementations of the two WebSphere Application Server-defined methods that support connection setup and management in the context of reauthentication:

  • doConnectionSetupPerTransaction
    This method is called by the WebSphere Application Server runtime before a connection is first used in a transaction, and provides us with the perfect opportunity to alter the connection's attributes prior to it being used. In our case, we use database-specific code (in setUserOnConnection) to alter the identity on the existing connection if reauthentication is required. WebSphere Application Server nicely tells us when reauthentication is required (meaning that the new and old Subjects associated with the connection do not match) by setting the Boolean reauth flag to true.

    If we decide to perform reauthentication, we compute the appropriate target user ID by using the information in the Subject (remember, this was set in our login module earlier). We also support clearing the existing user ID on the connection if the new target user has a null ID (meaning unauthenticated).

    However, in one case this isn't quite enough. When a connection is first used, we still need to set the authentication information, but WebSphere Application Server does not consider this to be reauthentication, since the connection identity has not been set in the first place. Therefore, we have to handle this case separately in the following method, doConnectionSetup().

  • doConnectionSetup
    This method is called whenever a connection is first created and used -- not within a transaction, but literally when it is first used. Here, we must set the connection identity to be that of the current user (there is no reauthentication flag here to help us). But, unfortunately, since there is no Subject in this case, we have no direct way of knowing what user identity our login module has specified. Therefore, we have to make an assumption: we assume that doConnectionSetup() is being called from the same thread as the user thread. This is in fact the case, but this is an assumption which could change in future versions of WebSphere Application Server (although such a change in extremely unlikely since databases themselves depend on this behavior).

    We obtain the user ID of the current user, using the same method as the login module, but obtain it in a generic way by using the getPrincipalFromThread() method, which each helper must implement. Once the identity is determined, we alter the connection identity just as we did with the previous method.

DB2AssertionDataStoreHelper

This class naturally leverages the control logic from AssertionGenericHelper and then implements the three required methods discussed above. The methods associated with this class are:

  • setUserOnConnection
    For DB2, we use the DB2 JDBC API resetDB2Connection(). By providing a new user ID and password, the DB2 runtime alters the existing connection to be used by this new user. As one might expect, this altered connection's auditing and authorization (the entire security environment, actually) is updated to reflect the new user. (Refer to DB2 documentation in Resources for more details and best practices for databases.)
  • getUserOnConnection
    This method cannot be implemented in DB2, since there is no API to determine the user associated with the connection. This method is only used for debugging purposes, and therefore is not critical.
  • clearUserOnConnection
    For DB2, we use resetDB2Connection() to return the connection to its original state. For this to work, we need the connection pool identity and password (defined on the alias) values, which we can get out of the Subject PasswordCredential.
  • PrincipalInformation getPrincipalFromThread()
    This method simply forwards a request to the AssertionWithPasswordLoginModule to determine the current user that is accessing the database.

OracleAssertionDataStoreHelper

This class also leverages the control logic from AssertionGenericHelper and then implements the three required methods we discussed earlier. As with DB2, the methods associated with this class are:

  • setUserOnConnection
    For Oracle, we use the Oracle-defined setClientIdentifier() method on the Oracle connection to alter both the auditing and authorization information. (Refer to Leveraging Oracle Database Security with J2EE Container Managed Persistence and the Oracle documentation in Resources for more on this.) Notice that lowercase user IDs are converted to uppercase because Oracle user IDs are always uppercase, and the implementation of the schema authorization that is usually implemented with a virtual private database is case sensitive. We must make sure that our user IDs are all uppercase to prevent subtle problems. (Refer to Oracle documentation in Resources for more details and best practices for databases.)
  • getUserOnConnection
    For Oracle, we use the getUserOnConnection() method to determine the current user. This method is for debugging purposes only.
  • clearUserOnConnection
    We clear the user on the connection using setClientIdentifier() with a blank user ID.
  • PrincipalInformation getPrincipalFromThread()
    This method simply forwards a request to the AssertionLoginModule to determine the current user accessing the database.

Configuration steps

In this section, we will step through the process of configuring an application that contains a resource reference to use DB2 with identity propagation. (The sample DB2 application, included in the download file, is TestAuthorizingDSApp.ear. A comparable Oracle sample application, TestAuthOraDSApp.ear, is also included. Similar process steps can be used with Oracle and with CMP beans, but we won't show them here.) The following steps go with the sample DB2 application that will be discussed next, but because these steps are not specific to any particular application, we thought it would be beneficial to describe them without associating them with a specific application.. In fact, other than the application installation steps, all other tasks here are likely to be performed only once per cell (or maybe once per DataSource), rather than once for each application.

  1. Give WebSphere Application Server access to the code.
    The code we developed is used by the WebSphere Application Server runtime, and so must be placed on a classloader that is available when accessing database resources. We decided to create a shared library and use this from an application server classloader:
    1. Take the binary JAR file, DBIdAssertionHelpers.jar (or compile from source), and place it somewhere on your computer. In our example, we chose c:\temp\DBLibraries, but in production you will naturally want to pick a better location.
    2. In WebSphere Application Server, create a shared library by selecting Environment => Shared Libraries, and specify the filesystem location and library name for the JAR file. Our shared library name is DBIdentityAssertion (Figure 3). Click OK.
      Figure 3. Create shared library
      Figure 3. Create shared library
    3. Create a new classloader on the application server that uses this library. Navigate to Application Servers => <your server name> => Java and Process Management => Class Loader => New. Accept the defaults and click Apply.
    4. Select the Libraries property and configure this to use the library we just created by clicking the Add button, then OK (Figure 4).
      Figure 4. Create a new classloader
      Figure 4. Create a new classloader

    We now have a new application server level classloader that loads the custom library containing the infrastructure code used here.

  2. Create an authentication alias
    To authenticate to the database, we will need a J2C authentication alias. Since this is just an example, we will use db2admin as our user ID. (Obviously, don't do this in production; WebSphere Application Server does not need database admin authority.)
    1. Navigate to Global Security => JAAS Configuration => J2C Authentication Data.
    2. Create a new alias, db2admin, with an appropriate user ID and password. In Figure 5, note the fully qualified alias name that WebSphere Application Server generates. You will need this full name later; the actual name on your system will likely be different.
      Figure 5. Create an authentication alias
      Figure 5. Create an authentication alias
  3. Create a DataSource
    Create a new JDBC provider (select the DB2 Universal JDBC Driver Provider) and then a DataSource in the usual manner. After creating the DataSource, you will need to make some changes to it to support the reauthentication function. The steps to do this are described below, where we change a DataSource named "DB2 Universal JDBC Driver DataSource" (your name will likely be different).
    1. To set a user-defined custom data store helper class on the DataSource, navigate to JDBC providers => DB2 Universal JDBC Driver Provider => Data sources => <your datasource> in the WebSphere Application Server admin console.
    2. We will override the default helper and specify our own helper, com.ibm.swservices.security.database.AssertionDB2DataStoreHelper (Figure 6). You will notice that there is a second custom DataStoreHelper in the example code, AssertionOracleDataStoreHelper, which can be used with Oracle. Click Apply.
      Figure 6. Create a DataSource
      Figure 6. Create a DataSource
    3. To make testing easier, set the connection pool maximum size (under connection pool properties) to 2. This will make reauthentication occur more frequently so you can observe the behavior.
    4. Select WebSphere Application Server datasource properties and enable reauthentication on the new datasource by checking Enable database reauthentication (Figure 7), then select OK.
      Figure 7. Enable reauthentication
      Figure 7. Enable reauthentication
    5. Set the default schema. While this is not directly related to reauthentication, our example queries the database using SQL statements with no schema qualifier. Therefore, the default schema value will be used. If none is specified, the schema for the user that authenticated to the database will be used. Obviously, problems will occur if the user changes, or if the connection pool user's (in our case, db2admin's) schema is not the same as the schema used by the samples. Here, ADMIN is the name of schema that contains the sample tables. Your environment may be different.

      To set the default schema that WebSphere Application Server will use when querying the database, select the Custom properties option on the DataSource page for your Datasource. Select the currentSchema property (Figure 8, for DB2 only) and specify the (case sensitive) name of the schema that contains the sample tables here. Click OK.

      Figure 8. Set the default schema
      Figure 8. Set the default schema

    You have now altered the DataSource to use the new custom DataStoreHelper and to support reauthentication.

  4. Create a custom login configuration
    We need to create a custom login configuration to support the J2C identity mapping function. With this configuration, we need to leverage the existing WebSphere Application Server default identity mapping module that provides the database authentication data (using the J2C alias). Therefore, our new configuration will include:
    • our default module: com.ibm.ws.security.auth.j2c.WSPrincipalMappingLoginModule
    • our custom module: com.ibm.swservices.security.database.AssertionWithPasswordLoginModule, which asserts the user's identity. (Oracle users can use the AssertionLoginModule for the custom module. This module does not provide a user password, but since Oracle supports reauthentication without a password, one is not needed.)

    The above modules are configured under the Application Logins panel, but the login module classname for the default identity mapping can also be determined by examining the DefaultPrincipalMapping login configuration. To create a new configuration:

    1. Navigate to Global Security => JAAS Configuration => Application Logins and select New.
    2. Assign the configuration an appropriate name (we used DBAssertion), then OK.
    3. Select your new configuration and add to it both the default and custom login modules listed above. When specifying the classname, always select use login module proxy. Figure 9 shows our new DBAssertion login configuration after we've configured in the two login modules.
      Figure 9. Configuration summary
      Figure 9. Configuration summary

    It's not a bad idea to save everything to the repository now. Generally, you will need to perform the steps above once per DataSource (except for the login configuration, which can be shared). Next, we will outline the steps that are required each time an application is installed.

  5. Application installation
    Now that DataSource and login configurations with all the necessary information have been created, we can install an application and configure it to use these new configurations. The steps focus on configuration of the resource references for EJB components. (You can do the same thing for CMP beans by specifying the default connection factory (p #5 of application installation in the admin console) or CMP specific connection factory (step #6 of application installation), but these variations will not be covered here.)

    Be aware that the sample application does include CMP beans. Those examples will not work unless you map the CMP bean resource references. Try this only after testing the simpler cases. Although the screen shots shown here reflect our sample application, the basic steps will apply to any application.

    1. Install the application as you would normally (generally you will just accept the defaults, but you may also want to specify the target servers) until you get to step 8 in the installation wizard: Map resource references to resources.
    2. Select the resource references at the bottom of the page for your EJBs, then scroll to the top and select the existing JNDI name (in our case, jdbc/DB2AuthDS). Click Apply on that panel. (See Figures 10 and 11.)
      Figure 10. Resource references JNDI name
      Figure 10. Resource references JNDI name
      Figure 11. Map resource references to resources
      Figure 11. Map resource references to resources
    3. Keep the resource references selected. In the "Specify authentication method" section of the panel, select Use custom login configuration and the DBAssertion login configuration you created earlier, then Apply.
      Figure 12. Specify authentication method
      Figure 12. Specify authentication method
    4. Add a custom property to the resource references. This property will reference the authentication alias created earlier and will be used by the WebSphere Application Server default principal mapping module. Click the Mapping Properties button for each of your references. You will see Figure 13.
      Figure 13. Custom properties
      Figure 13. Custom properties
    5. Select New to create a new property, then specify the following values:
      • Name: com.ibm.mapping.authDataAlias
      • Value: <alias name>.
      In our case, the alias is keysbotzumNode01/db2admin. Click OK.
      Figure 14. Create a new custom property
      Figure 14. Create a new custom property
    6. The summary area appears in the bottom right of the resource references page (Figure 15). Notice that container authorization is shown (this really should say "authentication"), that the method is DBAssertion, and that our custom mapping properties display.
      Figure 15. Login configuration
      Figure 15. Login configuration

We have now completed the steps that are specific to database identity projection for the sample application. Complete the application installation by clicking Next until the installation has finished, then save your changes.

Easing application installation

To alleviate the tedium and potential for error in these application installation steps, below is a sample wsadmin script that will install the sample application and specify the needed resource-reference information for both the embedded resource reference in our one EJB module, and for the default resource for CMP beans (not shown above). Using the script assumes that you have already defined the DataSource, the login configuration, and the authentication aliases. You will probably need to alter the bold elements to make them valid for your environment.

$AdminApp install C:/home/workWithDev/datasource-
reauth/TestAuthorizingDSApp.ear {-MapResRefToEJB 
{{TestDSEJB TestAuthDSTasks TestAuthDSEJB.jar,META-
INF/ejb-jar.xml jdbc/DB2AuthDS javax.sql.DataSource 
jdbc/DB2AuthDS DBAssertion 
WebSphere:name=com.ibm.mapping.authDataAlias,value=key
sbotzumNode01/db2admin }} -DataSourceFor20EJBModules 
{{TestDB2EJB TestDB2EJB.jar,META-INF/ejb-jar.xml 
jdbc/DB2AuthDS cmpBinding.container DBAssertion WebSphere:name=com.ibm.mapping.authDataAlias,value=key
sbotzumNode01/db2admin}} -MapModulesToServers { 
{TestDB2EJB TestDB2EJB.jar,META-INF/ejb-jar.xml 
WebSphere:cell=keysBotzumCell,node= 
keysbotzumNode01,server=myServer} {TestDSEJB 
TestAuthDSEJB.jar,META-INF/ejb-jar.xml WebSphere:cell= 
keysBotzumCell,node= keysbotzumNode01,server=myServer} 
{TestAuthDSWeb TestAuthDSWeb.war,WEB-INF/web.xml 
WebSphere:cell= keysBotzumCell,node= 
keysbotzumNode01,server=myServer}}}

$AdminConfig save

This above script was generated using the wsadmin installInteractive installation process. When an installation is completed in interactive mode, the equivalent non-interactive script line (shown above) is sent to the wsadmin.traceout file in the current profile. Use the installInteractive command yourself to generate an appropriate script for your own environment.

A second option is to specify container managed authentication on the resource itself: instead of specifying resource authentication information on every reference, you can specify the custom authentication module and the container managed authentication alias at the DataSource level (JDBC Providers => your provider => DataSources => your datasource page), but be aware that this is deprecated in WebSphere Application Server V6. Figure 16 shows how to configure this information on a DataSource directly.

Figure 16. Configuring container managed authentication
Figure 16. Configuring container managed authentication

Run the sample application

Before you can run the sample application, you need to prepare the database.

Configure the database

We only summarize the steps for configuring a DB2 or Oracle database in this section; we are performing default installations that include the sample databases that ship with the product.

We are assuming a test system. Your DBA will likely take issue with doing this on an important DB server. If so, talk with your DBA about how to collect the needed auditing information.

DB2 configuration
Other than creating the required user accounts and configuring the sample databases (which most DB2 installations provide by default), no additional DB2 configuration steps are required. However, if you are interested in seeing the DB2 auditing information, you will need to enable auditing. Your database administrator should be able to help you with this, but here is a summary of the steps we followed on our test server:

  1. To enable auditing:
    db2audit configure scope all
    db2audit start
    db2audit prune all -- delete previous/old information
    db2audit flush -- flush records to file (db2audit.log)
    del db2audit.out -- delete file
  2. To obtain audit information for what has happened since the last flush:
    db2audit extract

    This will send auditing information in a human readable form to db2audit.out in the <DB2 install>/DB2/security directory. Here are some interesting snippets from a DB2 audit trace taken from a run of our test application:

    We get the initial connection as DB2ADMIN:

    timestamp=2004-12-21-13.41.13.852000;category=CONTEXT;audit
    event=CONNECT;
      event correlator=2;
      database=sample;userid=db2admin;authid=DB2ADMIN;
      origin node=0;coordinator node=0;
      application id=NF000001.LC12.041221184113;application 
    name=db2jcc_application;
    ...

    We change the connection from DB2ADMIN to another user:

    timestamp=2004-12-21-13.41.15.354000;category=CONTEXT;audit 
    event=CONNECT_RESET;
      event correlator=3;
      database=sample;userid=db2admin;authid=DB2ADMIN;
      origin node=0;coordinator node=0;
      application id=NF000001.LC12.041221184113;application 
    name=db2jcc_application;
      
    timestamp=2004-12-21-13.41.15.654000;category=CONTEXT;audit 
    event=CONNECT;
      event correlator=2;
      database=sample;userid=testuser;authid=TESTUSER;
      origin node=0;coordinator node=0;
      application id=NF000001.LC12.041221184115;application 
    name=db2jcc_application;
    ...

    We use a connection as testuser2 (a user ID that does not have access to the EMPLOYEE table); note the authorzation violation:

    timestamp=2004-12-21-13.41.37.015000;category=CONTEXT;audit 
    event=PREPARE;
      event correlator=3;
      database=sample;userid=testuser2;authid=TESTUSER2;
      origin node=0;coordinator node=0;
      application id=NF000001.B912.041221184137;application 
    name=db2jcc_application;
      package schema=NULLID;package name=SYSSN300;
      package section=1;text=SELECT * FROM EMPLOYEE;
    
    timestamp=2004-12-21-
    13.41.37.085000;category=CHECKING;audit 
    event=CHECKING_OBJECT;
      event correlator=3;event status=-551;
      database=sample;userid=testuser2;authid=TESTUSER2;
      origin node=0;coordinator node=0;
      application id=NF000001.B912.041221184137;application 
    name=db2jcc_application;
      package schema=NULLID;package name=SYSSN300;
      package section=1;object schema=ADMIN;object 
    name=EMPLOYEE;object type=TABLE;
      access approval reason=DENIED;access attempted=SELECT;
    ...

    We see a connection being altered from testuser2 to testuser3:

    timestamp=2004-12-21-13.43.26.753000;category=CONTEXT;audit 
    event=CONNECT_RESET;
      event correlator=5;
      database=sample;userid=testuser2;authid=TESTUSER2;
      origin node=0;coordinator node=0;
      application id=NF000001.B912.041221184137;application 
    name=db2jcc_application;
      
    timestamp=2004-12-21-13.43.26.993000;category=CONTEXT;audit 
    event=CONNECT;
      event correlator=2;
      database=sample;userid=testuser3;authid=TESTUSER3;
      origin node=0;coordinator node=0;
      application id=NF000001.B912.041221184326;application 
    name=db2jcc_application;

Oracle configuration

As with DB2, we assume that the sample Oracle databases are installed along with some sample users. However, unlike DB2, some additional configuration is required to leverage the Oracle VPD function. The steps are briefly summarized here:

  1. Connect to Oracle as sysdba.
  2. Add VPD policy to tack on pred_function to all SQL SELECT/UPDATES on EMP:
    	begin
    		DBMS_RLS.ADD_POLICY (
    		OBJECT_SCHEMA => 'SCOTT',
    		OBJECT_NAME => 'EMP',
    		POLICY_NAME => 'DS_EX',
    		FUNCTION_SCHEMA => 'SCOTT',
    		POLICY_FUNCTION => 'PRED_FUNCTION',
    		STATEMENT_TYPES => 'SELECT,UPDATE');
    	end;
    /    (the "/" to the left here is very important)
  3. Define a pred function to enforce filtering:
        CREATE OR REPLACE function scott.pred_function
    	(p_schema in varchar2 default NULL, 
    	p_object in varchar2 default NULL)
        RETURN varchar2
        AS
        l_client_id varchar2(30) :=
    		nvl(sys_context('userenv',
    		'client_identifier'), 
    		' NULL');
        l_user varchar2(30) := sys_context('userenv',
    		'session_user');
        BEGIN
    	return 'ename = ' || 'sys_context(''userenv'',
    	''client_identifier'')';
        END;
     /
  4. Test out your VPD with two employees in the EMP table: SCOTT and BLAKE:
        exec DBMS_SESSION.SET_IDENTIFIER('SCOTT');
        select sys_context('userenv',
    	'client_identifier') from dual;
        select pred_function from dual;
        select ename, sal from scott.emp;
    
    ENAME             SAL
    ---------- ----------
    SCOTT            3000
    
        exec DBMS_SESSION.SET_IDENTIFIER('BLAKE');
        select sys_context('userenv',
    	'client_identifier') from dual;
        select pred_function from dual;
        select ename, sal from scott.emp;
    
    ENAME             SAL
    ---------- ----------
    BLAKE            2850

    If you hadn't defined the VPD procedures above, the same select would have returned the entire table contents.
  5. Enable auditing. To do this, you must first configure AUDIT_TRAIL to OS or DB, as appropriate, on the database manager. In our case, we set the auditing to use OS auditing, which sent audit information to the Windows event log. We then configured Oracle to audit table access: audit select table by access;.
  6. After running some tests in the sample application, we will see these types of messages in the event log. This shows that Oracle auditing is properly reflecting our identity assertion:
    Audit trail: SESSIONID: "194" ENTRYID: "6" STATEMENT: "6" USERID: "SYSTEM" 
    TERMINAL: "unknown" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "SYS" 
    OBJ$NAME: "DUAL" OS$USERID: "Admin" SES$LABEL: "DB2ADMIN".
    
    Audit trail: SESSIONID: "193" ENTRYID: "30" STATEMENT: "9" USERID: "SYSTEM" 
    TERMINAL: "unknown" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "SCOTT" 
    OBJ$NAME: "EMP" OS$USERID: "Admin" SES$LABEL: "SCOTT".

Using the sample application

With the database is ready, it is time to test the function.

The application we have included with this article to demonstrate the datasource reauthentication feature (TestAuthorizingDSApp.ear, available in the download file) should be installed and configured using the steps described above. This application uses the EMPLOYEE table example (which is installed by default during the DB2 install process) and J2EE security, and is protected using J2EE form-based login. (A version of the sample application for Oracle, TestAuthOraDSApp.ear, is also included.)

To run the sample application:

  1. Install the application and start the application server that is hosting TestAuthorizingDSApp.ear.
  2. Start testing datasource reauthentication by opening the login page in a browser window with this URL: http://<hostname>:<port>/TestAuthDSWeb. Figure 17 will display.
    Figure 17. Sample application main page
    Figure 17. Sample application main page
  3. Verify DataSource reauthentication functionality using either straight JDBC calls or CMP EJBs. If you select Access DataSource as Authenticated User using JDBC calls, the results from executing JDBC code will display as the currently authenticated user of the application. Since you are not yet authenticated, you will first be presented with the login page (Figure 18).
    Figure 18. Sample application login page
    Figure 18. Sample application login page
  4. On the login page, enter a user ID and password for a user in the WebSphere Application Server registry that has access to the SAMPLE database, then press Login. Keep in mind that this means you will need to configure DB2 authorization to give that user access to the database. (Refer to your DB2 documentation for details on configuring table level authorization for a database.)

    We assume that DB2 and WebSphere Application Server share common users. This can be achieved by using the same registry (for example, the Windows® registry) or by ensuring that the user IDs in the registries are otherwise somehow kept in sync.

  5. Now that you are logged in as a user with access to the SAMPLE database, you should see something similar to Figure 19.
    Figure 19. User access summary
    Figure 19. User access summary
    The JDBC code is quite trivial, as it simply fetches all users from the EMPLOYEE table. If it succeeds, all the employees from that table display in a TextArea on the results page. This page also contains a couple of buttons to facilitate retesting:
    • Again -- Takes you back to the main page where you can re-execute the same test or, alternatively, you can execute the test using CMP entity beans.
    • Access DataSource as Authenticated User using CMP calls -- Displays Figure 20.
    Figure 20. User access summary using CMP entity bean
    Figure 20. User access summary using CMP entity bean

    The results page once again contains all the employees in the table, but this time the table was accessed using a CMP entity bean.

  6. The previous steps have shown how an authenticated user with privileges to access the database tables gains access to data in the EMPLOYEE table. To see what happens when a WebSphere Application Server authenticated user that does not have these privileges tries to use this application, press the Logout button on the current page and return to the Login page (Figure 21).
    Figure 21. Login page
    Figure 21. Login page
  7. This time, login with a user ID that has access to the application but does not have access to the SAMPLE database. This will take you back to the main page of the application.
  8. Select Access DataSource as Authenticated User using JDBC calls. Figure 22 will display.
    Figure 22. User access summary failure
    Figure 22. User access summary failure

    The operation failed to fetch any data back from the EMPLOYEE table since user scott does not have access to the table.

  9. Return to the main page using the Again button and retry the test using CMP Entity beans.
    Figure 23. User access summary failure using CMP entity bean
    Figure 23. User access summary failure using CMP entity bean
  10. Using CMP Entity beans also results in an exception being generated as expected. See CMP caching considerations if you plan to combine reauthentication with CMP caching.
  11. The Access DataSource as an Unauthenticated User button on the main page logs out the current user and then uses JDBC calls to access the EMPLOYEE table. When an unauthenticated user access the database, it will execute under the identity of the user that fetched the connection in the first place; that is, the user ID and password associated with the J2C authentication alias configured earlier. When you press this button, you should see Figure 24.
    Figure 24. User access summary of user who fetched the connection
    Figure 24. User access summary of user who fetched the connection

Troubleshooting

Just like WebSphere Application Server code, the database reauthentication helper classes we have provided in this article are instrumented using the JRAS tracing facilities (JRAS usage is deprecated, but we are using it for now). What is traced -- and the level of detail of the trace -- is controlled by trace-strings specified using WebSphere Application Server's admin facilities. In terms of troubleshooting database reauthentication, the following trace-strings will prove very useful:

  • WAS.j2c=all=enabled
    Enables logging of WebSphere Application Server connector module operations. Information logged for this component includes information about connections as they are allocated, assigned to transactions, and subsequently returned to the free pool.
  • RRA=all=enabled
    Enables tracing of database adapters. It traces information about the container's interaction with the JDBC drivers and also logs calls about SQL statements executed and calls made to configured datastore helper classes.
  • com.ibm.swservices.*=all=enabled
    Enables diagnostic logging that we put into the reauthentication datastore helper classes.

To safeguard against users mistakenly enabling reauthentication without providing the reauthentication implementation, logic was added to WebSphere Application Server V6.0.1 to cause a SQLException to be thrown if reauthentication is enabled on the DataSource but the doConnectionSetupPerTransaction() method does not have a custom implementation.

Troubleshooting reauthentication failures can be tricky, particularly in some cases using DB2 where the actual underlying error may not be visible in the typical WebSphere Application Server logs. For example, if the call to resetDB2Connection specified invalid information, the reason will not be visible in the usual WebSphere Application Server error output. You will actually need to enable WebSphere Application Server tracing (as indicated above) to see the actual DB2 error message. The sample output below was taken from a test where we specified an invalid password for the connection user. Here is the normal WebSphere Application Server error output (SystemOut.log):

[5/16/05 19:14:42:290 EDT] 0000002a ConnectionEve A   J2CA0056I: The 
Connection Manager received a fatal connection error from the Resource 
Adaptor for resource jdbc/DB2AuthDS.  The exception which was received 
is com.ibm.websphere.ce.cm.StaleConnectionException: An error occurred 
during a deferred connect reset and the connection has been terminated.  
See chained exceptions for details.  DB2ConnectionCorrelator: 
NF000001.E511.050516231431

Notice above that WebSphere Application Server does not print a meaningful error message. The true error can be found only by enabling tracing. Below is the trace for the same event, but notice that it does indicate the precise error:

[5/16/05 19:14:42:290 EDT] 0000002a GenericDataSt <  
mapExceptionHelper: Mapping was done returning: Exit

com.ibm.db2.jcc.am.DisconnectException: An error occurred during 
a deferred connect reset and the connection has been terminated.  
See chained exceptions for details.  DB2ConnectionCorrelator: 
NF000001.E511.050516231431
	at 
com.ibm.db2.jcc.t4.T4Agent.readDeferredResetConnection(T4Agent.java:501)
	at 
com.ibm.db2.jcc.t4.T4Agent.beginReadChain(T4Agent.java:513)
	at 
com.ibm.db2.jcc.am.Agent.flow(Agent.java:252)
	at 
com.ibm.db2.jcc.am.PreparedStatement.flowExecute(PreparedStatement.java:2209)
	at 
com.ibm.db2.jcc.am.PreparedStatement.executeQueryX(PreparedStatement.java:505)
	at 
com.ibm.db2.jcc.am.PreparedStatement.executeQuery(PreparedStatement.java:488)
	at 
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:667)
	at 
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:477)
	at 
com.myd.session.TestAuthDSTasksBean.testDB2DS(TestAuthDSTasksBean.java:64)
	at 
com.myd.session.EJSRemoteStatelessTestAuthDSTasks_19d9d6be.testDB2DS(EJSRemoteStatelessTestAuthDSTasks
_19d9d6be.java:66)
	at 
com.myd.session._TestAuthDSTasks_Stub.testDB2DS(_TestAuthDSTasks_Stub.java:297)
	at 
com.ibm.swservices.servlet.DBIdAssertionServlet.doGet(DBIdAssertionServlet.java:41)
	at 
javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
	at 
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
	at 
com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1216)
	at 
com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:630)
	at 
com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:80)
	at 
com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1752)
	at 
com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
	at 
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:466)
	at 
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:405)
	at 
com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:104)
	at 
com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:555)
	at 
com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:608)
	at 
com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:941)
	at 
com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1028)
	at 
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1394)
---- Begin backtrace for Nested Throwables
com.ibm.db2.jcc.am.SqlException: Connection authorization failure 
occurred.  Reason: password invalid.
	at 
com.ibm.db2.jcc.t4.T4Connection.mapSecchkcd(T4Connection.java:1773)
	at 
com.ibm.db2.jcc.t4.T4Connection.securityCheckComplete(T4Connection.java:1365)
	... rest deleted ...

Versions

Testing

We have tested the code examples provided here with DB2 and Oracle, with direct JDBC and CMP beans, using WebSphere Application Server, distributed, on AIX® and Windows platforms. The same techniques will likely work with other databases provided they support extensions that enable user identity information to be asserted on existing connections.

DB2
Our testing used DB2 UDB V8.1 FP 9 (plus APAR IY71420; this APAR fixes a minor bug where the use of resetDB2Connection() does not honor the default schema settings on the DataSource). Both this fixpack and the APAR are required, as they together include the needed resetDB2Connection() API. We used the universal driver (JCC driver) with DB2 UDB on AIX and Windows, as well as DB2 on z/OS® (but not iSeries™). Our tests were performed using XA and non-XA modes as well as type-2 and type-4 modes.

Oracle
We tested our sample code with Oracle 9i Release 2 (personal edition) using the thin non-XA driver on Windows without trouble. We have every reason to believe that this will work with other operating systems, other Oracle editions, or later versions of Oracle.

Performance

To test the performance of database identity propagation, we used the "Access DataSource as Authenticated User using JDBC calls" function of the sample application that we provide with this article. This function is fairly straightforward and results in the execution of one single SQL statement:

SELECT * FROM EMPLOYEE

Performance was measured by using a load generator tool to simulate 12 concurrent users that executed the above function repeatedly. The size of the connection pool was set to 10 so that connections would be re-authenticated periodically.

Keep in mind that this example does not properly reflect most real world situations. Given this extremely simple test scenario, the relative cost of reauthentication is much higher than we would expect in a real application. Three key items are likely to be different in real applications:

  • The SQL queries are likely more complicated (and therefore more costly).
  • The size of the databases are larger (our database had only 50 rows).
  • The number of SQL queries performed per user request is normally more than one.

Thus, the amount of "real" work done per reauthentication will increase substantially in a real application, making the relative cost of reauthentication less then in our simple test case. In our limited performance testing with DB2 and Oracle, we found that the overhead of performing reauthentication varied between 15% and 50%, depending on a number of factors, including the database, the system, and the test scenario. That is, in our very simple (and worst case) test, we found that throughput dropped by between 15% and 50%.

The numbers presented here are not the result of formal carefully controlled benchmarks and are likely not representative of any particular application load. You should test this yourself with your applications to determine the performance impact in your environment.

(DB2 V8 APAR PQ99707 on z/OS includes performance enhancements to make reauthentication substantially more efficient. This APAR is for the z/OS platform only. We were unable to test with that APAR.)

8.3 Other versions and futures

DB2 V9 will include built-in fine-grained authorization support, known as Label Based Access Control (LBAC). Using these features in conjunction with what is described in this article, you will be able to achieve fine-grained authorization based on the user that authenticated to WebSphere Application Server, similar to what Oracle provides. DB2 on the zSeries platform currently supports multi level access control (MLS).

IBM is planning to enhance DB2 to provide a more efficient reauthentication framework across all platforms. As mentioned earlier, reauthentication on the zSeries platform is already relatively efficient.


Disclaimers

While we have tested the approach here sufficiently to believe it works in general, and although the APIs described here are officially supported with their respective products, we have not performed extensive load testing or detailed analysis of every possible scenario. You need to thoroughly analyze and test this solution on the platform you are using. (The authors welcome feedback about your experiences.) Below are some issues you should consider before applying this solution in your environment.

Database security configuration not explored

This paper is not a tutorial on database security. The authors have made no attempt to configure the database security in any reasonable way other than to demonstrate the function discussed in this article. You should work with an experienced DBA to properly configure database security and leverage the function discussed in this article.

Trust issues

When you use this solution, it is important to realize that fundamentally you are asking the database to trust the application server to provide end user identity information to it. Thus, you trust the applications running on the application server to do this identity projection properly. Leveraging Oracle Database Security with J2EE Container Managed Persistence (see Resources) says this another way:

Note this design implies a shared trust between the database and the application. This is absolutely necessary in almost all situations. It is not 100% trust, but it will require some trust. If you don't trust your applications, at least a little, then don't let them connect to your database! Otherwise, you will need to use a limited trust model similar to the one illustrated here.

Keep in mind that while this solution requires that you trust your applications to perform identity projection, this is still an improvement on the previous scheme where each application simply accessed the database under a single user identity. There the trust was just as complete, you just might not have realized it.

CMP caching considerations

The caching features of WebSphere Application Server CMP beans must be considered when implementing reauthentication. If CMP data is cached by WebSphere Application Server beyond the transactional boundary, you need to be aware that there are possible access violations that this article does not address. CMP beans by default are not cached across transactions, but you can turn on caching for individual beans and control the lifetime of the data in cache (see "Lifetime In Cache" in the WebSphere Application Server documentation). The cache does not differentiate based on user ID. This means that a CMP found by one user may be seen, via the cache, by other users not necessarily authorized at the database for that data. This does not affect creates, updates, and deletes to beans, as they always flow to the database and thus are always performed under the current user ID. Given this behavior, you may choose not to turn on caching for highly secure CMPs, or you may turn on caching when your goal is only to audit creates, updates, and deletes (and where auditing who queries a bean is unimportant).


Conclusion

This article has shown how you can "have your cake and eat it to." Thanks to new features in WebSphere Application Server V6, and with the examples in this article, you can write J2EE compliant applications while leveraging a database-specific authorization and auditing infrastructure.


Acknowledgements

The authors wish to thank Art Jolin from IBM Software Services for WebSphere for his valuable comments, as well as Tom Alcott for reviewing this paper. We would also like to thank several members of the IBM DB2 team: Paul Ostler, Sherry Guo, Paul Bird, and Curt Cotner. Their assistance and willingness to help with this effort made this article possible.


Download

DescriptionNameSize
code sampleswas6_databaseidentityprop.ZIP  ( HTTP | FTP )351 KB

Resources

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. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. 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 WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=85642
ArticleTitle=IBM WebSphere Developer Technical Journal: Database identity propagation in WebSphere Application Server V6
publish-date=06152005