Ibatis3 with oracle proxy authentication

Most of all oracle security features could be done by oracle proxy authentication. Oracle proxy authentication provides fine grained access control for temporary users of the account, without compromising the original password even enabling database auditing and logging. In this current post i will first setup database for proxy authentication and later will connect to it by connection pool.

1) Database setup:

sqlplus /nolog
conn sys/manager@orcl as sysdba

create a proxy user

create user          proxy_user
identified by        pw_proxy
default tablespace   users
temporary tablespace temp;

create a target user

create user          target_user
identified by        pw_target
default tablespace   users
temporary tablespace temp
quota unlimited on   users;

now we will alter target user to connect through proxy user

alter user target_user grant connect through proxy_user;

Also grant create session and the create table system privilege

grant create session,
create table
to    target_user;

Note that only target user has connect session privilege. Now we will create one table for demonstration purpose and insert some data on it.

connect target_user/pw_target;

create table FDC_OWNERSHIP (
name  varchar2(200)
);
insert into FDC_OWNERSHIP values ('val1');
insert into FDC_OWNERSHIP values ('val2');
insert into FDC_OWNERSHIP values ('val3');
commit;

Now proxy user could connect with syntax proxy_user[targer_user]

connect proxy_user[target_user]/pw_proxy;
select count(*) from FDC_OWNERSHIP;

All our database setup completed, now we can care about ibatis3. For demonstration purpose we will create a Mapper interface and a connection factory class for get the proxy connection.

public interface LsaDbSqlMapper {
@Select("select own.name from ${schemaName}.FDC_OWNERSHIP own")
List orgNames (@Param("schemaName") final String schemName);
}

Here is the quick implemention of the connection factory class:

public class LsaSessionFactory {
private LsaSessionFactory() {    }
private static SqlSessionFactory sessionFactory;
private static OracleOCIConnectionPool ociPool;

private static String tnsAlias = "(DESCRIPTION =\n" +
"    (ADDRESS_LIST =\n" +
"      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.10.57)(PORT = 1521))\n" +
"    )\n" +
"    (CONNECT_DATA =\n" +
"      (SERVICE_NAME = xyz)\n" +
"    )\n" +
"  )";

private static DataSource getOciDataSource(String username, String password) throws SQLException{
if(ociPool == null){
ociPool = new OracleOCIConnectionPool();
ociPool.setURL("jdbc:oracle:oci:@"+ tnsAlias);
ociPool.setUser(username);
ociPool.setPassword(password);
Properties prop = new Properties();
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "3");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "5");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1");
ociPool.setPoolConfig(prop);
}

return ociPool;
}
public static SqlSessionFactory getSessionFactory(String username, String password){
if(sessionFactory == null){
try {
Environment env = new Environment("Development", new JdbcTransactionFactory(),getOciDataSource(username, password));
Configuration config = new Configuration(env);
config.addMapper(LsaDbSqlMapper.class);

sessionFactory = new SqlSessionFactoryBuilder().build(config);
} catch (SQLException e) {
e.printStackTrace();
}
}
return sessionFactory;
}

public static Connection getProxyConnection(String proxyUserName) throws SQLException{
if(ociPool != null ){
Properties userNameProp = new Properties();
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME,proxyUserName);
return ociPool.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME, userNameProp);
}
return null;
}
public static void closePool() throws SQLException{
if(ociPool != null){
ociPool.close();
}
}
}

Here is the simple test of the connection factory

public class Service {
public static void main(String[] args) throws Exception{
System.out.println("Test Proxy authentication ...");
SqlSession session = LsaSessionFactory.getSessionFactory("PROXY_USER","pw_proxy").openSession(LsaSessionFactory.getProxyConnection("target_user"));
LsaDbSqlMapper sqlMapper = session.getMapper(LsaDbSqlMapper.class);
List orgNames = sqlMapper.orgNames("eos");
System.out.println("Orgs:"+ orgNames);

LsaSessionFactory.closePool();
}

References:
1) Effective Oracle Database 10g Security by Design
2) René Nyffenegger's collection of things on the web
3) Using Oracle Proxy Authentication with JPA (EclipseLink-Style)