Can a user with only select privileges block transactions?  Yes!

Although this topic has been covered on the interweb already, I thought it was worth a mention because this fact had evaded me (and a team of experienced DBAs) until recently.

A session using an application support account, with resource limits and a “READ_ONLY” role, was found to be blocking another session’s transaction.  The user had selected a row with a popular GUI tool then accidentally clicked on a field in that row, which resulted in a select for update.

I was shocked, so I tried a simple experiment with the lock table command:

17:09:17 SYS@MYDB SQL> create user RO_USER identified by RO_USER;
User created.
17:09:56 SYS@MYDB SQL> grant create session to RO_USER;
Grant succeeded.
17:10:03 SYS@MYDB SQL> grant select on APP.key_table to RO_USER;
Grant succeeded.
17:10:24 RO_USER@MYDB SQL> lock table APP.key_table in exclusive mode;
Table(s) Locked.

Oracle has a few bugs logged for this, including the enhancement request Bug 6823286.
Hopefully they will fix this one day soon, so that privileges are checked before allowing lock table and select for update commands.


2 thoughts on “Read Only Users Can Lock Tables

  1. Yep this behavior kind of sucks … and unfortunately one can SELECT * FROM all_sequences FOR UPDATE too … which means that the moment anyone wants to fetch the next range of values for a (cached) sequence, they’ll be hung waiting for the TX lock. And there’s a special sequence which generates AUDSID values when non-SYSDBA users log on … so you can connect the dots from there 😉

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s