?
Solved

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

Posted on 2007-07-29
11
Medium Priority
?
1,524 Views
Last Modified: 2013-12-11
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("rollback");
   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?

Regards,
Jason.
0
Comment
Question by:jaxox
11 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 1000 total points
ID: 19587316
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
0
 

Author Comment

by:jaxox
ID: 19587394
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?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19587402
>>but this one will involve the architecture change to the system.  

How? It involves creating an account with the correct access level
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:jaxox
ID: 19587472
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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19587631
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
0
 

Author Comment

by:jaxox
ID: 19589685
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?
0
 
LVL 2

Expert Comment

by:freeexpert
ID: 19590359
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?
0
 

Author Comment

by:jaxox
ID: 19590420
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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19590705
>>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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19591952
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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19628489
:-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question