Dynamic Query how to restrict only SELECT query only

Question

We are creating portal to write select statement for end user. As this is expected to use externally, we are allowing to use only SELECT query.

How can we restrict only SELECT query execution using jdbcTemplate?

Answer 1

How can we restrict only SELECT query execution using jdbcTemplate?

This is the wrong target for the restriction. If you want to restrict someone to only use SELECT queries then do it in the Oracle database rather than in the middle-tier, i.e. Java (or worse, trying to enforce the restriction on a client application).

If you have an Oracle user table_owner that, unsurprisingly, owns the tables and data that you are using then if you want to restrict the middle-tier to only using SELECT statements against those tables then do not let the middle-tier connect to the table_owner user; if you do then the database automatically assumes that a user has full permissions to read and modify everything that they own so there will be no restrictions on what you can do.

Instead:

1. Create a different user table_viewer
2. Then GRANT SELECT ON table_owner.table_name TO table_viewer
3. Then connect your Java application to the table_viewer user and you can use SELECT * FROM table_owner.table1 but you will be forbidden from using INSERT, UPDATE, DELETE, etc. statements against that table because the user you are connecting to does not have those privileges.
4. If you want to read other tables then GRANT the appropriate privileges to that user.

Then you will not need to modify the Java application (apart from maybe changing which user you are connecting to) and the database will ensure that the connected user is only doing what their granted permissions allow.

---

If you want ALL users to only be able to read from a table then:

ALTER TABLE table_owner.table_name READ ONLY;

Then the table can only be read and any attempted modifications will raise an exception (until a user with the appropriate privileges changes it back to read-write).

Answer 2

One simple solution would be to validate that the statement begins with "SELECT". Only if this is met, you should feed the statement to the JdbcTemplate.

Depending on your implementation, you might also want to forbid semicolons (";") inside the string provided by the user, so they cannot provide multiple statements (and thereby execute non-SELECT statements).

Answer 3

Your portal should only connect to a database user that is limited to read-only privileges in the database. Never, ever, ever connect an application to the account that actually owns database objects, especially if you're allowing any kind of ad-hoc SQL. The privileges of schema owners generally can't be restricted on the objects they own, so treat them like you would a superuser or admin account: not for public consumption.

On top of that, the input validations suggested by @yamass are spot on as well.