Audit Trail in Web Application

Background:

Generally Web applications simply authenticate users (using LDAP) to the middle tier and the middle tier connects to the database as a single generic user. This model works as long as the end user identity isn't required in the back end database. But identity propagation has become increasingly important for both regulatory compliance and overall security. This document gives some ways of implementing it.

Solution:

The most common method used by existing multi-tier applications is to pass a client identifier from the middle tier to the backend database across an existing generic DB connection. Client identifiers were introduced in Oracle Database 9i to enable an application to pass an identifier across an existing thick or thin JDBC connection from the middle tier to the backend database. When the Oracle database auditing is turned on globally, nothing is audited by default. The DBA has to explicitly define the table and operations that need to be audited.

For e.g. tables/operations audited:

EMPLOYEE_DETAILS: INSERT/UPDATE/DELETE

Once this is done, when some inserts or updates happen on those tables, the DBA can verify the audit trail as follows.

select owner, obj_name, username, userhost, timestamp, action_name, client_id, ses_actions, returncode from sys.dba_audit_object where obj_name in ( 'EMPLOYEE_DETAILS') order by timestamp desc;

The client_id field will contain application end user or whatever identifier was passed in.

Option 1:

To set the client Identifier for JDBC applications, use the following oracle.jdbc.OracleConnection interface methods setClientIdentifier() : Sets the client identifier for a connection. But this method is deprecated since 10.1.0.x

Option  2:

A workaround to this above method is to use the API setEndToEndMetrics

String metrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];

metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "junittest";

((OracleConnection) getConnection()).setEndToEndMetrics(metrics, (short) 0);

These two methods works fine in a standalone/JUnit environment. But when the application is deployed in OAS, the above API does not work as OAS picks up the jar  ojdbc14dms.jar by default. So in order to use the jdbc jar without DMS*, the following parameter needs to be used at the server startup -Xbootclasspath/a:<path>/ojdbc14.jar.

*[Oracle DMS(Dynamic Monitoring Service) provides a set of API'S to measure and report performance metrics, status, information, and diagnostic information. To use DMS in the application, the application needs to use these DMS API's where it wants to measure the performance.]

Option 3:

To have the client_id in a deployed application, the following code can be used everytime we get a connection.

CallableStatement cstmt = getConnection().prepareCall(

                    "begin DBMS_SESSION.SET_IDENTIFIER(?); end;");

cstmt.setString(1, getClientContext().getUser().getUserName());

cstmt.executeUpdate();

This approach has a little overhead as for every connection we are calling a procedure. This options can be wrapped in a system property like –Denable.audit.trail= true so that client can enable/disable it.