X

@OracleIMC Partner Resources & Training: Discover your Modernization options + Reach new potential through Innovation

How to use database proxy users with ADF

Guest Author

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!

Join the discussion

Comments ( 9 )
  • John Flack Tuesday, June 7, 2011
    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?
  • Lukasz Romaszewski Sunday, June 19, 2011

    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


  • Ludo Tuesday, December 20, 2011

    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


  • Ludovic Tuesday, December 20, 2011

    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


  • john archer Tuesday, May 15, 2012

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

    thanks

    john


  • Lukasz Friday, May 18, 2012

    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


  • Osama Allam Sunday, June 1, 2014

    Great article, I will apply soon,

    thank you in advance


  • Osama Allam Tuesday, June 3, 2014

    Dear Sir,

    I tried to apply your solution, but the problem is the actual Connection can not be cast to OracleConnection

    giving ClassCastException,

    as the actual Connection is of type:

    weblogic.jdbc.wrapper.PoolConnection_oracle_jdbc_driver_T4CConnection

    any suggestions


  • Lukasz Tuesday, June 3, 2014

    Osama,

    please remember to cast to oracle.jdbc.OracleConnection and NOT to oracle.jdbc.driver.OracleConnection.

    Regards,

    Lukasz


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.