Why SELECT rights can be dangerous?


One thing i often see on Oracle Databases is, that the DBA create a read only user with only select rights to a schema, but is it really read only?
In this short example i will show you that this can be also dangerous, for example if you use a Java Application to access the read only data. Java Application can have a Isolation Level READ COMMITED Feature, which use a SELECT FOR Update insted of a normal SELECT to prevent dirty reads.

Test Case: Create a normal READ ONLY User and grant SELECT rights to another Schema:

SQL> create user usera identified by user;

User created.

SQL> grant connect to usera;

Grant succeeded.

SQL> grant select on scott.emp to usera;

Grant succeeded.

SQL> conn usera/user

SQL> select * from scott.emp for update;

To above Statements succeed without any error. So what happen when User scott tries to Update one Row in the emp Table?

SQL> update emp set sal=100 where empno = 7369;

SQL> select sid,serial#,username,blocking_session from v$session where username in ('SCOTT','USERA');

       SID    SERIAL# USERNAME                       BLOCKING_SESSION
---------- ---------- ------------------------------ ----------------
       366      15105 USERA
       394       8487 SCOTT                                       366

SELECT FOR UPDATE statement acquires exclusive TX lock on the rows (if any), this is a normal locking behavior. I found Bug 6823286 on My Oracle Support (created in 2008) but the tests above are from a database, and it seems that this bug is still open.

Beginning with 12c release of the database Oracle give you the option only to grant the read rights to a table, which prevent this issue. However after a migration this is a extra step, which must be done. Also keep in mind that many software vendors need time to update the DDL scripts for the application the deliver.

As always comments are welcome.


Ein Gedanke zu “Why SELECT rights can be dangerous?

  1. Pingback: Oracle 12c In-Memory Option im Patchset released

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:


Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s