Make LocalSessionFactoryBean enforce read only on db connections

Question

Using spring/hibernate/C3P0 ComboPooledDataSource. I am on a legacy project with spring 2.5, hibernate 3.3 and newest C3P0.

I am using LocalSessionFactoryBean implementation.

I use the spring TransactionInterceptor to globally set the transaction attributes.

I am adding a second replicated database to be used for reporting queries only. This will be a read only database and I would like to set all the connections to be read only.

I was trying to create a second instance of LocalSessionFactoryBean that has data sources which reference secondary databases.
However, what I would like to do is set all these transactions to read only.

I was hoping there might be a way to do this in the ComboPooledDataSource datasource. For instance the apache commons BasicDataSource has a readOnly setting. The C3P0 one does not.

Next, I thought there might be a way to do it in the LocalSessionFactoryBean. But no luck finding that. Any ideas?

Answer 1

Create a database account that only has read-only access. That is the best way to guarantee that the user cannot write. Create a second data source for that account and have at it.

Trying to do this with Spring/Hibernate is not the proper way to do it. If you use the right tool for the job it will be much more clean, understandable and maintainable.