Open firebird-automations opened 8 years ago
firebird-automations
commented
8 years ago Commented by: Sean Leyne (seanleyne)
The issue is not the runtime error. Rather the issue is with: CREATE TABLE tdetl(id int primary key, pid int references tmain
By standard this DDL operation should have failed as John does not have either SELECT or REFERENCES rights to table tmain.
firebird-automations
commented
8 years ago firebird-automations
commented
8 years ago Commented by: Claudio Valderrama C. (robocop)
REFERENCES right is granted automatically for any created table due to backwards compatibility. Such is the behavior since Interbase 5 days.
firebird-automations
commented
8 years ago Commented by: Sean Leyne (seanleyne)
Claudio,
Are you saying that when new users are created they are automatically granted REFERENCES to existing tables?
Or, are you saying that REFERENCES are granted to all existing users when a new table is created?
In this case: - table tmain existed before user JOHN was created. - all grants to JOHN where revoke (so he should not have any rights, including REFERENCES)
firebird-automations
commented
8 years ago Commented by: Claudio Valderrama C. (robocop)
This is from memory, 15 years ago: At the time of FB1, the engine checked REFERENCES when the system needed to lookup a value in a FK. That was wrong according to Diane Brown (the SQL expert at Cognos) so I changed the engine to check it at DDL time. However, the right to create a FK was granted automatically to PUBLIC to avoid breaking scripts and schemas (this was done before FB and I don't remember that we deleted that code), since virtually nobody cared about REFERENCES, the most obscure of the SQL rights.
I don't remember how SQL "references" in rdb$user_privileges maps to GDML rights in rdb$security_classes.
I consider REFERENCES a nuisance mostly of academic interest but if the users want it, the code will have to be revisited. Too much water has passed under the bridge that any assumption from old versions may be void today.
Submitted by: @pavel-zotov
session #1 // SYSDBA
########
C:\MIX\firebird\QA\fbt-repo\tmp>isql /3333:e30 -user sysdba -pas masterkey Database: /3333:e30, User: SYSDBA SQL> create or alter user john password '123'; SQL> revoke all on all from john; Warning: ALL on ALL is not granted to JOHN. SQL> grant create table to john; ------------------------------ we give to him right to create OTHER table(s)... SQL> commit; SQL> create table tmain(id int primary key, x int); ----- ... but for THIS table user 'JOHN' has no any rights SQL> insert into tmain values(1, 100); SQL> insert into tmain values(2, 200); SQL> commit;
session #2 // JOHN
########
SQL> connect '/3333:e30' user john password '123'; Database: '/3333:e30', User: JOHN
SQL> select * from tmain; Statement failed, SQLSTATE = 28000 no permission for SELECT access to TABLE TMAIN ------------------------ OK, expected.
SQL> alter table tmain add constraint tmain_unq unique(x); Statement failed, SQLSTATE = 28000 unsuccessful metadata update -ALTER TABLE TMAIN failed -no permission for ALTER access to TABLE TMAIN ------------------------ OK, expected.
SQL> alter table tmain drop x; Statement failed, SQLSTATE = 28000 unsuccessful metadata update -ALTER TABLE TMAIN failed -no permission for ALTER access to TABLE TMAIN ------------------------ OK, expected.
SQL> create table tdetl(id int primary key, pid int references tmain); ---- ?? Why he can refer to TMAIN table ?? SQL> insert into tdetl values(111, 1); SQL> insert into tdetl values(112, 1); SQL> insert into tdetl values(113, 1); SQL> commit;
session #1 // SYSDBA
########
SQL> delete from tmain where id=1; Statement failed, SQLSTATE = 23000 violation of FOREIGN KEY constraint "INTEG_5" on table "TDETL" ------- Poor result... :'( -Foreign key references are present for the record -Problematic key value is ("ID" = 1)