Skip to main content

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)

Comments

Popular posts from this blog

Send e-mail with attachment through OSB

Oracle Service Bus (OSB) contains a good collection of adapter to integrate with any legacy application, including ftp, email, MQ, tuxedo. However e-mail still recognize as a stable protocol to integrate with any application asynchronously. Send e-mail with attachment is a common task of any business process. Inbound e-mail adapter which, integrated with OSB support attachment but outbound adapter doesn't. This post is all about sending attachment though JavaCallout action. There are two ways to handle attachment in OSB: 1) Use JavaCallout action to pass the binary data for further manipulation. It means write down a small java library which will get the attachment and send the e-mail. 2) Use integrated outbound e-mail adapter to send attachment, here you have to add a custom variable named attachment and assign the binary data to the body of the attachment variable. First option is very common and easy to implement through javax.mail api, however a much more developer manage t

Tip: SQL client for Apache Ignite cache

A new SQL client configuration described in  The Apache Ignite book . If it got you interested, check out the rest of the book for more helpful information. Apache Ignite provides SQL queries execution on the caches, SQL syntax is an ANSI-99 compliant. Therefore, you can execute SQL queries against any caches from any SQL client which supports JDBC thin client. This section is for those, who feels comfortable with SQL rather than execute a bunch of code to retrieve data from the cache. Apache Ignite out of the box shipped with JDBC driver that allows you to connect to Ignite caches and retrieve distributed data from the cache using standard SQL queries. Rest of the section of this chapter will describe how to connect SQL IDE (Integrated Development Environment) to Ignite cache and executes some SQL queries to play with the data. SQL IDE or SQL editor can simplify the development process and allow you to get productive much quicker. Most database vendors have their own front-en

Load balancing and fail over with scheduler

Every programmer at least develop one Scheduler or Job in their life time of programming. Nowadays writing or developing scheduler to get you job done is very simple, but when you are thinking about high availability or load balancing your scheduler or job it getting some tricky. Even more when you have a few instance of your scheduler but only one can be run at a time also need some tricks to done. A long time ago i used some data base table lock to achieved such a functionality as leader election. Around 2010 when Zookeeper comes into play, i always preferred to use Zookeeper to bring high availability and scalability. For using Zookeeper you have to need Zookeeper cluster with minimum 3 nodes and maintain the cluster. Our new customer denied to use such a open source product in their environment and i was definitely need to find something alternative. Definitely Quartz was the next choose. Quartz makes developing scheduler easy and simple. Quartz clustering feature brings the HA and