Skip to content
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

Closed
firebird-automations opened this issue Jan 18, 2012 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

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 ? <<<<<<<<<<<<

      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

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

description: 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

=>

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Oracle seems to work the same way as Firebird, i.e. not requiring an UPDATE privilege for SELECT FOR UPDATE.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: 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

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

And what does standard say about such case?

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Very good - that means we can select reasonable (like POSTGRES) behavior

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 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 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

WITH LOCK is now denied if user is not granted update on relations in SELECT operator

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: 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 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA469 [ QA469 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment