Systems Engineering and RDBMS

Read Only Accounts in Oracle

Posted by decipherinfosys on January 30, 2008

While working on a project recently, a question came up about read only accounts.  As you might already be aware of, a user that has been granted only select access can still execute a “select for update” or even a “Lock Table” command to place locks on the table.  It comes as a surprise to many (me included) when first faced with such a scenario.  One would have thought that there would be enough granular level of privileges to make “select for update” and “lock table” different than just select privileges but that is not the case (haven’t tested in 11g yet).

This is true for not only direct user sessions but is also true for database links.  It is pretty easy to test this out:

SQL> create table lock_test(col1 varchar2(10));

Table created.

SQL> insert into lock_test values (‘ABCD’);
SQL> commit;

SQL> grant create session to testuser identified by testuser;

Grant succeeded.

SQL> grant select on lock_test to testuser;

Grant succeeded.

Now, let’s connect using this user and issue the select, select for update and Lock Table commands:

SQL> connect testuser/testuser@ani
SQL> select * from lock_test for update;


SQL> lock table lock_test in exclusive mode;

Table(s) Locked.


So, as you can see from above, using the “read only”user account, we were able to lock the table by using both the “select for update” as well as the “Lock Table” commands.

This is a potential security issue.  A would be hacker or a disgruntled employee, using a read only account, could easily wreak havoc on a database system by running a “select for update” statement on vital tables.  For 24×7 OLTP systems, this could mean serious down time and loss of $$$.  It would be up to the DBA’s responsible for the database to track down and kill the session or sessions that are running the select for update.  By the time the DBA’s get things under control, a lot of damage could have been done.  In less malicious situations, an innocent user may actually execute such a statement and cause down time unknowingly.

There are ways (should rather say workarounds) to make this user account a real “read only” user account.  One way to work around this issue is to create a view to mask the table and grant the select privilege directly on the view itself rather than the table.  In addition, you would also need to modify the view such that a select for update cannot be done on it.  You can choose to create a view like this:

create view vw_lock_test as select distinct col1 from lock_test;

And then when you try to do a select for update against it, you will get this error:

ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Another way to prevent the “select for update” issue is by having a log on trigger and within that trigger modifying the session to have “set transaction read only”.  That way, there will be no need to create the view with a DISTINCT clause since that is a bad option as well – forces sort operations un-necessarily.

So, the “select for update” issue is taken care of by this “workaround”.  What about the LOCK TABLE issue?  We can disable the table locks on that table:

SQL> Alter table lock_test disable table lock;

And now when the read only user account tries to issue a LOCK TABLE command, he will get this error:

ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

Be aware though that before doing any DDL or truncate commands on the tables with disabled locks, you would need to enable table lock on such tables which is a good thing in production environments anyways since it prevents any accidental drops/truncates of the tables.

Sorry, the comment form is closed at this time.

%d bloggers like this: