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
- Application User - Database user that represents the application user.
- 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.
- Download and Install Oracle database from following URL. For the purpose of testing, we used version # 10.2.0.3.0. http://www.oracle.com/technology/software/products/database/index.html
- As part of database installation, install the sample database HR.
- Execute following DDL SQL statements using popular tools like TOAD or SQL Developer.
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;
b) 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;
c) Create a role so that many users can be assigned to that role
CREATE ROLE APP_USER_ROLE;
d) 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;
e) Grant Create Session to the APP_USER_ROLE
GRANT CREATE SESSION TO APP_USER_ROLE;
f) 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
Java Code
- Download and configure the Oracle Type 4 JDBC Driver from following URL. For the purpose of testing, we used version # 11.1.0.7.0. http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
- 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:
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);
}
}
}
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
- Create a Connection Pool in WebSphere that uses “conn_pool_user” user to establish the database connections using the Oracle Type 4 JDBC driver.
- Use following function to get the connection from WebSphere connection pool using Oracle Proxy Authentication feature.
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
- Both connection pool user (conn_pool_user) and application user (John.Doe or John.Doe1) will require create session privilege.
- The password of connection pool user will be stored in WebSphere as J2C Authentication data, associated to the connection pool.
- 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.
- Do monitor your connection pool to ensure that physical connections are not getting created everytime the application needs a connection to the database. The pool should be able to re-use the prior instantiated connections.
