This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes How do I disallow update/truncate/alter/merge/drop queries ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "How do I disallow update/truncate/alter/merge/drop queries ?" Watch "How do I disallow update/truncate/alter/merge/drop queries ?" New topic
Author

How do I disallow update/truncate/alter/merge/drop queries ?

salvin francis
Ranch Hand

Joined: Jan 12, 2009
Posts: 917

(been a long time since i have posted to code ranch)

I have a requirement where a client should be allowed to type queries in a text box and the program would fetch the results. For security reasons, we are disallowing any DML statements.

Currently our application is using the oracle database (it may change in the future).

To my surprise, I have found that the following code:

works in oracle. I was under the impression that executeQuery would probably throw an exception.

Is String parsing the only way to prevent a DML statement in java ?

The reason i mentioned parsing cause I am pretty sure there are people who are going to try something like :


or worse:



My Website: [Salvin.in] Cool your mind:[Salvin.in/painting] My Sally:[Salvin.in/sally]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3434
    
  47

You need to create two account in Oracle database: one would serve to hold (own) the database objects, the other will be used by your application to connect to the database. The owner of objects will grant only the SELECT privilege on desired tables or views to the other account. Basically, this is it. Oracle has extensive, freely available documentation on various security features, you might be interested to read them. Other databases should allow similar setup, I think.

However, allowing users to directly execute their queries is great security risk. It's SQL injection without the need for actual injection. Make sure you really want to allow this. You should at the very least set up auditing in the database, so that you can find the culprit if someone wreaks havoc in your database; this is also possible to do using built-in tools in Oracle (you can pass user information from middle tier to Oracle audits somehow, though I do not know the details of this).
salvin francis
Ranch Hand

Joined: Jan 12, 2009
Posts: 917

Thanks for the quick response,
I think creating a user with read only rights seems to be the best option,
Auditing is a problem since its a web - based application and hence the server is the only client that executes the query.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3434
    
  47

salvin francis wrote:Auditing is a problem since its a web - based application and hence the server is the only client that executes the query.

Oracle actually has tools to solve this somehow (the middle tier can tell the database on which user's behalf is it executing the query). I don't know details, but it is certainly documented.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How do I disallow update/truncate/alter/merge/drop queries ?
 
Similar Threads
EJB place in SCEA exam
Regarding the SQL querry generation
Commit problem
How to carry out SQL query on Composite key OneToMany relationship tables
Java script is not populated on webpage.