InfoSphere Guardium application user translation

Five methods for identifying application end users associated with database activity

Many organizations today have data auditing requirements that make it mandatory to be able to identify which application end users are executing specific database transactions. In an environment where a connection pool is being used to enhance performance, it can be a challenge to identify the end user. Learn five methods for solving this fundamental governance problem using IBM® InfoSphere® Guardium®, and understand which of these methods is most applicable for your environment.

John Haldeman (john.haldeman@infoinsightsllc.com), Software Engineer, IBM

photo of author John HaldemanJohn Haldeman is part of the Optim and Guardium Technology Ecosystem team out of the IBM Toronto Lab. The Technology Ecosystem team provides technical assistance to IBM business partners throughout the sales lifecycle: from demos and POCs, through to implementation and best practices.



Benjamin Leonhardi (benleon@ca.ibm.com), Software Engineer, IBM

Photo of author Benjamin LeonhardiBenjamin Leonhardi is a Software Engineer in the InfoSphere Warehouse and Netezza Technology Ecosystem team at the IBM Toronto Lab. Before that he was a Software Developer for InfoSphere Warehouse at the IBM Research & Development Lab Boeblingen, Germany. He was a developer in the data mining, text mining, and mining reporting solutions.



February 2012 (First published 26 May 2011)

Also available in Russian Japanese

Introduction

InfoSphere Guardium is an IBM database activity monitoring system. It provides low impact auditing and security for databases across the organization. With it, organizations can track what is occurring inside the database at a fine-grained level of detail with a minimal amount of invasiveness and performance impact.

Application user translation is the process of identifying on which application the end users are executing which database transactions. It requires the tracing of activity from the end user, through the application layer, and down into the database. Because creating a database connection is a relatively expensive operation, there would be a large performance impact if a new database connection had to be created each time an application user logged in. To get around this problem, application servers create a pool of reusable connections, rotating through them for application transactions. When a connection pool is created, a single and powerful database user is used to log into the database. The application then assumes the responsibility of access control for any data drawn from the database using the connection pool.

Figure 1. Database access through a connection pool
Diagram Showing Three Application Users: fbrooks, kzuse, and aturing Being Consolidated Into a Single Database User: APPS Via a Connection Pool

The result of connection pooling is a much faster application. Unfortunately, it also results in a loss of information concerning which of the application end users executed a specific database transaction. This is a problem. Identifying application end users is crucial for any database environment that has strict auditing requirements. For example, in the health care industry, understanding which end users are accessing Electronic Protected Health Information (EPHI) can be a compliance requirement for the Health Insurance Portability and Accountability Act (HIPAA) in the United States. In the financial sector, application user identification improves financial tracking and accountability, which can help in complying with the Sarbanes-Oxley Act of 2002 (SOX).

This article outlines how to use five different methods to configure InfoSphere Guardium to solve this problem. It also examines how to choose the best method for a specific application.

At a high level, the five methods of application user identification in Guardium are as follows:

  1. Built-in application user translation
  2. Identifying user switching using the Guardium Application Event API
  3. Analyzing patterns in stored procedures
  4. Application server-based S-TAP agents
  5. Database and application server APIs

Built-in application user translation

Guardium supports a variety of out-of-the-box applications. These applications include Oracle E-Business, PeopleSoft, Siebel, SAP, and Business Objects. The Guardium development organization performed research into each of these applications and looked for patterns in the SQL that the applications execute. They found that each one consistently identifies its users in the SQL statements that are executed during a transaction. By identifying these patterns in the SQL, Guardium can extract which application end user is executing a database transaction.

Example with Siebel

To help demonstrate this functionality, this article examines Siebel CRM Application User Translation in detail. Whenever a transaction occurs in Siebel, a field indicating the end user is updated for the affected tables. Guardium can be configured to extract that information during the Siebel Transaction.

For example, when you update a revenue number in Siebel, an SQL UPDATE transaction is recorded in Guardium similar to the following:

UPDATE SIEBEL.S_REVIN 
  SET DB_LAST_UPD_SRC='User'.DB_LAST_UPD=current timestamp-current timezone, 
  LAST_UPD='2-11-04-19.49.43.000000',LAST_UPD_BY='8SIA-7ZPPT',
  MODIFICATION_NUM=0000000000000000,ACCNT_ID='1-4YR' 
  WHERE ROW_ID='UA1-1V1TV AND MODIFICATION_NUM=0000000000000000

Figure 2 illustrates how this SQL is recorded in Guardium.

Figure 2. The UPDATE SQL statement run by Siebel CRM when updating revenue numbers
Guardium screen with SQL ID, timestamp, session ID, server IP, source program, db user name, and full SQL

Guardium can be configured to extract the user ID (8SIA-7ZPPT) and the user name (FBROOKS) for this statement. This is shown in Figure 3:

Figure 3: Guardium extracting the user ID from the Update statement and mapping it to a user name
Guardium Extracting the User ID from the Update Statement

This example shows how, by configuring Guardium correctly, auditors and compliance professionals can easily view which application user is executing which database transaction.

Configuration

Now that the Siebel example has been explored, this article examines built-in application user translation configuration. Configuration for Oracle E-Business, PeopleSoft, Siebel, SAP, and Business Objects differs, but configuration for each one has some common threads.

The first step is to configure the translation process inside the Guardium Administration Console as shown in Figure 4.

Figure 4: The Application User Translation menu in the Administration Console
Screen shot of Administration Console with application user translation highlighted

A series of parameters for the application's database are specified in the Application User Translation Panel. If Guardium requires a connection to the database in order to import user ID to user name mappings, a database user name and password will also need to be supplied (this is required for Oracle E-Business and Siebel). When the user names are exposed inside the SQL patterns directly, only the IPs and port numbers of the application need to be specified, for example, with PeopleSoft and Business Objects.

Figure 5: Example configurations for Siebel, Business Objects, Oracle E-Business, and PeopleSoft
Figure 5 shows the example configurations for Siebel, Business Objects, Oracle E-Business, and PeopleSoft

Once these parameters have been entered into the Guardium Administration Console, the Guardium inspection engine will have to be restarted in order for application user translation to take effect, as shown in Figure 6.

Figure 6: The Inspection Engine Configuration in Guardium
The Inspection Engine Control in Guardium. This is Where the Inspection Engines Would be Restarted

This is all that would be required for PeopleSoft and Business Objects. Oracle E-Business and Siebel require the extra step of importing the mappings from Application user IDs to Application user names. In order to import the mappings, an Application User Translation Import Process must be run, and then scheduled to run on a regular basis. The run controls for this process can be found in the Administration Console as shown in Figure 7.

Figure 7: Configuration dialog for mapping import
Configuration Dialog for Mapping import

Once built-in Application User Translation is configured and running, you can look at some pre-packaged Guardium reports to see application user activity. For example, for Oracle E-Business, the EBS Processes Database Access and EBS Application Access reports are provided. You may need to populate some groups in Guardium for these reports to run correctly. To see which groups require population, view the Reports Query Definition as shown in Figure 8.

Figure 8: Query definition for the EBS Application Access report
Query Definition for the EBS Application Access Report. For this Report to Run Correctly, Two Groups must be Populated: EBS App Servers and EBS DB Servers

You can also build your own reports that display application user data. To do this, create a report and add the App User Name fields from the Access Period and App User Name entities. When you view the reports, make sure that Aliases are turned on.

Some special notes on SAP

Inside the Application User Translation configuration panel, SAP has an entry in the Application Type List (SAP Observed). Configuration using the panel is not needed for SAP Application User Translation to work. It is kept in the Administration Console only for historical reasons. Instead, to configure Application User Translation for SAP, you need to populate the SAP App Servers, and SAP DB Servers groups in Guardium, as shown in Figure 9.

Figure 9: The two groups to populate in order to configure SAP Application User Translation
The Two Groups to Populate in Order to Configure SAP Application User Translation

Once populated with the application server and database server IPs, the inspections engines will have to be restarted in order for application user translation to take effect.

DB methods

There is an additional option for Siebel and SAP that has not been discussed yet, which is DB-based translation, as shown in Figure 10.

Figure 10: DB Application User Translation selected in the Administration Console for Siebel and SAP
DB Application User Translation can be selected In the Administration Console for Siebel and SAP

No translation actually occurs here. Guardium will simply import audit data already being captured by the application. This is an older, more invasive, method and as such, observed methods are the recommended configuration. If DB methods must be used instead of observed, the auditing functions of SAP and Siebel must be enabled. For Siebel, this means that the Docking: Transaction Logging parameter must be set to TRUE. For SAP, the rdisp/vb_delete_after_execution parameter must be set to 2. Additionally for SAP, the rsm13002 transaction with DELETE must be run on a regular basis to purge the report.


The InfoSphere Guardium Application Event API

Now that built-in Application User Translation has been explored, you will examine the Guardium Application Event API. Unlike built-in Application User Translation, the Guardium Application Event API will work with any application, provided that the application can be configured or modified to execute SQL statements when control of a session is shifted from one application user to another.

The Application Event API calls are simple no-operation (no-op) SQL statements that are executed before and after an application user takes charge of a connection in a connection pool. The no-op calls tell Guardium what application user is currently using the database session. The calls perform no change on the database, but can be detected by Guardium. Normally, these no-op calls are executed on single row special tables such as DB2 LUWs SYSDUMMY1 table, or ORACLE DBs DUAL table.

For application user translation, the Guardium AppEventAPI call normally takes the form of:

SELECT 'GuardAppEvent:Start',  'GuardAppEventUserName:user_name' FROM DUMMY_TABLE;
-- SQL Transactions related to the user named user_name
SELECT 'GuardAppEvent:Released'  FROM DUMMY_TABLE;

The concept can be easily demonstrated using database command line tools. Consider running the following SQL code in the DB2 LUW command line interface:

SELECT 'GuardAppEvent:Start',  'GuardAppEventUserName:fbrooks' FROM SYSIBM.SYSDUMMY1;
SELECT count(*) FROM ITEMS;
SELECT 'GuardAppEvent:Released'  FROM SYSIBM.SYSDUMMY1;
SELECT 'GuardAppEvent:Start',  'GuardAppEventUserName:kzuse' FROM SYSIBM.SYSDUMMY1;
SELECT count(*) FROM ITEMS; 
SELECT 'GuardAppEvent:Released'  FROM SYSIBM.SYSDUMMY1;
Figure 11: Executing the Guardium Application Event API from the DB2 command line in Linux
Figure 11 shows how the Guardium Application Event API from the DB2 Command Line in Linux is excecuted

As shown in Figure 12, the session in Guardium shows how the user fbrooks is attributed to the first SELECT count(*) statement. It also shows how the user switches to kzuse for the second SELECT count(*) statement.

Figure 12: Attributing a SELECT count(*) statement to the application user
Attributing a SELECT count(*) Statement to the Application User fbrooks Using the Guardium Application Event API, and Then Switching the Application User to kzuse

Normally the Application Event API requires an application change before it can be used. Customers need to modify application code to execute the no-op SQL whenever an application user switches for a session in the connection pool. There are some applications, however, that are flexible enough to allow you to execute the no-op SQL calls with no code changes.


Application user identification via stored procedures

Application user identification via stored procedures is similar to the Guardium Application Event API discussed in the previous section in that it uses SQL statements to define boundaries between users in a single database session. This time, stored procedures are used instead of a null SQL statement. If the application executes a stored procedure in a predictable way when the application user switches inside a pooled connection, Guardium can be configured to pick this up and assign attributes to the user.

For example, as shown in Figure 13, consider if an application executes the set of statements inside a single database session.

Figure 13: An Oracle database session with application user boundaries defined using a call to the set_application_property stored procedure
Figure 13 shows an example of an Oracle database session displayed in Guardium. User boundaries are defined using a call to the set_application_property stored procedure

A call to set_application_property with the parameter user_name is made each time an application user switches in the pooled connection. Guardium can be configured to detect this stored procedure call and use the second parameter, as shown in Figure 14, as the current application user name.

Figure 14: Guardium output when configured to identify the stored procedure as a boundary for application users
Guardium populating an application user field after having identified a call to set_application_property as a switch in a user

Configuration

in order for Guardium to identify a user by the stored procedure call shown previously, a new custom ID procedure in the Guardium Administration Console needs to be defined. The procedure name would be the name of the stored procedure that is called by the application when an application user switches, in this case set_application_property. In order to avoid the calls to this same procedure that have user_date as the first parameter, you can specify that the first parameter must be user_name by creating a condition value for that first parameter. Finally, you would specify that the second parameter would act as the Username field in the Application Event Entity. You can then add the Application Event Username field to your reports to view the application user, as shown in Figure 15.

Figure 15: The Guardium Stored Procedure Configuration window
Figure 15 shows the parameters required to configure Guardium in this case. Identifying set_application_property as the stored procedure, and adding a condition that the first parameter must be 'user_name'. The position of the actual username is also specified

This method of application user identification is ideal for applications that use stored procedures heavily, or create user boundaries by calling stored procedures. Also, there are some applications that may not be flexible enough to call no-op select statements to take advantage of the Guardium Application Event API, but can be configured to make the appropriate stored procedure calls. This would be an ideal method for those applications as well.


Application Server S-TAP

There are situations when it is not possible or desirable to change the application source code, so the Guardium Application Event API cannot be used. The application may also not use stored procedures to define user boundaries. In some of these cases the ability of the Guardium S-TAP to monitor network traffic can be used to identify application users. In this scenario the S-TAP is used to correlate application end users with database activity. An S-TAP is installed on the machine that hosts the application server, not the database server. It is then configured to monitor incoming HTTP traffic to the application server, filter out user names and correlate them through an application server session id with database activity. In Guardium this is called Application Server User Identification or Application Server S-TAP.

This approach is advantageous because it works without changing the application or reconfiguring the application server. It is therefore a very fast and easy solution. The disadvantages are that it will also not work with all enterprise applications. Possible problems include incompatible authentication mechanisms that encrypt or hash user names. Application Server S-TAP will work best for standard Java Enterprise Applications using a type of Form Login authentication.

Analyzing the application

The first step to configure Application Server S-TAP is to analyze the application that is supposed to be audited.

You need to find out the following three things:

  • What is the HTTP port of the application server
  • How can you identify the application user name in the HTTP traffic during login
  • How can you identify the Java Session id that is correlated to the user name

While there are a variety of available tools to analyze web traffic, one of the easiest to use is a Firefox plugin called Live HTTP headers. After it is installed, it is easy to see the HTTP traffic that is transmitted during a web login, as shown in Figure 16.

Figure 16: Login screen for the plants by Websphere application
Login Screen for the Plants By Websphere Application

For this article you will use the Demo Application Plants By Websphere as an example. When you log in to the application with the username jon@doe.com, you will see, as shown in Figure 17, the HTTP traffic in the Live HTTP headers window.

Figure 17: HTTP traffic for login
HTTP traffic for Login

In the Plants By Websphere application, the HTTP call you are interested in is the Post call to the AccountServlet, with the parameter action=login. You can see that the username is transmitted in the content of the HTML POST call. The actual username has a prefix of userid=, and a postfix of &.

The Java Session id is transferred in a cookie with the call, it has a Prefix of JSESSIONID= and a Postfix of &.

Configuring Application Server S-TAP

To enable Application Server S-TAP you need to configure the Guardium collector with the information you retrieved from the HTTP stream, as shown in Figure 18.

Figure 18: The Guardium Administration Console
The Guardium Administration Console

The configuration takes place in the Guardium Administration Console under Administrative Console, Local Taps, S-TAP control. This contains all major configuration settings for the S-TAP, as shown in Figure 19. For this to work, when you configure the S-TAP, it needs to be installed on the machine hosting the application server not the database server.

Figure 19: Configuration for Application Server S-TAP
S-TAP configuration for Application Server S-TAP

Figure 19 shows the configuration of the Application Server S-TAP for the Plants By Websphere example. These settings correspond to the values you have seen in the HTTP traffic. The Ports value is the HTTP port number of the application server hosting the application. The application user name is transmitted with the prefix userid= and the postfix & by the web application. The database access is transmitted with the prefix JSESSIONID= and the postfix & in a HTTP cookie for the Session id that you need to correlate the application user with. The pattern entries are used by Guardium to verify the location in the HTTP stream.

After changing the parameters, you need to apply the changes. The S-TAP will then restart, and the status should be good.

Figure 20: Application server S-TAP report
Application Server S-TAP Report

After the Application Server User Identification has been configured, Guardium will store the application username in the field Application User, in the entity Access Period. You can add this field to reports. In Figure 20 shown previously, you can see a report that contains the DB user name which is the user associated with the JDBC Datasource and the Application User, which is the actual application user used during login.

Application Server S-TAP summary

Application Server S-TAP is a very easy way to identify application users for Java Application Server applications when built-in functionality is not available. It doesn't require any changes to existing applications which makes it a very attractive solution. Nevertheless some considerations need to be taken into account.

For Application Server S-TAP to work, the username needs to be transmitted in clear text in the HTTP stream. Some authentication mechanisms, for example, regular HTTP based authentication are hashed or Base64 encoded and are not supported. In general Form Logins will transmit the username in clear text.

This method will not work with all applications. It depends on the way that connection pooling is used. For example, it requires that the access to the database is synchronous with the same process or thread that is handling the HTTP request. A good example is a servlet-based application where a servlet processes HTTP requests, and in the process receives a connection from a connection pool to access the database.

HTTPS connections are also not supported. This is normally not a crucial problem because the S-TAP is installed on the application server. In normal enterprise configurations, only traffic to the web server is HTTPS encrypted and the connection between web and application server is standard HTTP.


Database and Application Server APIs

Guardium is not the only auditing system to run into the problem of propagating application users down into the database layer from the application layer. Many native database auditing solutions have the same problem. These solutions solved it by adding additional APIs to their systems. Over time, application servers were built to take advantage of these APIs so that auditing could be performed with a minimal amount of application development effort. The good news is that Guardium has been designed to detect the use of these APIs and assign the appropriate application user accordingly.

Examples with DB2

To illustrate this process, examples with the IBM DB2 LUW database and WebSphere Application Server (WAS) will be discussed. The first example is DB2 LUW implementation of the standard JDBC Connection.setClientInfo API. Consider an application that uses the setClientInfo API as follows:

Class.forName("com.ibm.db2.jcc.DB2Driver");
Properties props = new Properties();
props.setProperty("user", "guard");
props.setProperty("password", "password");
Connection  connection = DriverManager.getConnection("jdbc:db2://10.10.9.28:50001/orders"
, props);

connection.setClientInfo("ClientUser", "fbrooks");
execSelectOnItems(connection);

connection.setClientInfo("ClientUser", "kzuse");
execSelectOnItems(connection);

This is somewhat of a contrived example. In a real application, hard coding of user names like this would not occur. Instead the application user would be inserted as a variable. The user is likely to be identified through a session ID. The constants fbooks and kzuse are simply used here to help demonstrate the concepts. The execSelectOnItems method runs a simple SELECT statement against the database that gets the number of records in a table named ITEMS. Guardium picks up the database traffic and assigns any SQL executed after the call to setClientInfo("ClientUser", "fbrooks") to the user fbrooks. No configuration is required, as shown in Figure 21.

Figure 21: How the setClientInfo calls appear in Guardium
The setClientInfo calls in DB2 translate to SET CLIENT USERID calls in Guardium

The second example is with DB2 connection pooling using trusted contexts. Trusted contexts allow for DB2 LUW to be configured to trust an IP/Username pair. It then allows you to optionally skip re-authentication and re-connection during a switch of database user names. Unlike the setClientInfo API, switching the user name through trusted contexts will allow DB2 to enforce any access controls associated with that user. The actual DB2 user is being switched. With trusted contexts, you get both high performance and the ability to use all the access control mechanisms that come with modern databases. Another great thing about trusted contexts is that Guardium will pick up the user switch. To Guardium, the switch looks like a special kind of connect statement. To illustrate this, take the following code that would be executed in the application server.

Object[] objects = new Object[6];
Properties properties = new Properties();
byte[] cookie = new byte[1];
Connection con;
DB2ConnectionPoolDataSource ds1 = 
new DB2ConnectionPoolDataSource();	      
ds1.setServerName("10.10.9.28");
ds1.setPortNumber(50001);
ds1.setDatabaseName("orders");
ds1.setDriverType (4);
	 
objects = ds1.getDB2TrustedPooledConnection("guard", "password", properties); 
DB2PooledConnection pooledCon = (com.ibm.db2.jcc.DB2PooledConnection)objects[0];
cookie = (byte[])objects[1];
con = pooledCon.getDB2Connection(cookie, "fbrooks", null, null, null, null, properties);
execSelectItems(con);
con = pooledCon.getDB2Connection(cookie, "kzuse", null, null, null, null, properties);
execSelectItems(con);

Execution of this code in an application server would look like the example in Figure 22 in Guardium.

Figure 22: How user switching through trusted contexts appears in Guardium
Trusted Context look like special CONNECT statements to Guardium. Guardium picks this up and identifies the application user (which is also the database user at this point)

The DB User Name field specifies the user that the application server originally logs in with. This is the trusted user for the trusted context. The application user field specifies the current user used in the connection pool. The line between database and application user starts to become blurred as kzuse and fbrooks are really database users with database level privileges, but are also application users. Similar APIs exist for other databases, and Guardium should be able to pick most these up as well.

Both the trusted context and setClientInfo example shown previously dealt with how applications might access the APIs directly. For most enterprise applications, they leave these kinds of calls to the application server. WebSphere is one such application server that can make the calls. WebSphere using DB2 trusted contexts automatically is discussed in the next section.

Example with WebSphere

You can configure WebSphere 7 to using DB2 trusted contexts inside the login configurations of an application's resource references. When you do this, WebSphere can be configured to take advantage of trusted contexts automatically, as shown in Figure 23. Detailed instructions on how to configure this can be found in the resources section.

Figure 23: A data source resource reference configured to use trusted contexts in WebSphere
Figure 23 shows an example of how a data source resource reference that is configured to use trusted contexts in WebSphere

You can also Configure WebSphere to use plug-in like data store helpers to call either the Guardium Application Event APIs or a database API. The developerworks article Developing Special DataStoreHelper Plugins for WebSphere to insert Pre and Post SQL in the resources section describes this in more detail. WebLogic has similar functionality as well. It calls the process Credential Mapping. A resource for how to configure WebLogic for Credential Mapping can also be found in the resources section.


Conclusion

One of the key problems of database auditing software is the identification of application end users associated with database activity. The problem occurs because, for performance reasons, application servers implement database connection pools.

You have learned of five different techniques for the identification of application users in Guardium. Each of them have their strength and weaknesses and are applicable in some situations, like the following.

  • For supported applications like Siebel or SAP, built-in Application User Translation is, of course, the best course of action.
  • If it is possible to modify the SQL calls of the application, Guardium Application Event API is the most stable and robust way to ensure that application users are tracked.
  • For many applications that use Stored Procedure calls for their own user management, analyzing patterns in stored procedures is a viable solution.
  • If it is not possible to change the application source code, and the application in question is a standard Java Enterprise Application, then Application Server S-TAP may be a quick and easy way to solve the problem.
  • If the Application Server and Database support APIs for application user propagation, this can be enabled.

In this article we have described the problem of Application User Identification in Database Auditing software. We have given an overview of the available techniques for application user identification in Guardium, and we demonstrated how they can be implemented. We also provided a guideline as to when each of them should be used.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently..

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

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

 


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

All information submitted is secure.

Choose your display name



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

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

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

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

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Security
ArticleID=660828
ArticleTitle=InfoSphere Guardium application user translation
publish-date=02262012