select only user causing locks?

  • From: jungwolf <spatenau@xxxxxxxxx>
  • To: Oracle-L <Oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 29 Apr 2005 11:45:24 -0500

Hello all,

I just ran into another idiosyncrasy of oracle or maybe a long
misunderstanding on my part.

We have a read-only user that, not surprisingly, has only create
session grant, select object grants, and synonyms created for it by a
superuser.  Pretty standard.  This morning I discovered a process
waiting on a row lock, and it turns out the holder of the lock was the
read-only account.

A little research with some test accounts shows that, indeed, an
account with only select privs can issue a select for update to lock a
row, or even a lock table x in exclusive mode.  Trying to update the
locked row obviously results in "insufficient privileges", but until
the read-only account ends the transaction other users are unable to
modify the row.

Is this a well-known feature?  How does one create a truly read-only
account that isn't able to, you know, do anything besides select from

(still reeling from learning you can drop objects in readonly
tablespaces as well; what's this world coming to?)

Other related posts: