Issue Details (XML | Word | Printable)

Key: CORE-3736
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Pavel Zotov
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

WITH LOCK clause is allowed for users with read-only rights on some table, thus blocking others from updating this table

Created: 18/Jan/12 10:19 AM   Updated: 27/Mar/14 12:16 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 3.0 Initial, 2.0.6, 2.5.0, 2.1.4, 2.5.1
Fix Version/s: 2.0.7, 2.1.5, 2.5.2, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
ISQL Version: LI-V2.5.2.26390 Firebird 2.5
Server version:
LI-V2.5.2.26390 Firebird 2.5

SESSION #1 // sysdba

[firebird@firebirdG empbuild]$ isql employee.fdb
Database: employee.fdb
SQL> create user usrxxx password '123'; commit;
SQL> create table supermain(id int, f01 int); commit;
SQL> insert into supermain values(1,100);
SQL> insert into supermain values(2,200);
SQL> insert into supermain values(3,300);
SQL> commit;
SQL> create role readmain;
SQL> commit;
SQL> grant select on supermain to readmain;
SQL> commit;
SQL> grant readmain to usrxxx;
SQL> commit;


SESSION #2 // user USRXXX, role with read_only right

[firebird@firebirdG empbuild]$ isql employee.fdb -user usrxxx -pas 123 -role readmain
Database: employee.fdb, User: usrxxx, Role: READMAIN
SQL> update supermain set f01=1;
Statement failed, SQLSTATE = 28000
no permission for update/write access to TABLE SUPERMAIN
SQL> rollback;
SQL> select * from supermain for update with lock; -- <<<<<<<<<<<<<<<<< passed! why ? <<<<<<<<<<<<

          ID F01
============ ============
           1 100
           2 200
           3 300


SESSION #1 // sysdba

SQL> set transaction read committed no record_version no wait;
SQL> select * from supermain;

          ID F01
============ ============
Statement failed, SQLSTATE = 40001
lock conflict on no wait transaction
-deadlock
SQL> rollback;
SQL> set transaction read committed no wait;
SQL> update supermain set f01=-f01 order by id rows 1;
Statement failed, SQLSTATE = 40001
lock conflict on no wait transaction
-deadlock
SQL> rollback;
SQL> set transaction read committed no wait;
SQL> delete from supermain;
Statement failed, SQLSTATE = 40001
lock conflict on no wait transaction
-deadlock

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 18/Jan/12 10:36 AM
PostgreSQL requires both SELECT and UPDATE privileges for both kinds of row-level locks (FOR UPDATE and FOR SHARE). I think we should use the same protection.

Dmitry Yemanov added a comment - 18/Jan/12 11:06 AM
Oracle seems to work the same way as Firebird, i.e. not requiring an UPDATE privilege for SELECT FOR UPDATE.

Alexander Peshkov added a comment - 18/Jan/12 11:15 AM
And what does standard say about such case?

Dmitry Yemanov added a comment - 18/Jan/12 11:25 AM
Standard says nothing about locking rows inside SELECT. The FOR UPDATE clause is standard but it is not intended for modifications of any kind. WITH LOCK / FOR SHARE are non-standard vendor extensions. Oracle uses standard FOR UPDATE for non-standard behavior.

Alexander Peshkov added a comment - 18/Jan/12 11:33 AM
Very good - that means we can select reasonable (like POSTGRES) behavior

Alexander Peshkov added a comment - 19/Jan/12 03:36 PM
WITH LOCK is now denied if user is not granted update on relations in SELECT operator