1 Reply Latest reply on Aug 18, 2017 4:35 PM by Scott Geranen

    How to correctly implement Oracle VPD Without Oracle Database Proxy Authentication

    Scott Geranen
    Scott Geranen Aug 4, 2017 6:46 PM

      We are implementing a Virtual Private Database and using EclipseLink in our application.  We are using WebLogic 12.1.3 with the built-in version of EclipseLink (2.5.2) and Oracle Database 12.1.0.2.0.  Our application requires JTA as we receive JMS messages, read/update the database, and send JMS messages within distributed transactions.

       

      http://www.eclipse.org/eclipselink/documentation/2.5/concepts/data_access004.htm clearly states that "EclipseLink does not support Oracle Database proxy authentication with JTA."  Therefore, we attempted to implement session event handlers per "Oracle VPD Without Oracle Database Proxy Authentication" in http://docs.oracle.com/middleware/1213/toplink/solutions/oracledb.htm#TLADG1060.  In our postAcquireExclusiveConnection event listener, we set the CLIENT_IDENTIFIER using:

       

                      new SQLCall("CALL DBMS_SESSION.SET_IDENTIFIER('" + callerPrincipal + "')");

       

      And we cleared the client CLIENT_IDENTIFIER in the preReleaseExclusiveConnection event listener using

       

                      new SQLCall("CALL DBMS_SESSION.CLEAR_IDENTIFIER()");

       

      However, transactions in this configuration would not have the CLIENT_IDENTIFIER principal assigned.  When we disabled the clear of the client CLIENT_IDENTIFIER in the preReleaseExclusiveConnection, but continued to assign the CLIENT_IDENTIFIER in the postAcquireExclusiveConnection event listener, transactions would then have a CLIENT_IDENTIFIER principal, but the value appears to be from the previous transaction.  Instead, we have it working (as far as we can tell so far) by setting it in the postBeginTransaction event and resetting it in the postCommitTransaction and postRollbackTransaction events.

       

      In case it is relevant, we have the following properties set in persistence.xml:

                 <property name="eclipselink.cache.shared.default" value="false"/>
                 <property name="eclipselink.jdbc.exclusive-connection.mode" value="Always" />
                 <property name="eclipselink.query-results-cache" value="false" />
                 <property name="eclipselink.target-server" value="WebLogic_10" />
                                     

      Questions:
      1. Could the problem we see with postAcquireExclusiveConnection be a bug in EclipseLink 2.5.2?
      2. Could we be missing a property to make postAcquireExclusiveConnection behave properly?
      3. Is it OK to use postBeginTransaction or are we going to run into other problems?
      4. Should we be using some other event such as postAcquireUnitOfWork?

        • 1. Re: How to correctly implement Oracle VPD Without Oracle Database Proxy Authentication
          Scott Geranen
          Scott Geranen Aug 18, 2017 4:35 PM (in response to Scott Geranen)

          Our initial approach in the original post would fail about 1% of the time when under stress with multiple concurrent threads with different principals.

           

          The technique we have working is to set the CLIENT_IDENTIFIER in the application as follows:

           

           

          The WebLogic DataSource deployment descriptor is configured with a oracle.ucp.ConnectionLabelingCallback class using the ‘connection-labelling-callback’ property.  The configure() method of the callback uses the JDBC API to assign the CLIENT_IDENTIFIER of the Oracle DB connection by calling.

           

           

          preparedStatement = connection.prepareStatement("CALL DBMS_SESSION.SET_IDENTIFIER(?)");

          preparedStatement.setString(1, callerPrincipal);

          preparedStatement.executeUpdate();

           

           

          The callback configure() method is called before a DataSource returns a pooled connection to an application.  

           

           

          The EJB is that is using JPA has an EJB method interceptor that retrieves the EJBContext caller principal and stores the caller principal in a location available to the ConnectionLabelingCallback when the configure method is called.

           

           

          Does anyone see where this would be a problem?