NZ DBA: Oracle Database Tuning & Tips

May 31, 2012

Read Only Users Can Lock Tables

Filed under: Oracle Database,Security — Ari @ 5:20 am
Tags: , , ,

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.

About these ads

3 Comments »

  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 ;-)

    Comment by Tanel Poder — May 31, 2012 @ 4:21 pm | Reply

    • 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 | Reply

  2. Reblogged this on A Prévôt-Leygonie.

    Comment by Amaury Prévôt-Leygonie — March 18, 2014 @ 10:57 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: