Audit of database operations for front-end users in Java EE applications

Introduction

In some systems with high requirements for security audits, administrators need to see what database operations are performed by the logged-on user of each application, and usually when our application accesses the database, the authenticated user of the same database is used to obtain the database connection, so that the identity of our logged-on user cannot be delivered to the database end. Many customers need to be able to audit logged-in users at the database end. Of course, in the application server-side of the program to write a log, the record of each user to perform what actions can also meet the audit requirements, but this approach often has the cost of performance. Through practice, this article introduces the solution is to pass the user identity through the database connection to the database side, thus completes the audit at the database end, this is a lighter level of the way.

Solution Brief

Prior to JDBC 4.0, the JDBC specification did not provide an API to pass user identities, and we could only implement them through the API provided by the database manufacturer. Given the practicality of this requirement, JDBC 4.0 adds a corresponding API for us. Currently, Oracle 11g Release 1 (11.1) and DB2 9.5 JDBC Driver support the JDBC 4.0 specification, but in prior versions, we can only rely on vendor-supplied APIs. This article describes some of the practices that use these APIs and how to view the user identities passed over the database side.

The basic mode for passing user identities is:

Database Sessions (session)

The JDBC framework (or the O/R mapping framework), such as Hibernate and IBatis, provides the concept of session, which is the encapsulation of database connections and transactions. A connection is typically used during a database session, corresponding to a transaction.

For pure JDBC, getting a database connection is equivalent to opening a session.

Open a database session.

Set up user identification information.

Perform some database operations.

Clears the user identity on the connection.

Closes the database session.

It is important to clear the identity on the connection because the database connection that we typically use is a logical connection, and its corresponding physical connection (TCP/IP connection) is not closed after the logical connection is closed, so clearing the identity information on the connection ensures that no other database logical connections are affected.

The support provided by JDBC 4.0

Java 6 supports the JDBC 4.0 specification, which provides support for the delivery of user information on database connection java.sql.Connection in JDBC 4.0. There are two methods available in this interface:

void　setClientInfo(String　name,　String　value)　throws　SQLClientInfoException;
void　setClientInfo(Properties　properties)　throws　SQLClientInfoException;

The first method allows us to pass three properties on the Connection:

ApplicationName: The name of the application that accesses the database.

Clientuser: The user identity that accesses the database is different from the user who established the database connection. The user who establishes the database connection is the user who can be authenticated and authorized by the database.

Clienthostname: Accesses the host name of the database client.

The second method is similar to the first method, except that the parameter is placed in a Properties object. We usually setclientinfo ("Clientuser", userId) attach the user identity to the database connection. A common pattern for using this method is:

Listing 1. Passing user identities using the JDBC 4.0 API

Connection　conn　=　getConnection();
conn.setClientInfo("ClientUser"　,　currentUserId);
//do　something　on　the　connection　
conn.setClientInfo("ClientUser"　,　null);
conn.close();

Note that the way to clear the user identity on a connection is to empty the identity. Here are two common database types that we'll use to introduce the method of identity delivery.

Identity Delivery for DB2

DB2 provides com.ibm.db2.jcc.DB2Connection, a class that supports user information delivery in the following ways:

public　void　setDB2ClientUser(String　s)　throws　SQLException;
public　void　setDB2ClientWorkstation(String　s)　throws　SQLException;
public　void　setDB2ClientApplicationInformation(String　s)　throws　SQLException;
public　void　setDB2ClientAccountingInformation(String　s)　throws　SQLException;