How to create Read Only Connection in Java to oracle database?

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();
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?

Who is Participating?
The best way to ensure read-only is at the db level by correct db grants to an account. Use that account for your app
jaxoxAuthor Commented:
Thanks for your comment but this one will involve the architecture change to the system.  
Is there any shortcut or dirty way to do it?
>>but this one will involve the architecture change to the system.  

How? It involves creating an account with the correct access level
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

jaxoxAuthor Commented:
It's because I'm serving more than 20 applications in Production environment at this moment.
The suggested changes will lead to changes in their application database.  

I know your suggestion can do but will involve a lot of team cooperation, and too difficult to archieve in a few days.
If you've set up the datasource in Websphere correctly, it would actually only take one edit to your datasource config. i.e. it wouldn't require *any* changes to the code itself
jaxoxAuthor Commented:
I think you are suggesting to update the new DB user account information into Websphere datasources.

Indeed, the major changes is that the new DB user account itself.  
These accounts should be created by individual application team as there is a lot of plsql, view & table grant.  If I need to have this read only DB account, every team need to work.

Or if there is any setting to set the datasource to read only?
I assume all these applications use the same use account? Is it possible to remove privileges for that one user account, and then have the one application that own the data use a new account with more write permissions?
jaxoxAuthor Commented:
No because all applications have their own database user account.  The difficulties are to ask the application team to provide a read only database user account.  

Therefore, I try to find some way to add control thru my framework.
>>The difficulties are to ask the application team to provide a read only database user account.

Make them aware of the security implications. They may take a different attitude
Mark GeerlingsDatabase AdministratorCommented:
I have very little Java experince, so I don't know if Java offers an easy way to do this or not.  In the database, I agree with CEHJ, that is: if you want a user(or application)  to have read-only privileges, then set up a database user for that purpose (who owns no tables or views) then grant that user just select (read-only) privileges on the tables and views it needs access to.  That approach will work with Oracle regardless of the client used.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.