In Oracle: how can I tell if an SQL query will cause changes without executing it?

Question

I've got a string containing an SQL statement. I want to find out whether the query will modify data or database structure, or if it will only read data. Is there some way to do this?

More info: In our application we need to let the users enter SQL-queries, mainly as part of the applications report system. These SQL queries should be allowed to read whatever they like from the databse, but they shouldn't be allowed to modify anything. No updates, deletes insert, table drops, constraint removals etc.

As of now I only test whether the first word in the string is "select", but this is too constricting and too insecure.

Answer 1

You should grant only select privileges on your tables for the login used by the application to be sure.

Answer 2

Create a new user for that part of the application that only has select privileges. Bear in mind that you'll also need to create synonyms for all the tables/views that that "read-only" user will be able to view.

The "regular" part of your application will still be able to do other operations (insert, update, delete). Just the reporting will use the read-only user.

As Horacio suggests, it is also a good idea/practice to add "wrapper" views that only expose what you want to expose. Some sort of "public API". This can give you flexibility if you need to change the underlying tables and don't want to/can't change the reports to the new definitions of said tables. This might, however, be seen as a lot of "extra work".

Answer 3

I agree with others that the right thing to do is use a separate schema with limited access & privileges for those queries that should be read-only.

Another option, however, is to set the transaction read-only before executing the statement entered by the user (SET TRANSACTION READ ONLY).

Answer 4

Create VIEWS to expose the data to end users, this is worthy because of three things:

1. The end user doesn't know how really your database look like.
2. You may can provide a simpler way to extract some pieces of data.
3. You can create the view with a read-only constraint:

    CREATE VIEW items (name, price, tax)
          AS SELECT name, price, tax_rate
          FROM item
          WITH READ ONLY;

Answer 5

Something that has worked well for me in the past, but may not fit your situation:

• Use stored procedures to implement an API for the application. All modifications are done via that API. The procedures exposed to the front end are all complete units of work, and those procedures are responsible for rights enforcement.
• The users running the front end application are only allowed to call the API stored procedures and read data.
• Since the exposed API does complete units of work that correspond to actions the user could take via the GUI, letting them run the procedures directly doesn't get them any additional ability, nor allow them to corrupt the database accidently.

Answer 6

SELECT * FROM table FOR UPDATE works even with only SELECT privilege, and can still cause a lot of damage. If you want to be safe, the read only transactions are better.