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.