How to create Read Only Connection in Java to oracle database?
Posted on 2007-07-29
How to create Read Only Connection in Java to oracle10g database?
I need to implement some control to the connection and grant to application team.
The senario are describe as follow:
One application can create 2 connection thru my modules under Websphere 6.
One belongs to her application and the other one belongs to others.
I would need to implement control the latter one such that the application can perform query only.
I've tried the following but problem happens.
1. I've tried the java.sql.Connection.setReadOnly(true). No effect at all. The method are of no use.
2. execute the following in the second connection:
ps = conn.createStatement();
ps.execute("SET TRANSACTION READ ONLY");
After doing this, the first connection throw exception when doing an update.
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction|null
It seems when transaction is set to read only, all connection under the same transaction will be affected.
I've an idea to provide own set of ReadOnly Statement, PreparedStatement, CallStatement such that application can only call executeQuery, but I think this is not a good option from perspective of future maintenance.
Can anyone help on this problem? Is there other alternatives?