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