Auditing an Oracle database for security issues is very important. provides all of the information and tools that you will need Click here for details of Limited's detailed Oracle database security audit service Click here for details of Limited's Oracle Security Training Courses
There are 52 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog

Home » Archives » May 2005 » A select only user causing locks?

[Previous entry: "Very interesting undocumented feature on Amis"] [Next entry: "Marcel-Jan has an interesting tool on his site called SQL-Gotcha"]

A select only user causing locks?

May 13th, 2005 by Pete

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 =
- 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 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.

May 2005

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Weblog Home
Weblog Archives

Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

Atom 0.3 FEED
Powered by gm-rss 2.0.0

Valid XHTML 1.0!