• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Bartender
Posts: 2911
150
Google Web Toolkit Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
(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:


 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2911
150
Google Web Toolkit Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic