Jan 1 ’07

Web-Exposed Databases

by Editor in z/Journal

Increasing scrutiny of database activity, fueled by regulatory requirements, is exposing a rarely discussed hole in database activity auditing: invisible users. This blind spot is especially troublesome for organizations that must comply with regulations that mandate database access auditing, including section 404 of the Sarbanes-Oxley Legislation, section 10 of the PCI (Payment Card Industry) standard, and Title II, Security Rule, and Administrative Safeguards of HIPAA. Native DB2 for z/OS audit logs have no awareness of specific user identities  accessing sensitive database information via Web applications. This includes Web-enabled commercial packages such as SAP, Oracle EBusiness Suite, PeopleSoft, etc. Therefore, when audit logs reveal fraudulent database transactions, there’s no link to the responsible user. The problem isn’t unique to DB2; the issue applies to all database platforms. That’s because the problem isn’t with the database technology itself, but with the design of most Web applications.

The Problem With Web Applications

Web application users don’t directly access the database server. Rather, a browser request invokes Web application server code that performs the required database transaction on the user’s behalf. To accomplish this, the Web application doesn’t access a unique database account for each user; instead, a process known as connection pooling is applied as follows:

  1. The Web application server creates a relatively small set of database connections (called “the pool”) using a single database account commonly referred to as the “application account.”
  2. When a Web server thread that processes a user request needs database access, it picks an arbitrary connection from the pool, sends the request over this connection, receives the response, and releases the connection back to the pool.

Connection pooling lets a database service thousands of concurrent user sessions with only a handful of database connections. Moreover, with a pool of available connections, the Web application can quickly recover from a single connection failure without any noticeable effect on response time. This approach is exercised by Internet-facing applications and internal enterprise Web applications such as SAP, Oracle E-Business Suite, PeopleSoft, etc. Unfortunately, pooled connections have two negative characteristics for the database auditor:

  1. All user transactions are aggregated in a single database account identified only by the Web application account name (see Figure 1). The database receives no information regarding the actual user’s identity.
  2. Multiple user sessions are multiplexed into a single database connection while simultaneously dispersing requests from a single user session across multiple database connections.

Consequently, the database server’s native audit mechanism can’t correlate any single request to a specific user. The resulting audit trail associates all database activity with the generic “application account.” This is problematic because when logs reveal questionable database transactions, there’s no link to the responsible user. Regulatory compliance, security best practices, and common sense all dictate that audit data must establish accountability by linking actions to users. Without such accountability, there’s little value in the audit process.

What can be done to eliminate this blind spot? There are four options to consider: rewriting the applications, using proprietary database mechanisms, relying upon Web application audit information, and using external database audit devices.

Rewriting the Applications

The most obvious approach to identifying each user to the database is to rewrite Web applications so they access a unique database account for each user. Doing so enables native database auditing tools to record each transaction with the appropriate user name. Unfortunately, it’s an unrealistic solution for several reasons:

A Better Approach to Rewriting

Rather than accessing a unique database account for each user, the Web applications may continue to use pooled connections, but be rewritten to embed a reference to user identity in each database request. The database may then be programmed to incorporate the embedded reference information into the audit trail.

This approach is more attractive than using dedicated database user accounts. It has less impact upon database performance and it avoids shifting user management responsibilities to the database administrator. However, it still requires a complex rewrite that extends to both Web and database server code. Such a process introduces all other rewrite-related drawbacks described previously, including third-party software limitations, cost, schedule, and risk.

Proprietary Database Solutions

Some database platforms include built-in mechanisms to track application user identities. Oracle, for example, has a set of such features. Unfortunately, these mechanisms still require rewrite of the Web application to include special database requests in all code modules. Again, this introduces almost all the rewrite drawbacks previously described: third-party software limitations, cost, schedule, and risk. In addition, proprietary solutions are vendor-specific. So, organizations with multiple database vendors won’t be able to solve the problem organizationwide or will be forced to engineer and support multiple solutions.

Web Application Audit Information

Some organizations may attempt to indirectly satisfy database audit requirements to identify users via reliance on Web application audit information. In this case, Web application audit data and database audit data may be combined to build a complete picture. For example, if a suspicious database transaction originates from the “application account,” audit personnel may try to inspect Web application audit data with similar timestamps to identify the perpetrator. This sounds good, but it’s not simple.

At any time, hundreds or thousands of users may be logged into an application. Correlating the timestamps of application and database audit data reduces the number of potential perpetrators, but doesn’t usually deliver a definitive result. At that point, the auditor must match a specific Web request to a corresponding database transaction by interpreting the details of each transaction (Web parameters, query parameters, etc.). This is an extremely laborious and costly process. It also requires extensive Web application and database design expertise. To make matters worse, depending on the nature of the transaction, the entire process may still not deliver a definitive result. Beyond these technical challenges, reliance on Web application audit information to supplement database audit information suffers from other drawbacks:

External Database Audit Solutions

Several vendors have stepped forward with software and appliances to meet the increasing demand for database audit capabilities. These devices offer several advantages vs. native database audit technology, including separation of duties, performance, operational automation, and scalable support for heterogeneous database environments. In some cases, these external audit solutions also offer the ability to identify Web application users. However, with respect to Web application user identification, these solutions take different approaches.

Some external audit vendors apply a mechanism similar to that of proprietary database vendors. They require that Web application code be rewritten to insert special database requests. This again introduces rewrite problems. In addition, they don’t support tiered application environments (where the Web and application servers reside on separate machines). All these approaches seem to require slow, costly, risky application rewrites that are virtually impossible if the source code is owned by a third party.

But there’s hope. Some external audit devices include new technology that solves the invisible user problem without disrupting application infrastructure. These advanced solutions track all Web application and database requests in parallel. The Web application tracking mechanism captures user login information providing the system with direct knowledge of user identity information for each session. Then, by correlating Web requests with database requests in real-time, database audit records are linked to specific users (see Figure 2).

The correlation process carried out by the external audit device is similar to manual post-mortem log analysis, but it’s completely automated and uses a large volume of transactions to profile a statistically reliable relationship between database queries and Web requests. In addition, since external audit devices associate each database request with the actual user in realtime, advanced audit capabilities are enabled that aren’t possible via postmortem log analysis. For example, audit rules may be specified to track access from specific users or user groups. Similarly, access to sensitive tables prior to proper Web application authentication may be detected and investigated.

Finally, advanced external audit solutions require no Web application rewrites, don’t impact performance, support tiered applications, and support mixed database platform environments. So these newer external audit approaches can be deployed quickly, affordably, and without risk to application availability (see Figure 3). Currently, external audit solutions can’t monitor direct mainframe access, such as a CICS application accessing DB2 data. For now, DB2’s built-in audit mechanisms are the only option for tracking local access on the mainframe.


Any organization serious about the integrity of their database audit process should investigate this issue. How valuable is an audit process that can’t identify the source of a suspicious or malicious transaction? Lack of personal accountability defeats the purpose of an audit and severely handicaps remediation procedures in the event of a breach. Best practices, regulatory compliance, and common sense dictate that the database audit process establish user accountability. Web application rewrites, proprietary database mechanisms, and Web application audit information may all be applied to help solve the problem. However, new external audit devices promise to provide the least disruptive, most cost-effective approach. Z