0

I agree that the database user privileges should be tuned to prevent him for running DML or DDL queries but I'm trying to do a similar thing from the client side. The JDBC side.

I downloaded Oracle driver found here.

While the driver and it's companion JARs are in the classpath, I ran the following piece test code:

Connection connection = DriverManager.getConnection(url, username, password);
connection.setAutoCommit(false);
// connection.setReadOnly(true); // Doesn't help

Statement statement = connection.createStatement();
statement.executeQuery("ALTER TABLE GELBANA_TEST.TABLE1 ADD HQ varchar2(40) DEFAULT 'NY'");

connection.rollBack();

But the column is added to the table !!

I can also drop the column. Shouldn't this be enough to prevent the user from running DML or DDL queries ? I need to stop him from making any changes to the database. He should be allowed to only select data. I checked the connection URL parameters for something that could reject such queries but I found nothing close.

Community
  • 1
  • 1
Muhammad Gelbana
  • 3,890
  • 3
  • 43
  • 81
  • 2
    1) The allowed actions of an user are controlled by the *privileges* granted to the user. 2) Performed DDL (e.g. alter table) has intergrated `commit`, so it can't be *rollbacked*. – Marmite Bomber Sep 05 '18 at 13:17
  • 4
    Unlike other DBMS, Oracle does **not** support transactional DDL. So you cannot roll back the effects of a DDL statement. Did you try `Connection.setReadOnly(true)` to prevent any changes? But I'm not sure if that can be overwritten through a SQL statement by the user –  Sep 05 '18 at 13:17
  • @a_horse_with_no_name, I tried that. Didn't work. Thank you all for the information. – Muhammad Gelbana Sep 05 '18 at 13:25
  • You could use 2 schemata/users: One how owns the tables and can change them, one for external access in which you only grant select (or update, insert, delete etc) for the objects in the other schemata. In that way user2 will not be able to change objects belonging to user1 but (can) have full access to the data itself. – evilive Sep 05 '18 at 13:33
  • @evilive That works on the database side - the OP said that they are "trying to do a similar thing from the client side". – MT0 Sep 05 '18 at 14:00
  • @MT0: Ouch! You're right... :( – evilive Sep 05 '18 at 14:26
  • Privilege configuration on the *client side* is not the best way how to set up security. – Marmite Bomber Sep 05 '18 at 16:26
  • hmm, well the solution will always be a hack on the client side i guess... but what about checking that the query starts with the keyword 'select'? will limit the user a bit (e.g. with-clause), but might do the trick for you? – Peter Sep 06 '18 at 06:48

0 Answers0