Pete Finnigan's Oracle Security Weblog

I saw an interesting post on the Oracle-l mailing list a week or so ago and made a note to talk about it here. The thread is titled "select only user causing locks?" and the first post is here. The poster said he has a user that can only select from objects but he discovered that the user was causing a lock. He did some research and found that a user only granted select access can issue a select for update to lock a table and also can even lock a table in exclusive mode. He went on to ask if this is true and is it possible to create a truly read only account.

One of the follow ups says : "And SELECT FOR UPDATE should be a separate object privilege next to =
SELECT." - This is a good point about separation of privilege. The original poster follows with a point that power users could sit there with read only user account and lock up an entire application effectively causing a Denial of Service.

Is this a security bug or a feature? - It does not make sense that a user could issue a select for update when he has no update privilege himself. But could Oracle accommodate this in its privilege structure?

The thread carries on in the May index of the Oracle-l list on freelists.org. The original poster suggests that "set transaction read only" stops the select for update in 9i but not the lock table statement. But ensuring that set transaction read only had been issued for all power users would not be easy to prevent them issuing a select for update statement.