Identity Propagation from App to Data Tier

In order to have the ability to audit at all layers of your multi-tier architecture, it is important to have a Subject’s identity tied to all transactions. Many apps leverage JDBC connection pooling however, so a Subject’s identity is lost at the data layer, as everything appears to be coming from the system user configured in the connection pool. What isn’t well known is that the ability to propagate identity from an app container to the data tier is available within the Oracle JDBC libraries, both thin and OCI-based. One can configure a connection pool using a system user, and create a session with proxy user using the app container’s identity. Example snippet:

<%@ page language="java" contentType="text/html;charset=UTF-8"%>

<%@ page import="javax.naming.*,java.sql.*,oracle.jdbc.pool.OracleDataSource, java.util.*,oracle.jdbc.OracleConnection,javax.sql.DataSource,

java.io.PrintWriter"%>

try {

Context ic = new InitialContext();

DataSource dataSource =

(DataSource) ic.lookup("icam/sampledata");

Properties userNameProp = new Properties();

userNameProp.put(OracleConnection.PROXY_USER_NAME, request.getUserPrincipal().getName());

OracleConnection conn = (OracleConnection)dataSource.getConnection();

conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, userNameProp);

PreparedStatement userenvStmt =

conn.prepareStatement(

" select "

+"sys_context('USERENV','PROXY_USER') "

+", sys_context('USERENV','external_name') "

+", sys_context('USERENV','SESSION_USER') "

+", sys_context('USERENV', 'ENTERPRISE_IDENTITY') "

+" from dual"

);

ResultSet userenvRset =

userenvStmt.executeQuery();

if (userenvRset.next()) {

out.println("

Userenv proxy_user : " +

userenvRset.getString(1) + "");

out.println("

Userenv external_name : " +

userenvRset.getString(2)+ "");

out.println("

Userenv session_user : " +

userenvRset.getString(3)+ "");

out.println("

Userenv ENTERPRISE_IDENTITY: " +

userenvRset.getString(4)+ "");

}

userenvStmt.close();

This code assumes that the database is also configured to use Enterprise User Security (EUS), which ensures that the identity is consistent across the app container and database. The app container, WebLogic in this case, is configured to use an LDAP authentication provider.

The diagram shows OID as the LDAP store, but this could also be OVD combined with ODSEE or AD.

One would setup a shared schema in the database to map these proxy users to a database:

CREATE USER sharedschema IDENTIFIED GLOBALLY AS ‘’;

GRANT CREATE SESSION TO sharedschema;

CREATE USER app_public IDENTIFIED BY abcd1234

DEFAULT TABLESPACE shared

TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION TO app_public;

ALTER USER sharedschema

GRANT CONNECT THROUGH app_public

AUTHENTICATED USING DISTINGUISHED NAME;

Configuring the JDBC data source would leverage the app_public database user and any user that creates a proxy session would have visibility into the sharedschema data.