InfoSphere Guardium application user translation
Five methods for identifying application end users associated with database activity
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
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:
- Built-in application user translation
- Identifying user switching using the Guardium Application Event API
- Analyzing patterns in stored procedures
- Application server-based S-TAP agents
- 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 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
This example shows how, by configuring Guardium correctly, auditors and compliance professionals can easily view which application user is executing which database transaction.
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
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
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
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
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
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
SAP App Servers, and
SAP DB Servers groups in Guardium, as shown in
Figure 9: 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.
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
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
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
DB2 LUWs SYSDUMMY1 table, or ORACLE DBs DUAL
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
As shown in Figure 12, the session in Guardium shows how the user fbrooks
is attributed to the first
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
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
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
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
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
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 firstname.lastname@example.org, you will see, as shown in Figure 17, the HTTP traffic in the Live HTTP headers window.
Figure 17: HTTP traffic for login
In the Plants By Websphere application, the HTTP call you are interested in
is the Post call to the
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
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 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
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
& 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
After the Application Server User Identification has been configured,
Guardium will store the application username in the field
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
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 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; Properties properties = new Properties(); byte cookie = new byte; 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; cookie = (byte)objects; 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
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
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.
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.
- Find out more about InfoSphere Guardium in the IBM InfoSphere Guardium library
- Get more information on enabling trusted contexts for DB2 databases
- How to Configure Mashup center to use DB2 trusted context through WebSphere
- Learn more about developing special DataStoreHelper Plugins for WebSphere to insert pre and post SQL.
- Read about Database identity propagation in WebSphere Application Server V6
- Learn more about enabling JDBC credential mapping in Weblogic for DB2 and Oracle
- See how to using Cognos command blocks for pre and post SQL
- 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..