Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    22

    Red face Unanswered: LOCK of SELECT FOR UPDATE Statement

    Hi,

    I create a query user QUERY, and grant CONNECT, SELECT ANY TABLE role/privileges to it, so it can query the data of PRDT user, but cann't modify. I set the O7_DICTIONARY_ACCESSIBILITY=FALSE.

    I am very surprise to find that the QUERY user can lock the PRDT data by SELECT ... FOR UPDATE statement, but it cannot submit the modification, so QUERY did not COMMIT/ROLLBACK the SELECT FOR UPDATE statement, and the lock is not released, other users cannot update the locked rows.

    Does anybody know how to prevent this SELECT FOR UPDATE statement from being executed by users without INSERT/UPDATE/DELETE privileges?

    Thanks for your help, and sorry for my poor English

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    An update statement acquires a special row-level lock called a "row-
    exclusive" lock, which means that for the period of time the update
    statement is executing, no other user in the database can view or
    change the data in the row. The select for update statement, acquires a more lenient lock called the "share row" lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes.
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Posts
    22
    Originally posted by satish_ct
    Hi,

    An update statement acquires a special row-level lock called a "row-
    exclusive" lock, which means that for the period of time the update
    statement is executing, no other user in the database can view or
    change the data in the row. The select for update statement, acquires a more lenient lock called the "share row" lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes.

    Hi,

    Thanks for your reply. But I think you make a mistake. An update statement acquires a special row-level lock called a "row-exclusive" lock, no other user in the database can change the data in the row before the transaction COMMIT or ROLLBACK, BUT other user can VIEW the data, BEFORE or AFTER the complete of the transaction.

  4. #4
    Join Date
    Nov 2003
    Posts
    22
    There's no any answer for my question yet.

    I just want to prevent the SELECT FOR UPDATE statement from executing by user without UPDATE privilege.

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    You are right - this was a real surprise!!

    In 9.0.1:

    SQL> create user test identified by test;

    User created.

    SQL> grant connect,select any table to test;

    Grant succeeded.

    SQL> conn test/test@oracle9i
    Connected.

    SQL> select * from ckmadmin.test for update;

    X
    ----------
    1

    And it blocks.

    So, you can't, I think, prevent a user with SELECT ANY TABLE to make a select for update and block other users.
    I've also tried to grant the select on the table only:

    SQL> revoke select any table from test;

    Revoke succeeded.

    SQL> conn ckmadmin/ckmadmin@oracle9i
    Connected.

    SQL> grant select on test to test;

    Grant succeeded.

    But it's exactly the same.

    I've also searched in comp.databases.oracle.server and found that others have come across this issue, but no solution.

    So I searched Metalink and found, in a forum, some more powerful way to lock a table:

    -----------------------------
    Hi. I don't know if I would call it a 'feature,' but it is not a bug and is expected behavior. Also refer to the supporting information for the GRANT command in the Oracle8i SQL Reference, Table 11-4 Object Privileges and the Operations They Authorize:

    "The following table privileges authorize operations on a table. Any one of following object privileges allows the grantee to lock the table in any lock mode with the LOCK TABLE statement."

    ...
    SELECT
    ...

    There is an enhancement request filed (721956) to limit this capability for the SELECT statement.
    ------------------------------

    And voila':

    SQL> lock table ckmadmin.test in exclusive mode;

    Table(s) Locked.

    I would call this a bug ... I don't know if that enhancement request found its way to the releases > 9.0.1.

    HTH
    Al

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Chaps,

    I think you need to consider the read consistency which Oracle (attempts to) deliver.

    At first sight it doesn't make sense for a "select only" user to cause blocking - except when you consider that the table on which the select took place will usually be part of a FK constraint/relationship.

    It *is* difficult to get one's head around it, unless you consider the difference between single select statements and transactions. In the case of transactions, read consistency should be maintained throughout the course of the entire transaction.

    Where should things stop? Should Oracle continue using rollback until all possible transactions have commited? Or should it enforce rollback usage beyond a different transactions commit point until all selects (within other transactions) have been satisfied?

    It's a wierd area and not one which I claim to fully understand just yet but I hope that helps!

    Cheers
    Bill

  7. #7
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi Jovih,

    Ur answer is, An update statement acquires a special row-level lock called a "row-exclusive" lock, no other user in the database can change the data in the row before the transaction COMMIT or ROLLBACK, BUT other user can VIEW the data, BEFORE or AFTER the complete of the transaction.

    When there is an EXCLUSIVE LOCK , No other lock can be held along with it (this includes both read & write locks). This is to ensure DATA consistency.
    SATHISH .

  8. #8
    Join Date
    Nov 2003
    Posts
    22
    Thanks for all of you.

    Alberto, I cannot use a SELECT ANY TABLE user to lock table by LOCK statement, whatever in EXCLUSIVE or SHARED mode:

    SQL> R
    1* LOCK TABLE ctais2.test IN EXCLUSIVE MODE
    LOCK TABLE ctais2.test IN EXCLUSIVE MODE
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    How can you do this? :

    SQL> lock table ckmadmin.test in exclusive mode;

    Table(s) Locked.


    our environment: ORACLE 9.2.0.3 , AIX 5L

  9. #9
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by Jovih

    How can you do this? :

    SQL> lock table ckmadmin.test in exclusive mode;

    Table(s) Locked.


    our environment: ORACLE 9.2.0.3 , AIX 5L
    1)I was in 9.0.1

    2) the owner of the table (ckmadmin) had bestowed 'grant' to the user performing the lock:

    grant select on test to test;

    Try(2), if you can't perform the lock, maybe 9.2 is different.

    HTH
    Al

  10. #10
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    You must be logged in w/ DBA or lock any table privs.

  11. #11
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    U might not have the privileges to execute LOCK on that table. Check with the Privileges.
    SATHISH .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •