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:12 SYS@MYDB SQL> conn RO_USER/RO_USER Connected. 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.
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
Comment by Tanel Poder — May 31, 2012 @ 4:21 pm |
Hi Tanel,
Yikes, it is worse than I thought. Create session privilege means DENY SESSION too!
The AUDSES$ sequence on my 11.2 test database caches 10,000 values, but that means locking ALL_SEQUENCES would only take an average of one minute to hang the busiest application I currently support.
Thanks for your comment – I appreciate your work!
Comment by Ari — June 6, 2012 @ 6:15 am |