We are using Hibernate 3.3.1 version along with spring 3.0.5 and Oracle 11G. In current set up we have one connection and one hibernate configuration for the same, we use spring to inject the sessionFactory into the HibernateTransactionManager.

Now we are planning to implement Oracle VPD to increase security protection to sensitive data. Hence tables that holds sensitive infomration cannot be accessed by the same user which is used to access the ones that are not sensitive.

Could you please let me know is there any way to implement the hibernate configuration for this? In the current setup, both the Tables that holds sensivite and non sensitive info are having foreign key relation, hence retrieving one table will pull the child records from the other as well.

Is the same functionality can be acheived with modifying the hibernate configuration without altering the FK relation ship and the way the data retrieval implementaion in the code ?

There are multiple ways to implement such a design:

1. You can provide your own ConnectionProvider mechanism that sets the clientIdentifier prior to lending the connection to Hibernate.
2. You can use MultiTenancy so that the tenandId is the username. You can have two tenants: anonymous and the one assigned to the secured user. You have to make sure that you pass the context properly for the secured tenant ConnectopnProvider.