Implementing Oracle Proxy Authentication in WebSphere

By Melvin Friese and Deepak Pant

Problem Definition

Typical J2EE applications use connection pools to connect the application code running under application server (like WebSphere, WebLogic, JBOSS) to the backend database.

A connection pool is a feature that maintains a shared pool of data source connections on behalf of its clients (EJBs). The connection pool provides a connection to an EJB on request, and returns the connection to the pool when the EJB is finished using it. When it is returned to the pool, the connection is available for other EJBs. Because establishing a connection to a data source can be time-consuming, reusing such connections in a connection pool can improve performance.

Typically all the application users of the J2EE application are connected to the database using the single database user. Such a user is often called the connection pool user.

From a DBA perspective, this single user is performing all database activities. It makes Oracle auditing features unusable, and all the database auditing is handled within the application server because the database is not aware of the “real” end-user, behind the common connection pool user.

Oracle introduced a new feature called “Oracle Proxy Authentication” in Oracle8i, which attempts to kill to two birds with one stone. It allows the application servers to continue to take advantage of connection pool feature while meeting the DBA requirements of who is using the database.

Proxy authentication is the ability to connect as a user through another user. For example proxy authentication enables the application server’s connection pool user to authenticate once to the database using a ‘generic’ account and then establish lightweight session on behalf of actual application users.

It provides best of both worlds as the connection preparation time is optimized by using generic user and by opening proxy session on the connection, DBA’s can know the actual application end user.

Terminology

#	Item	Description
1	Application User	Database user that represents the application user.
2	Connection Pool User	User configured in the application server’s connection pool to connect to the database. This user establishes the connection, which application user uses it.

Database Setup

1. Download and Install Oracle database from following URL: http://www.oracle.com/technology/software/products/database/index.html
2. For the purpose of testing, we used version # 10.2.0.3.0.
3. As part of database installation, install the sample database HR.
4. Execute following DDL SQL statements using popular tools like TOAD or SQL Developer.

• Create Public Synonym for all the tables present in HR schema

CREATE PUBLIC SYNONYM COUNTRIES   FOR HR.COUNTRIES;

CREATE PUBLIC SYNONYM DEPARTMENTS FOR HR.DEPARTMENTS;

CREATE PUBLIC SYNONYM EMPLOYEES   FOR HR.EMPLOYEES;

CREATE PUBLIC SYNONYM JOB_HISTORY FOR HR.JOB_HISTORY;

CREATE PUBLIC SYNONYM JOBS        FOR HR.JOBS;

CREATE PUBLIC SYNONYM LOCATIONS   FOR HR.LOCATIONS;

CREATE PUBLIC SYNONYM REGIONS     FOR HR.REGIONS;

• Create database user that will be in Websphere Application Server’s Connection Pool

CREATE USER conn_pool_user IDENTIFIED BY conn_pool_password;

GRANT CREATE SESSION TO conn_pool_user;

• Create a role so that many users can be assigned to that role

CREATE ROLE APP_USER_ROLE;

• Grant Typical CRUD Permissions To APP_USER_ROLE

GRANT SELECT, INSERT, UPDATE, DELETE ON COUNTRIES      TO APP_USER_ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON DEPARTMENTS    TO APP_USER_ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEES      TO APP_USER_ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON JOB_HISTORY    TO APP_USER_ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON JOBS           TO APP_USER_ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON LOCATIONS      TO APP_USER_ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON REGIONS        TO APP_USER_ROLE;

• Grant Create Session to the APP_USER_ROLE

GRANT CREATE SESSION TO APP_USER_ROLE;

• Create database user for application users that will make use of the Proxy Authentication

CREATE USER “John.Doe” IDENTIFIED BY “SomePasswordXXX”;

ALTER USER “John.Doe” GRANT CONNECT through conn_pool_user;

GRANT APP_USER_ROLE TO “John.Doe”;

CREATE USER “John.Doe1” IDENTIFIED BY “SomePasswordYYY”;

ALTER USER “John.Doe1” GRANT CONNECT through conn_pool_user;

GRANT APP_USER_ROLE TO “John.Doe1”;

Note that the application user’s password should never be known to the users as it is not needed for establishing the proxy user connection.

Java Code

1. Download and configure the Oracle Type 4 JDBC Driver from following URL:http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
2. For the purpose of testing, we used version # 11.1.0.7.0.
3. Let us assume that the JDBC URL for the database is following:

jdbc:oracle:thin:hr/password@192.168.1.101:1521:orcl

Testing Proxy User Authentication Connection using simple Java class:

private static void prepareProxyConnection() throws Exception {

       OracleConnection pdConn = null;

       try {

              Properties pdProp = new Properties();

              // prepare basic connection and typecast it to OracleConnection

              pdConn = (OracleConnection) DriverManager.getConnection                                                       (JDBC_URL_PROXY, pdProp);

              // prepare first proxy user connection

              openProxySession(pdConn, “John.Doe”);

              // prepare second proxy user connection

              openProxySession(pdConn, ” John.Doe1″);

       }

       catch (Exception ex) {

              ex.printStackTrace();

              throw ex;

       }

       finally {

              // close the main connection object

              if (pdConn != null) {

                     pdConn.close();

              }

       }

}

private static void openProxySession(OracleConnection pdConn, String strProxyUser) throws Exception {

       try {

              // prepare properties for proxy user

              Properties pdProp = new Properties();

              pdProp.put(“PROXY_USER_NAME”, strProxyUser);

              // open proxy session

              pdConn.openProxySession (  OracleConnection.PROXYTYPE_USER_NAME,                                              pdProp);

              // fetch user information

              fetchUserInformation(pdConn);

              // execute SQL statement –

              // this method will do some database work like SELECT etc

              executeSomeSQL(pdConn);

       }

       catch (Exception ex) {

              ex.printStackTrace();

              throw ex;

       }

       finally {

              // close the proxy session object

              if (pdConn != null) {

                     // close the proxy session so that it can be reused

                     pdConn.close(OracleConnection.PROXY_SESSION);

              }

       }

}

1. Following SQL statement will return “John.Doe” or “John.Doe1” as “PROXY_USER” if executed using the proxy user connection object.

SELECT USER,

  SYS_CONTEXT(‘USERENV’,’AUTHENTICATED_IDENTITY’) AS AUTHENTICATED_IDENTITY,

  SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)         AS CURRENT_SCHEMA,

  SYS_CONTEXT(‘USERENV’,’PROXY_USER’)             AS PROXY_USER,

  SYS_CONTEXT(‘USERENV’,’SESSION_USER’)           AS SESSION_USER

FROM DUAL;

Java Code running under WebSphere Application Server

1. Create a Connection Pool in WebSphere that uses “conn_pool_user” user to establish the database connections using the Oracle Type 4 JDBC driver.
2. Use following function to get the connection from WebSphere connection pool using Oracle Proxy Authentication feature.

private OracleConnection prepareProxySession(String strProxyUser) throws SQLException {

       OracleConnection pdOraConn = null;

       try {

              // get the jdbc connection object from connection pool

              Connection pdConn = getConnectionFromPool();

              // cast the object to native oracle connection object

              OracleConnection pdOraConn = (OracleConnection) WSJdbcUtil

                           .getNativeConnection((WSJdbcConnection) pdConn);

              // prepare properties for proxy user

              Properties pdProp = new Properties();

              pdProp.put(“PROXY_USER_NAME”, strProxyUser);

              // open proxy session using native oracle conn object

              pdOraConn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME,

                           pdProp);

       }

       catch (SQLException ex) {

              m_pdLog.error(“SQLException:”, ex);

              throw ex;

       }

       return pdOraConn;

}

Use following function to close the proxy session connection and return it back to the WebSphere connection pool. The code assumes a member variable (m_pdOraConnection) that holds reference to the connection object.

public void cleanup() throws Exception {

       try {

              // close the proxy session so that any other user can use it

              m_pdOraConnection.close(OracleConnection.PROXY_SESSION);

       }

       finally {

              m_pdOraConnection = null;

       }

}

Special Notes

1. Both connection pool user (conn_pool_user) and application user (John.Doe or John.Doe1) will require create session privilege.
2. The password of connection pool user will be stored in WebSphere as J2C Authentication data, associated to the connection pool.
3. The password of application user (John.Doe or John.Doe1) will not be stored or known to the application. So no application user can use the credentials to connect directly to the database.