Revision as of 21:24, 29 March 2008

This page is under construction. The code samples provided here require validation in various architectures before being finalized. --Doug 21:22, 29 March 2008 (EDT)

How to use EclipseLink JPA with Oracle Proxy Authentication

The Oracle database offers proxy authentication enabling the application to leverage a shared data source connected to the database by a single common/default user and then when used within the application the connection can be 'proxied' to be a different user. This offers the benefit of the database having knowledge of the specific user for the purposes of auditing or secure data access.

In this how-to the focus is on the usage of proxy authentication in conjunction with EclipseLink's JPA.

Overview

Requirements

• Access to OracleConnection (typically from OracleDataSource)

• Event listener to open and close the proxy session. This can be done with regular or exclusive connections

Configuration

• Configure SessionEventListerner

Opening the Proxy Session

SessionEVentListener.postAcquireExclusiveConnection(SessionEvent event)

    @Override
    public void postAcquireExclusiveConnection(SessionEvent event) {
        Session session = event.getSession();
        if (!session.isClientSession()) {
            return;
        }
        ClientSession clientSession = (ClientSession)session;
        Accessor accessor = (Accessor)event.getResult();
        accessor.incrementCallCount(clientSession);

        openProxySession(accessor.getConnection(), 
                         ((ExclusiveIsolatedClientSession)event.getSession()).getConnectionPolicy().getProperties());
    }

    /**
     * In case "proxytype" property is specified connects using proxy connection,
     * otherwise calls its superclass.
     */
    private void openProxySession(Connection conn, 
                                  Map properties) throws DatabaseException, 
                                                         ValidationException {
        Integer proxytype = 
            (Integer)ConversionManager.getDefaultManager().convertObject(properties.get(OracleOCIConnectionPool.PROXYTYPE), 
                                                                         Integer.class);

        if (proxytype != null) {
            try {
                Properties props = new Properties();
                props.putAll(properties);
                props.remove(OracleOCIConnectionPool.PROXYTYPE);
                ((OracleConnection)conn).openProxySession(proxytype.intValue(), 
                                                          props);
            } catch (SQLException exception) {
                throw DatabaseException.sqlException(exception);
            } catch (ClassCastException classCastException) {
                throw ValidationException.oracleJDBC10_1_0_2ProxyConnectorRequiresOracleConnection();
            } catch (NoSuchMethodError noSuchMethodError) {
                throw ValidationException.oracleJDBC10_1_0_2ProxyConnectorRequiresOracleConnectionVersion();
            }
        }
    }

SessionEventListener.preReleaseExclusiveConnection(SessionEvent event)

    @Override
    public void preReleaseExclusiveConnection(SessionEvent event) {
        Session session = event.getSession();
        if (!session.isClientSession()) {
            return;
        }
        ClientSession clientSession = (ClientSession)session;
        oracle.toplink.internal.databaseaccess.Accessor accessor = 
            clientSession.getWriteConnection();
        if (accessor != null) {
            oracle.jdbc.OracleConnection oracleConn = 
                (oracle.jdbc.OracleConnection)accessor.getConnection();
            if (oracleConn != null && oracleConn.isProxySession()) {
                try {
                    oracleConn.close(oracle.jdbc.OracleConnection.PROXY_SESSION);
                } catch (java.sql.SQLException sqlEx) {
                    throw new RuntimeException(sqlEx);
                }
            }
            accessor.decrementCallCount();
        }
    }

Passing in the User Credentials

Passing in the credentials required for opening the proxy session involves passing the Properties to the event listener.

Extras

The following are not require but are useful utilities when working with proxy authentication.

Configure OracleDataSource using Customizer

In some environments the creation of an OracleDataSource may be required. the following code uses a SessionCustomizer to replace the configured internal connection pool with an OracleDataSource using the provided configuration values.

            // create a data source using the supplied connection string
            OracleDataSource ods;
            try {
                ods = new OracleDataSource();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            ods.setURL(session.getLogin().getConnectionString());
            ods.setUser(session.getLogin().getUserName());
            ods.setPassword("tiger");

            // substitute connector with the new one using the created data source 
            session.getLogin().setConnector(new JNDIConnector(ods));

            // make sure to have external connection pooling flag set 
            session.getLogin().setUsesExternalConnectionPooling(true);
            // This line is required to ensure that the connections being proxied are not shared
            ((ServerSession)session).getDefaultConnectionPolicy().setShouldUseExclusiveConnection(true);