Using application context to limit to the data visibility is one way VPDs are implemented. However in application with JDBC session pools this creates an additional challenge since session context is not cleared automatically when the connection close method is closed. It's only a logical close and connection return to the pool with session context values intact. Following java code illustrate this behavior. The JDBC libraries used are
Driver Name Oracle JDBC driver
Driver Version 11.2.0.3.0
Driver Major Version 11
Driver Minor Version 2
Database Major Version 11
Database Minor Version 2
The
DBConnection class which is the connection pool class is the same class used earlier and not listed below.
public class Test {
public static void main(String[] args) {
try {
DBConnection pool = new DBConnection("sbx", "sbx", "192.168.0.99", "1521", "fgacdb");
System.out.println("setting context");
testSet(pool);
System.out.println("context set done connection closed");
Thread.sleep(1 * 5 * 1000);
System.out.println("getting from pool after close");
testGet(pool);
System.out.println("explicit clearing");
testClear(pool);
System.out.println("explicit clearing done");
Thread.sleep(1 * 5 * 1000);
System.out.println("getting context value after clear");
testGet(pool);
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static void testSet(DBConnection pool) throws SQLException {
Connection[] cons = new Connection[10];
for (int i = 0; i < 10; i++) {
cons[i] = pool.getConnection();
}
for (int i = 0; i < 10; i++) {
try {
Connection con = cons[i];
CallableStatement cls = con.prepareCall("{call fgac_ctx_pkg.fgac_set_context(?,?)}");
cls.setString(1, "user_id");
cls.setString(2, "user " + i);
cls.executeUpdate();
cls.close();
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void testGet(DBConnection pool) throws SQLException {
Connection[] cons = new Connection[10];
for (int i = 0; i < 10; i++) {
cons[i] = pool.getConnection();
}
for (int i = 0; i < 10; i++) {
try {
Connection con = cons[i];
PreparedStatement pr = con.prepareStatement("select sys_context ('fgac_ctx','user_id') from dual");
ResultSet rs = pr.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
pr.close();
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void testClear(DBConnection pool) throws SQLException {
Connection[] cons = new Connection[10];
for (int i = 0; i < 10; i++) {
cons[i] = pool.getConnection();
}
for (int i = 0; i < 10; i++) {
try {
Connection con = cons[i];
CallableStatement cls = con.prepareCall("{call fgac_ctx_pkg.fgac_clear_context(?)}");
cls.setString(1, "user_id");
cls.executeUpdate();
cls.close();
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}Code uses following PL/SQL package
create or replace package fgac_ctx_pkg is
procedure fgac_set_context( i_attribute in varchar2, value in varchar2 );
procedure fgac_clear_context (i_attribute in varchar2 );
end fgac_ctx_pkg;
/
create or replace package body fgac_ctx_pkg is
procedure fgac_set_context
( i_attribute in varchar2, value in varchar2 )
is
begin
dbms_Session.set_context('fgac_ctx',i_attribute, value);
end;
procedure fgac_clear_context
( i_attribute in varchar2 )
is
begin
dbms_Session.clear_context('fgac_ctx',attribute => i_attribute);
end;
end fgac_ctx_pkg;
/
create or replace context fgac_ctx using FGAC_CTX_PKG;Running the code would give the following output
setting context
context set done connection closed
getting from pool after close
user 2
user 1
user 0
user 3
user 4
user 5
user 7
user 6
user 8
user 9
explicit clearing
explicit clearing done
getting context value after clear
null
null
null
null
null
null
null
null
null
null
It could be seen from the output that after setting session context and calling the connection close method, it is still possible to get the session context values when connections are borrowed from the connection pool. The solution is to explicitly call the session clear method. After the explicit clear the session context values are no longer there hence the null output.
Explicitly setting and clearing maybe feasible for a small program but when application is already developed and trying to add these additional lines may require considerable amount of work and testing. This is where the OracleConnectionWrapper comes in handy which allows to wrap the connection close method and allows to call the explicit clear method from one place. This requires minimum work for an existing application. Similarly setting the context value would be done by overriding the getConnection method of the connection pool class.
Extend OracleConnectionWrapper to create a customized wrapper.
public class MyOracleConnectionWrapper extends OracleConnectionWrapper {
public MyOracleConnectionWrapper(Connection con) {
super((OracleConnection) con);
}
@Override
public void close() throws SQLException {
System.out.println("clearing");
CallableStatement cls = super.prepareCall("{call fgac_ctx_pkg.fgac_clear_context(?)}");
cls.setString(1, "user_id");
cls.executeUpdate();
cls.close();
super.close();
}
}Modify Connection Pool class by overriding getConnection method and populating session context with user id using application specific methods.
public class DBConnection {
private PoolDataSource pds = null;
public DBConnection(String username, String password, String host, String port, String sid) {
try {
pds = PoolDataSourceFactory.getPoolDataSource();
pds.setUser(username);
pds.setPassword(password);
pds.setURL("jdbc:oracle:thin:@" + host + ":" + port + ":" + sid);
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setInitialPoolSize(10);
pds.setMinPoolSize(10);
pds.setMaxPoolSize(15);
} catch (SQLException ex) {
ex.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
MyOracleConnectionWrapper con = new MyOracleConnectionWrapper((OracleConnection) pds.getConnection());
CallableStatement cls = con.prepareCall("{call fgac_ctx_pkg.fgac_set_context(?,?)}");
cls.setString(1, "user_id");
cls.setString(2, "user " + applicationSessionObj.getUserID());
cls.executeUpdate();
cls.close();
return con;
}
}With this code when a connection is borrowed from the connection pool it will have session context values associated with that user id and when connection close method is called session context will be cleared.
Similar to session context auto commit set at connection level is also not reset when returned to pool. To examine this behavior change for loop code where connections are borrowed from pool as follows on testSet method
for (int i = 0; i < 10; i++) {
cons[i] = pool.getConnection();
System.out.println(cons[i].getAutoCommit());
cons[i].setAutoCommit(false);
}and on testGet method
for (int i = 0; i < 10; i++) {
cons[i] = pool.getConnection();
System.out.println(cons[i].getAutoCommit());
}This will show the status of auto commit when connections are initially borrowed from the connection pool versus status after auto commit is set to false, connection closed and returned to pool and borrowed from pool again.
