Restrict executeQuery in Java from data manipulation in an Oracle database

Question

I am developing a query browser in Java where I want to restrict users from manipulating data. So I am using executeQuery as I searched many questions and all are answered as executeQuery is used to select and does not allow data manipulation as this on link.

It's working just fine with MySQL but when its comes to Oracle I am getting the error

ORA 00900: invalid SQL statement

but the real problem is, in database the statement is updating the record. I am getting the same error for update, delete, insert and drop but all commands are manipulating the data in database.

Now I have the option to check whether my query string start with data manipulation keywords but I am trying to avoid that checking. It's working absolutely fine in MySSQL, so I amnot getting what is the issue with Oracle.

Below is the code sample:

Connection conn = null;
Statement stmt = null;
ResultSet query_set = null;
try {
    String query = "insert into users values(1,'name')";
    Class.forName ("oracleDriver"); //Oracle driver as our database is Oracle.
    conn = DriverManager.getConnection("oracleDbUrl", "dbUsername", "dbchecksum"); //login credentials to the database
    stmt = conn.createStatement();
    query_set = stmt.executeQuery(query);
} catch(Exception e) {
    e.printStackTrace();
}

Kindly suggest how I can restrict users from data manipulation without applying check on the query string and what is the reason behind this Oracle behavior.

Answer 1

The JDBC standard does not specify the executeQuery() method behaviour, apart from requiring it to throw an exception when the statement returns something other than a single result set. The Oracle driver seems to comply with this requirement. The method is intended to be used with queries, but nowhere in the the documentation is it stated that it should prevent you from using it with DML statements.

You can choose to handle this exception and roll back the transaction, if you want to prevent changes to your data. Obviously, you will have to disable autocommit for this to work.

The correct way to achieve what you want would be to grant only select privileges to the user ID your application connects with.