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.
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).
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.