Couple of years ago an interesting fact floated up in Oracle-L – a regular user with only SELECT privilege on a table can successfully execute a SELECT FOR UPDATE against it, locking all rows and even lock the whole table using LOCK TABLE command. Locking a table in exclusive mode would stall all changes and selects against that table – effectively hanging all applications using that table. Pete Finnigan wrote a review of the issue in this blog entry.

This means that many of the “read only” accounts used by support or reporting users aren’t really that read only – these accounts could be used for a (hopefully) short denial of service attack and leaves another opportunity for human error to cause trouble in production environments. This issue applies both for direct user sessions and dblinks.

This behaviour came as news to me back then and no real solution for this issue has been proposed so far. So, while I do not provide any new information regarding the problem itself, I do propose a solution for it.

First lets start from reproducing the problem case. Note that I use two users, SYSTEM as the table owner and TEST as the “read only” user.

  [Read more...]