Oracle VPD in XA

[rebarone]

We are trying to use XA transactions with JBoss, Spring, Hibernate, and Oracle. With the basic setup, it appears that autoCommit is set to true, so that failed transactions can't be rolled back. By changing the setup, the failed transactions are rolled back, but the transaction uses multiple connections. Because we are using Oracle's "Virtual Private Database" (VPD), or "Fine Grained Access Control" (FGAC), it is imperative that a single connection is used for the entire transaction. The reason is that Oracle uses information that we place on the Connection's database session to determine access for DML.

For basic setup:
Here is the xa-datasource connection info:

<datasources>
 <xa-datasource>
 <!-- The jndi name of the DataSource, it is prefixed with java:/ -->
 <jndi-name>jdbc/XAOracleDS</jndi-name>

 <track-connection-by-tx>true</track-connection-by-tx>
 <application-managed-security>true</application-managed-security>
 <isSameRM-override-value>false</isSameRM-override-value>
 <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
 <xa-datasource-property name="URL">jdbc:oracle:thin:@server:port:SID</xa-datasource-property>
 <xa-datasource-property name="User">username</xa-datasource-property>
 <xa-datasource-property name="Password">pwd</xa-datasource-property>

 <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
 <!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->
 <!-- Checks the Oracle error codes and messages for fatal errors -->
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
 </exception-sorter-class-name>
 <prepared-statement-cache-size>5</prepared-statement-cache-size>
 <!-- Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa -->
 <no-tx-separate-pools/>

 <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
 <metadata>
 <type-mapping>Oracle9i</type-mapping>
 </metadata>

 </xa-datasource>

 <mbean code="org.jboss.resource.adapter.jdbc.vendor.
OracleXAExceptionFormatter" name="jboss.jca:service=OracleXAExceptionFormatter">
 <depends optional-attribute-name="TransactionManagerService">
jboss:service=TransactionManager
</depends>
 </mbean>

</datasources>

I'm using OpenSessionInViewFilter to set up transactions, with:
session.setFlushMode(FlushMode.NEVER);

This results in failed transactions NOT rolling back. If I implement a suggestion found on the web, changing hibernate.cfg.xml to add:

<property name="hibernate.connection.release_mode">after_statement</property>

It appears to set autocommit correctly, but since it ends up using different connections, the database session information I set after the Transaction was created with a callable statement, the Oracle VPD security isn't applied. I know it's using different connections because subsequent usage of the connection shows the database session information is set, but since it's coming from a pool, it's not neccessarily the security settings for the user who started the transaction.

We're using Oracle 10g drivers...

[charlesju01]

Hello,

Just Wonder if you have solved this problem. I have the same problem you had - I tried using Oracle VPD and Hibernate, but had the same problem.

Cheers

[rebarone]

We didn't get any help from the JBoss people. We did create our own connectionProvider to return the connections. We used ThreadLocal variables to set database session information as each connection was retreived. That meant that we had to use "after_statement" release_mode in hibernate, which is not what we wanted to do. It would be more efficient to use "after_transaction" or on_close", but this is not possible because of the auto_commit issue...