In Oracle: how to determine if an SQL query will lead to changes without executing it?

Question

I have a string containing an SQL statement. I want to know if the query will change the data or the structure of the database, or if it will read only the data. Is there any way to do this?

Additional information: in our application, we must allow users to enter SQL queries, mainly as part of the application reporting system. These SQL queries should be allowed to read everything they like from the database, but they are not allowed to change anything. No updates, removes insert, table drops, constraint removal, etc.

At the moment, I am only checking if the first word in the string is "select", but it is too compressive and too uncertain.

Answer 1

Create a new user for that part of the application that has only privileges of choice. Keep in mind that you will also need to create synonyms for all tables / views that a read-only user can view.

The "normal" part of your application will still perform other operations (insert, update, delete). Only the report will use the user read-only.

According to Horacio, it would also be nice to practice adding a “wrapper” that reveals only what you want to expose. Some kind of "public API". This can give you flexibility if you need to change base tables and don’t want / cannot change reports for new definitions of specified tables. This, however, can be seen as "extra work."

Answer 2

You should provide only privileges of choice in your tables so that the login used by the application is sure.

Answer 3

I agree with others that the right thing is to use a separate scheme with limited access and privileges for those requests that should be read-only.

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

Answer 4

Create VIEWS to display data to end users, this is worthy of three things:

• The end user does not know what your database looks like.
• You can provide an easier way to extract some pieces of data.
• You can create a read-only view:

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

Answer 5

Something that worked for me in the past, but might not match your situation:

• Use stored procedures to implement the API for the application. All modifications are made through this API. Procedures open to the front are all complete units of work, and these procedures are responsible for enforcing rights.
• Users using the front-end application are allowed to call API stored procedures and read data.
• Since the open API performs complete units of work that correspond to actions that the user can perform through the graphical interface, allowing them to run procedures directly, they do not receive additional features and do not allow them to accidentally damage the database.

Answer 6

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