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);
    }