New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
WITH LOCK clause is allowed for users with read-only rights on some table, thus blocking others from updating this table [CORE3736] #4081
Comments
Modified by: @AlexPeshkoffassignee: Alexander Peshkov [ alexpeshkoff ] |
Modified by: @pavel-zotovdescription: SESSION #1 // sysdba [firebird@firebirdG empbuild]$ isql employee.fdb SESSION #2 // user USRXXX, role with read_only right [firebird@firebirdG empbuild]$ isql employee.fdb -user usrxxx -pas 123 -role readmain
============ ============ SESSION #1 // sysdba SQL> set transaction read committed no record_version no wait;
============ ============ => ISQL Version: LI-V2.5.2.26390 Firebird 2.5 SESSION #1 // sysdba [firebird@firebirdG empbuild]$ isql employee.fdb SESSION #2 // user USRXXX, role with read_only right [firebird@firebirdG empbuild]$ isql employee.fdb -user usrxxx -pas 123 -role readmain
============ ============ SESSION #1 // sysdba SQL> set transaction read committed no record_version no wait;
============ ============ |
Commented by: @dyemanov 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. |
Commented by: @dyemanov Oracle seems to work the same way as Firebird, i.e. not requiring an UPDATE privilege for SELECT FOR UPDATE. |
Modified by: @dyemanovsummary: user with read_only right on some table can block others from updating this table => WITH LOCK clause is allowed for users with read-only rights on some table, thus blocking others from updating this table |
Commented by: @AlexPeshkoff And what does standard say about such case? |
Commented by: @dyemanov 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. |
Commented by: @AlexPeshkoff Very good - that means we can select reasonable (like POSTGRES) behavior |
Modified by: @dyemanovVersion: 2.5.1 [ 10333 ] Version: 2.1.4 [ 10361 ] Version: 2.5.0 [ 10221 ] Version: 2.0.6 [ 10303 ] Version: 3.0 Initial [ 10301 ] Version: 2.1.3 [ 10302 ] Version: 2.1.2 [ 10270 ] Version: 2.0.5 [ 10222 ] Version: 2.1.1 [ 10223 ] Version: 2.1.0 [ 10041 ] Component: Engine [ 10000 ] |
Commented by: @AlexPeshkoff WITH LOCK is now denied if user is not granted update on relations in SELECT operator |
Modified by: @AlexPeshkoffstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.0.7 [ 10390 ] Fix Version: 3.0 Alpha 1 [ 10331 ] Fix Version: 2.1.5 [ 10420 ] Fix Version: 2.5.2 [ 10450 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] Test Details: Confirmed getting exceptions: |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: @pavel-zotov
Is related to QA469
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 ? <<<<<<<<<<<<
============ ============
1 100
2 200
3 300
SESSION #1 // sysdba
SQL> set transaction read committed no record_version no wait;
SQL> select * from supermain;
============ ============
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
Commits: db697a5 a266343 12163ff 77d484a
====== Test Details ======
Confirmed getting exceptions:
335544352 : no permission for UPDATE access to TABLE T_READ_ONLY_FOR_NON_SYS
335544878 : concurrent transaction number is 806
The text was updated successfully, but these errors were encountered: