Friday May 27, 2011

How to use database proxy users with ADF

If you are a Forms developer and want to migrate your existing application to the ADF technology you often hit an issue with existing database users which cannot be directly used in three-tier architecture. ADF applications typically use a database connection pool with only one db user containing all necessary objects. In most cases those users need to be recreated in the application server security layer (e.g. LDAP server). But there are cases where that kind of migration involves redesigning the whole database layer as the database users are granted the permission to the objects in main schema. There is couple of solution for this issue; one of them is using proxy users.

Database Proxy Authentication is a feature of Oracle Database which strengthens the security by introducing additional layer of authentication through so called middle-tier proxy user. You can find more information about this feature in the following documentation:

http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/authentication.htm#i1010326

Everything boils down to a simple idea of having one db user (proxy user) which can be used in a datasource configuration on the application server and still being able to force the database to recognize separate connections as made by application users. This approach can be used in Forms 11g and of course in all JEE application including ADF application. To configure the WLS and your application to use this feature you need to follow these steps:

  • Create a db user which will be used as a connection proxy, e.g. midtier
  • Create application users in WLS with identical names of those already existing on the database
  • Grant “connect through” to all db users which are going to be mapped to the application users e.g:
alter user <app_user>grant connect through <proxy_user>;

  • Create a datasource on the application server with proxy user (e.g. /jdbc/midtier)
  • Add Java implementation class to the application module and then add following methods (REMEMBER TO USE oracle.jdbc.OracleConnection AND NOT oracle.jdbc.driver.OracleConnection!!!):

private Connection getCurrentConnection() throws SQLException {

/* Note that we never execute this statement, so no commit really happens */
PreparedStatement st = getDBTransaction().createPreparedStatement(
"commit",1);
Connection conn = st.getConnection();
st.close();
return conn;
}

@Override
protected void afterConnect() {
OracleConnection conn;
String currentUser = ADFContext.getCurrent().getSecurityContext().getUserName();
java.util.Properties prop =
new java.util.Properties();
prop.put(OracleConnection.PROXY_USER_NAME, currentUser);


try {
conn = (OracleConnection)getCurrentConnection();
conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);

}
catch (SQLException e) {
e.printStackTrace();
throw new JboException(e);
}

super.afterConnect();
}

@Override
protected void beforeDisconnect() {
try {
OracleConnection conn = (OracleConnection)getCurrentConnection();
conn.close(OracleConnection.PROXY_SESSION);
}
catch (SQLException e) {
e.printStackTrace();
throw new JboException(e);
}

super.beforeDisconnect();
}

This will enable awareness of the application users on the database side (identity of the application users will be preserved) but still with one single user used in connection pool definition. It will fully utilize connection pooling as well as opposed to using “Enable Identity Based Connection Pooling” feature in the WLS (I will describe this option in one of the next posts).

The drawback of this approach is that you have to define each user twice: first time in the database and then in the security provider in WebLogic server. Because it adds some overhead in the security management I would recommend to use it only as a temporary solution until you change your architecture to the full three-tier. Another way to avoid this issue is to base both application and database security on OID and have only one place for all security related information.

You can find a simple example of using proxy users in the attachment. To run the DBUsersApp application you need to do following steps:

  • Unpack the archive and open DBUsersApp.jws in JDeveloper
  • Create a midtier user in your database
  • Create some application users both in the database and WLS (e.g. user1, user2)
  • Grant “connect through” permission to application users on the database side:
alter user user1 grant connect through midtier;
alter user user2 grant connect through midtier;
  • Create a datasource in the WLS administration console using midtier db user and configure the jndi name to /jdbc/midtier
  • Deploy the application to the WLS
  • Log in as user1
  • You should see that the database connection used by the application is really using USER1 identity instead of default MIDTIER:
select user from dual;
will return USER1

 If you have question/problems with the application attached please fill free to post a comment.

 Have a nice weekend!

About

Oracle ECEMEA Partner Hubs Migration Center Team

We share our skills to maximize your revenue!
Our dedicated team of consultants can rapidly and successfully assist you to adopt and implement the latest of Oracle Technology in your solutions.

Stay Connected
partner.imc
@
beehiveonline.oracle-DOT-com
Google+

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today