Thursday, September 23, 2010

Almost foolproof ADF Oracle proxying

1) Create a common application model (this also sets up the oracle users in the context just for auditing)
@Override
    protected void prepareSession(Session session){

        super.prepareSession(session);

        try{
            String userName = ADFContext.getCurrent().getSecurityContext().getUserName();
            String host = ((HttpServletRequest)ADFContext.getCurrent().getEnvironment().getRequest()).getRemoteHost();
            String ip = ((HttpServletRequest)ADFContext.getCurrent().getEnvironment().getRequest()).getRemoteAddr();

            if (userName != null){
                setupSessionInfo(getDBTransaction(), host, ip, userName);
            }
            proxyUser(getDBTransaction());
            //this is done because of a crappy weblogic caching error
            clearPoolCache(getDBTransaction());
        } catch (Exception e){
            session = null;
            System.out.println("------> Error in user proxy");
            e.printStackTrace();
            throw new ApplicationModuleException(e);
        }
    }



2) Extend this AM in all you application modules that require proxy
public class MyAMImpl extends MyParentApplicationModuleImpl implements MyAM{

3) Grant user rights to connection pool user

alter user MyUser grant connect through CPUser;

4) Proxy user method

public static void proxyUser(DBTransaction transation) throws SQLException{

        Statement stat = transation.createPreparedStatement("rollback", 0);
        OracleConnection oConn = (OracleConnection)stat.getConnection();

        Properties props = new Properties();
        String uname = ADFContext.getCurrent().getSecurityContext().getUserPrincipal().getName();
        props.put(OracleConnection.PROXY_USER_NAME, uname);
        if (oConn.isProxySession()){
            oConn.close(OracleConnection.PROXY_SESSION);
        }
        try{
            oConn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);
        } catch (SQLException e){
            oConn.abort();
            if (!stat.isClosed()){
                stat.close();
            }
            throw e;
        }

        if (!stat.isClosed()){
            stat.close();
        }

    }

5) Clear cache method (could just switch weblogics cache off)
public static void clearPoolCache(DBTransaction transation) throws SQLException{

        Statement stat = transation.createPreparedStatement("rollback", 0);
        OracleConnection oConn = (OracleConnection)stat.getConnection();
        clearPoolConnStmntCache(oConn);
        if (!stat.isClosed()){
            stat.close();
        }
    }

6) setupSessionInfo method
protected void setupSessionInfo(DBTransaction transaction, String host, String ipAddress, String userName){

//this sql is just an example you can do more here
        String sql = " BEGIN dbms_session.set_identifier(:host); dbms_application_info.set_client_info(:ipAddress); END;";

        Map parameters = new HashMap();
        parameters.put("host", host);
        parameters.put("ipAddress", ipAddress);
        parameters.put("userName", userName);
        AdfJdbcUtil.executeNamedCall(false, transaction, sql, parameters);
    }

2 comments:

  1. Hello, It is interesting post. But where is clearPoolConnStmntCache method?

    I'm working on proxy user and tuning side.

    Erdenebayar

    ReplyDelete
    Replies
    1. Yhea sorry missed that one..

      public static void clearPoolConnStmntCache(Connection conn) throws SQLException {
      if(conn instanceof WLConnection) {
      ((WLConnection) conn).clearStatementCache();
      }
      }

      Delete