X

Best Practices from Oracle Development's A‑Team

Identity Propagation from App to Data Tier

Matt Carter
Director of Product Management

Introduction

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.

Main Article

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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha