Monitor database activity for application users with Guardium and WebSphere Application Server

An approach for monitoring database activity for application users in pooled connection environments without changing the application



Several compliance requirements, like PCI-DSS and SOX, mandate that specific activity on a company's database is audited and can be assigned to the person responsible for the respective activity. At the same time, current applications have increasingly taken on the responsibility of authenticating and authorizing users themselves instead of leaving it to the database. In this situation, it is virtually impossible, on the database level, to trace the individual actions on the database back to the application user responsible for a certain activity. Existing and usually proprietary approaches rely on database features like re-authentication or trusted contexts, allowing the application to switch the user owning the database connection and therefore enabling suitable monitoring.

Usually, both re-authentication and trusted contexts are vendor-specific and frequently require the re-authenticated user to be known to the database. With ever larger numbers of application users, especially for web applications which may sport large numbers of users, this approach becomes increasingly impractical and, as indicated above, increasingly less used in application development.

This article presents a generic approach for all WebSphere Application Server applications that does not require changes to the existing applications, that enables database activity monitoring tools like InfoSphere Guardium to reliably match the application front-end user with their corresponding database activity, leaves authentication and authorization to the application or application container respectively, and finally, works with only slight modifications for all big relational database management systems.

The challenge

In typical applications, such as J2EE applications, the container maintains a pool of database connections that are authenticated via a technical user of the running application. The application user only authenticates to the application, not to the database, with the net effect that the information about the application user is lost for any database logging or monitoring approach that resides on the database server, as shown in Figure 1.

Figure 1. Typical application topology where application user information is lost at the database layer
Diagram of a typical 3-tier-application architecture with technical user access to the database. The application user information is lost to the database
Diagram of a typical 3-tier-application architecture with technical user access to the database. The application user information is lost to the database

Why is this important?

Several compliance regulations, like PCI-DSS and SOX, include requirements with a focus on data governance and may require monitoring to be enabled on the databases to see what data was accessed when and by whom. Additionally, internal requirements might require privileged users to be monitored in order to protect data from unauthorized access, as well as make the DBA's work more transparent.

Within the PCI-DSS scenario, the monitoring focus lies on all access to credit card information. In order to monitor access to credit card information through applications that use pooled connections, it is necessary to determine the real application user behind the technical user connecting to the database. With the decoupling of data access and user management as outlined previously, this task becomes arbitrarily complex.

This article's approach


This article's approach is to convey the application user information to the database as metadata, so database activity monitoring tools can pick up this information.

At the same time, this transmitted meta-information is ignored by the database management system (DBMS), so both the DBMS (including DB activity, its permission system, authentication and authorization scheme) as well as the WebSphere Application Server (including its connection pooling facility) are not impacted and will work as efficiently as before. Furthermore, the application itself does not have to be changed because this approach is non-intrusive to the application.

This is accomplished by implementing a custom DataStoreHelper class that intercepts each transaction and is responsible for identifying the application user and transmitting it to the Guardium system for monitoring and evaluation.


In order for this approach to work, the following prerequisites must be fulfilled.

  1. The application must use the WebSphere connection pool which is configured within WAS and provided to the application via a JNDI name. This ensures that the DataStoreHelper developed later can be added non-intrusively to the application.
  2. The application uses WebSphere application security because the code relies on the application user retrieved statically from the class API.


When configuring a DataSource for use it with your application, WebSphere Application Server allows you to specify a data store helper class.

This helper bridges the gap between database vendor specific code and the generic javax.sql.DataSource interface. Typically there is no need to specify a custom helper class, as WebSphere Application Server provides default data store helper classes for the most commonly used databases.

In the case in this article, this represents a perfect plug-point for transmitting the application user identity to the Guardium system, as the DataStoreHelper interface defines a method doConnectionSetupPerTransaction(...) allowing you to intercept the connection per transaction before it is used. The idea is to retrieve the name of the currently logged-in user and include it in a special SQL statement executed on the connection before it is used to execute the real application related statements.

The following dummy SQL statement can be easily monitored by the Guardium system and enables the correlation of a connection and application user responsible for the executed statements in the current transaction, as shown in Listing 1.

Listing 1. Transmitting the current application user name per transaction
public void doConnectionSetupPerTransaction(Subject subject, String user,
                                            Connection conn, boolean reauth,
                                            Object properties) throws SQLException {
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT 'GuardAppEvent:Start','GuardAppEventUserName:");
    sql.append("' FROM SYSIBM.SYSDUMMY1");
    Statement stmt = conn.createStatement();

At first glance, it might seem odd to retrieve the principal name from the thread via a static call to WSSubject, as there is already a subject and even a user name passed in. But looking at it again, this is the subject used by the J2C layer, (subject of the technical user) for authenticating to the database.

The user that is passed in is null in most cases. It is only set in cases where the application uses component-based authentication providing the username and password directly, which would again be the technical user.

By implementing doConnectionCleanup(Connection conn), you are also able to inform the Guardium system whenever the connection associated to the current user is released. The only difference is that the dummy SQL statement sends 'GuardAppEvent:Released' instead of 'GuardAppEvent:Start', as shown in Listing 2.

Listing 2. Transmitting the current application user name on connection clean up
public boolean doConnectionCleanup(Connection conn) throws SQLException {
StringBuffer sql = new StringBuffer();
sql.append("SELECT 'GuardAppEvent:Released','GuardAppEventUserName:");
sql.append("' FROM SYSIBM.SYSDUMMY1");
Statement stmt = conn.createStatement();
return super.doConnectionCleanup(conn); 


  1. After compiling and jarring the DataStoreHepler, place the jar into the /lib folder of your WAS installation.
  2. Log in to the WAS Administrative Console.
  3. In the configuration page for the DataSource, select Specify a user defined data store helper, and then provide the full package and classname of your DataStoreHelper class, as shown in Figure 2.
    Figure 2. Specifying a custom DataSourceHelper class
    technical user showing the DataSource config dialog for specifying a custom DataSourceHelper
    technical user showing the DataSource config dialog for specifying a custom DataSourceHelper
  4. Save your settings and restart the WebSphere Application Server. The DataStore helper configuration is finished and ready to use.


For this article, the configuration was tested by opening two browser windows, and the application was logged into with two different users called marc and sven. The application itself is connected to the database via a technical user because connecting an application to the database using the instance owner is usually not recommended in the majority of scenarios. The application then queries a sensitive table. As you can see from Figure 3, all of the queries come from the same database session (3719) using the same technical user.

Figure 3. Guardium Activity log assigning the front end user to the database queries
technical user showing a typical topology using a technical user for the database connections
technical user showing a typical topology using a technical user for the database connections

Also, notice that the front-end / application user is clearly marked in the Event User Name column and assigned the statements he executed in his (application) session. Mission accomplished.

Possible extensions

The current metadata that is being transferred to the database applies to a DB2 database system, since the dummy select is issued against SYSIBM.SYSDUMMY1. For other database systems, this statement would have to be adapted to reflect the dummy table that comes with the respective DBMS. Examples include the following.

  1. DB2: SELECT 'GuardAppEvent:Start','GuardAppEventUserName:[insert username]' FROM SYSIBM.SYSDUMMY1
  2. Oracle: SELECT 'GuardAppEvent:Start','GuardAppEventUserName:[insert username]' FROM DUAL
  3. MS-SQL: SELECT 'GuardAppEvent:Start','GuardAppEventUserName:[insert username]'
  4. PostgreSQL: SELECT 'GuardAppEvent:Start','GuardAppEventUserName:[insert username]'

Alternative solutions

You can use the following alternative solutions to this challenge.

  • Application logging.
  • Re-authentication / trusted contexts.
  • Log collection and timestamp-based merge.

Application logging

Since the application authenticates and authorizes all access to the application, it appears that the application itself would be a sensible place to monitor and log all data access.

There are several drawbacks to this approach though. First, application logging needs to be consistent. For example, if during development the logging requirements change or if development accidentally omits a logging process, the resulting log is much less useful. Also, logging exactly what needs auditing is no trivial task because each logged database access would need to include the application user, and the application would need to determine on its own which database access requires auditing.

Second, leaving database activity monitoring to a dedicated solution directly at the database server has the added advantage that the access which occurs outside of the application, such as console access through privileged users or access through JDBC or ODBC bridges, would still be monitored by these agent-based solutions. A comprehensive auditing solution that monitors 100% of all database access is required by most compliance regulations.

Re-authentication and trusted context

Current database systems support mechanisms for setting the effective owner of a database connection. With DB2, a developer can choose between re-authenticating a connection, which is typically much more efficient than opening a new one, or taking advantage of so-called trusted contexts, such as the ability of the database to trust the (previous) connection owner to authenticate a different user and replace himself with this different user.

Both approaches would fulfill the requirement that database activity should be traceable back to a certain user because they are vendor-specific and usually incur additional management and configuration. This is often not feasible, even for smaller applications.

Log collection and timestamp-based merge

This approach aims to consolidate activity from different sources, such as the database and the application server, by comparing timestamps.

Since this can only be a heuristic approach, it will inherently not be able to match sources with 100% confidence. So this article will not further elaborate on its advantages and disadvantages as it is not a feasible approach for the strict compliance scenario outlined previously.

The disadvantages outlined for the different approaches warrant another approach, specifically tailored to the compliance scenario, such as transmitting the application user as metadata to the database per transaction.

This metadata is ignored by the database and does not impact the database management system, or the database connection pooling at the application server. But this is, nonetheless, monitored by database activity monitoring systems like InfoSphere Guardium Database Activity Monitor.


This article gave you a generic approach for WebSphere Application Server applications that let you enable database activity monitoring solutions like InfoSphere Guardium to reliably assign an application user to the database activity without requiring changes to the respective applications.

Downloadable resources

Related topics


Sign in or register to add and subscribe to comments.

Zone=Information Management, Security
ArticleTitle=Monitor database activity for application users with Guardium and WebSphere Application Server