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

Certain audit requirements mandate that specific database activity can be traced back to the user responsible for the specific activity. This is especially challenging in application scenarios where pooled database connections are used and the application itself is responsible for authentication and authorization. This article presents a generic approach for WebSphere® Application Server applications that enables database activity monitoring solutions like InfoSphere® Guardium® to reliably assign the application user to the database activity without requiring changes to the respective applications.

Sven Herschel (sven.herschel@de.ibm.com), Certified Guardium Technical Sales Professional, IBM

Photo of authorWith nine years of experience as an IT specialist, software architect and requirements engineer with a strong background in databases, web development, and digital certificates, Sven Herschel joined IBM as a Client Technical Professional for InfoSphere Guardium. He is responsible for the entire pre-sales cycle from first contact to on-site proof-of-concepts. In this role, he has first-hand experience with customer requirements regarding database security and compliance.



Marc Schwind (Marc.Schwind@de.ibm.com), Software engineer, IBM

Photo of authorMarc Schwind is a Software Engineer at the IBM Research and Development Lab in Boeblingen, Germany. He has nine years of experience as a developer in the areas of J2EE, WebSphere Application Server, business integration, and Business Process Management. During his development career he was a member of the Business Process Management SWAT team, advising customers on best practices and providing vital support in critical customer situations worldwide. Since 2011, he leads a development team for the IBM BPM offering and currently focuses on integration with Enterprise Content Management Systems. Marc holds an Engineering degree in Information Technology and joined IBM in 2003.



23 August 2012

Also available in Chinese Russian Portuguese

Overview

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

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

Overview

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.

Prerequisites

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 com.ibm.websphere.security.auth.WSSubject class API.

Implementation

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(WSSubject.getCallerPrincipal());
    sql.append("' FROM SYSIBM.SYSDUMMY1");
    Statement stmt = conn.createStatement();
    stmt.execute(sql.toString());
}

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(WSSubject.getCallerPrincipal());
sql.append("' FROM SYSIBM.SYSDUMMY1");
                
Statement stmt = conn.createStatement();
stmt.execute(sql.toString());
return super.doConnectionCleanup(conn); 
}

Configuration

  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
  4. Save your settings and restart the WebSphere Application Server. The DataStore helper configuration is finished and ready to use.

Result

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

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.


Conclusion

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.


Download

DescriptionNameSize
Sample source code for this tutorialSampleGuardiumDataStoreHelper.zip10KB

Resources

Learn

Get products and technologies

Discuss

  • Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

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

 


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

All information submitted is secure.

Choose your display name



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

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

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

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

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Security
ArticleID=830920
ArticleTitle=Monitor database activity for application users with Guardium and WebSphere Application Server
publish-date=08232012