Well, I threatened to post something more technical, and here we are. I’ve just come back from a business trip to the US, meeting some prospects and customers. As always, I was asked a lot of technical questions. One of the frequent questions I encounter is – how can we propagate the application user and context information to the database, for monitoring and prevention purposes?

I’m going to answer this question here for a specific technological setup of Java and Oracle, but the solution can be adapted to other technologies. Then I’ll give some examples using Spring framework and various Java database tools such as Hibernate and apache commons-dbcp.
In Oracle, there are a couple of methods to propagate application user info into the database. Two such methods are:

  • Lightweight user sessions that can be created on top of a single database session in the middle tier or using a connection pool. This method is available to applications using OCI using OCI specific calls and settings like OCI_ATTR_PROXY_CREDENTIALS and using the new JDBC drivers from Oracle. Please note that this method only works when all your application users are users in the database.
  • Tracking users, modules and actions using specific CONTEXT (USERENV) parameters. This method is available to OCI applications using OCI_ATTR_MODULE, OCI_ATTR_ACTION and OCI_ATTR_CLIENT_INFO. This method is also available to Java applications using Oracle JDBC driver (either type 2 or type 4).

We will focus on the second method using the Oracle thin JDBC driver as it does not mandate creating all application users in the database.
First, let’s stick to the bare minimum (no exception handling and no frameworks):
Here is what should have worked in Oracle 9i –

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test
{
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL(“jdbc:oracle:thin:@localhost:1521:slavdev”);
dataSource.setUser(“scott”);
dataSource.setPassword(“tiger”);
OracleConnection conn = (OracleConnection) dataSource.getConnection();
conn.setClientIdentifier(“Slavik”);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(“select sysdate, sys_context(‘USERENV’,’CLIENT_IDENTIFIER’) from dual”);
rs.next();
System.out.println(rs.getString(1) + ” – ” + rs.getString(2));
rs.close();
statement.close();
conn.clearClientIdentifier(“Slavik”);
conn.close();
dataSource.close();
}
}

Surprisingly, this code actually works in 10gR2 giving the result: 2007-05-20 16:25:15.0 – Slavik. But on the other hand, making it work under 9iR2 is not so simple. Also, unfortunately, the setClientIdentifier() as well as clearClientIdentifier()are deprecated. So the recommended way to code this is to use the relatively new end-to-end metrics API.

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test1
{
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL(“jdbc:oracle:thin:@localhost:1521:slavdev”);
dataSource.setUser(“scott”);
dataSource.setPassword(“tiger”);
OracleConnection conn = (OracleConnection) dataSource.getConnection();
String metrics[] = new
String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = “Simple Test”;
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = “Test Application”;
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = “Slavik”;
// Set these metrics
conn.setEndToEndMetrics(metrics, (short) 0);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(
“select sysdate, ” +
“sys_context(‘USERENV’,’ACTION’), ” +
“sys_context(‘USERENV’,’MODULE’), ” +
“sys_context(‘USERENV’,’CLIENT_IDENTIFIER’) ” +
“from dual”);
rs.next();
System.out.println(rs.getString(1) + ” – ” + rs.getString(2) + “, ” +
rs.getString(3) + “, ” + rs.getString(4));
rs.close();
statement.close();
conn.close();
dataSource.close();
}
}

Now the results are – 2007-05-20 16:31:55.0 – Simple Test, Test Application, Slavik, and we did not waste any roundtrips to the server while setting all those details.

Please notice that the metrics setting will be set on the connection for all statements until we reset it by setting nulls and Short.MIN_VALUE.

For more details on the API, you can check http://download-west.oracle.com/docs/cd/B19306_01/java.102/b14355/endtoend.htm documentation.

A short explanation of what we set here:

  • Action – The current action that the application performs (can be a page name or an action within the page).
  • Module – The module within the application. Can be the application name.
  • Client Identifier – The user within the application performing these actions.

Now that we are over the basics, let’s move on to the fun stuff.

My next post will show how to transparently set those metrics using Spring framework, Hibernate and commond-dbcp.