Skip to Main Content
  • Questions
  • I want know how to prevent select for update from users without update grant.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Fábio.

Asked: December 27, 2007 - 2:33 pm UTC

Last updated: January 29, 2008 - 2:27 am UTC

Version: 10.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,

Thank you for this space for doubts.

I want know how to prevent select for update from users without update grant.
Follow the example below:

create user test1 identified by test1;
grant connect,resource to test1;

create table test1.t1 ( c1 number);
insert into test1.t1 values (1);
commit;



create user test2 identified by test2;
grant connect to test2;
grant select on test1.t1 to test2;


SQL> conn test2/test2@DB
Conectado.

Sessão alterada.


Sessão alterada.

USER é "TEST2"
SQL> select * from test1.t1 for update;

        C1
----------
         1

1 linha selecionada.



In another session

SQL> conn test1/test1@DB
Conectado.

Sessão alterada.


Sessão alterada.

USER é "TEST1"
SQL> update test1.t1 set c1=2;


Even the user TEST2 haven't had any grant but select, he locked the rows. "TX - row lock contention"

How may I prevent this?

Thanks again,
Fábio Ferreira.

and Tom said...

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

good info

Doug, December 31, 2007 - 9:12 am UTC

I didn't see this one on your metablog entry on your blog - how did you dig this out? Also I wanted to point out that the Andy Campbell one seems to be gone - did he move?
Tom Kyte
January 01, 2008 - 6:15 pm UTC

I came across it via pete finnegans blog - he pointed to it. one day, I might update my metablog :)

Here's an explanation that Tom gave me a while back

neil, December 31, 2007 - 3:02 pm UTC

I had a similar example, Tom gave a great explanation.

on preview, I can't get my links right :/

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:57080440307803

A reader, January 02, 2008 - 9:38 am UTC


Session Hangs using select for update

Kumar, January 25, 2008 - 1:41 am UTC

We are using an logic that is select for update on userinfo table when user logs on,
There are some validations after which the userinfo table gets updated with the current date and time. Between select and update statements nothing much process but finishes in fraction of seconds. We have also a provision that using same user credentials multiple sessions can log on .
The logic is if any user trying to execute select for update statement and gets an exception i.e. ORA-00054: resource busy and acquire with NOWAIT specified the user session simply get a warning message to wait for some time.
Now problem is when ever same user trying to connect at same time we observed that
there is an table lock on session which is which fired the select for update but could see the logs other sessions got above exception i.e. handled.
We don't under stand why it happens even though there is only one session being executed above statement all the way.
Could you please tell us what could be the reason and how to resolve it.
Also how to diagnose this problem from backend I mean I want to see why this session gets hangs at update .
Tom Kyte
January 25, 2008 - 8:58 am UTC

It is excessively "not clear at all" what is happening or what you are doing.

I don't know how you implemented this logic - how does a user "get a warning message", the database itself doesn't know how to do that - so, this must be entirely in the application.

there is always a table lock taken, in row share exclusive mode, when you select for update, not just when you notice something. It has to be there, it is used to protect the table from DDL during this transaction. It blocks only DDL and other operations that attempt to exclusively lock this table.

ops$tkyte%ORA10GR2> create table t as select * from dual;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select lock_type, mode_held, (select nvl(max(object_name),LOCK_ID1) from user_objects where object_id = LOCK_ID1) obj
  2  from dba_locks where session_id = (select sid from v$mystat where rownum=1);

no rows selected

ops$tkyte%ORA10GR2> select * from t for update;

D
-
X

ops$tkyte%ORA10GR2> select lock_type, mode_held, (select nvl(max(object_name),LOCK_ID1) from user_objects where object_id = LOCK_ID1) obj
  2  from dba_locks where session_id = (select sid from v$mystat where rownum=1);

LOCK_TYPE                  MODE_HELD       OBJ
-------------------------- --------------- ------------------------------
DML                        Row-X (SX)      T
Transaction                Exclusive       524306

ops$tkyte%ORA10GR2> lock table t in exclusive mode;

Table(s) Locked.

ops$tkyte%ORA10GR2> select lock_type, mode_held, (select nvl(max(object_name),LOCK_ID1) from user_objects where object_id = LOCK_ID1) obj
  2  from dba_locks where session_id = (select sid from v$mystat where rownum=1);

LOCK_TYPE                  MODE_HELD       OBJ
-------------------------- --------------- ------------------------------
DML                        Exclusive       T
Transaction                Exclusive       524306


You would need to sketch this out much better, including for example - well, an example.

test cases must be:

a) concise
b) yet 100% complete, so we can run them
c) but most importantly small
d) but all there

Table Lock

Richard, January 28, 2008 - 6:05 am UTC

I am, I admit, a "Locking" know-nothing. In my defence, that's because Oracle does the work for me, or has so far, without my needing to get technical about locks in my job.

So, with my limitations now exposed, could you say, one way or the other, whether preventing table locks (as described in the link above) is (potentially) a "good" thing, and would only mean having to be a bit more "on the ball" with regard to truncates and other DDL? Or, is such prevention of table locking a big No-No?
Tom Kyte
January 29, 2008 - 2:27 am UTC

in general, you would never need to do it, no.