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…
View original post 40 mots de plus