aspose file tools*
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
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: 928

(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: 3611
    
  60

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: 928

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: 3611
    
  60

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 ?