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!

Comments:

I see that you are overriding the afterConnect, and beforeDisconnect methods in the Application Module. In other documentation, similar actions that are meant to take place right after a user first logs into the application are done by overriding prepareSession. For instance, I've copied sample code for calling DBMS_SESSION to set the Client Identifier which runs through prepareSession. So what is the difference between afterConnect and prepareSession?

Posted by John Flack on June 07, 2011 at 08:48 AM CEST #

John,

You are right, if you want to be sure that the proxy user is set correctly in each configuration you should use prepareSession. However with default settings (jbo.doconnectionpooling=false and release mode=Stateful) each instance of application module has its own database connection so it is enough to override afterConnect and beforeDisconnect. You can find more information about connection pooling in ADF documentation:

http://download.oracle.com/docs/cd/E16162_01/web.1112/e16182/bcampool.htm

Posted by Lukasz Romaszewski on June 19, 2011 at 03:12 PM CEST #

Hi

I have just tested the feature "Identity Based connection pooling" of weblogic and it is working fine. But I don't know why it is mandatory to specify the remote user password (DB) inside the JDBC Credential mapping. I have granted my user with "alter user toto grant connect through myproxy;" and with SQLPLUS it is possible to connect as "toto" without to specify the password : sqlplus myproxy[toto]/myproxy_password
But with weblogic it doesn't work if we don't specify the password. All user passwords are encrypted and not known to us, so it is a big problem to use the "Identity Based Connection pooling".
Does anybody have experiences with this feature ?

Regards

Ludo

Posted by Ludo on December 20, 2011 at 10:35 AM CET #

Hi

I have just tested the feature "Identity Based connection pooling" of weblogic and it is working fine. But I don't know why it is mandatory to specify the remote user password (DB) inside the JDBC Credential mapping. I have granted my user with "alter user toto grant connect through myproxy;" and with SQLPLUS it is possible to connect as "toto" without to specify the password : sqlplus myproxy[toto]/myproxy_password
But with weblogic it doesn't work if we don't specify the password. All user passwords are encrypted and not known to us, so it is a big problem to use the "Identity Based Connection pooling".
Does anybody have experiences with this feature ?

regards

Ludo

Posted by Ludovic on December 20, 2011 at 10:36 AM CET #

Do you know if this is possible with ORM tools as well like Hibernate or EclipseLink?

thanks
john

Posted by john archer on May 16, 2012 at 01:06 AM CEST #

John,

I am not aware of any specific API you can use in ORM tools to make it work but if you can get an access to database connection just borrowed from the pools before you start using it you can apply the same API as above to prepare the proxy connection.

Regards,
Lukasz

Posted by Lukasz on May 18, 2012 at 02:21 PM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
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