Prevent user that has no acces rights to some table T
from creating FOREIGN KEY that references to this T
. [CORE4308]
#4631
Labels
T
from creating FOREIGN KEY that references to this T
. [CORE4308]
#4631
Submitted by: @pavel-zotov
Non-privileged user which has NO any rights to some table with PK can create new ("own") table with FOREIGN KEY and reference to the table that he even can not SELECT.
Perhaps, SELECT privilege for that table can help him to fill the child table with data faster and in such way he can forbid to owner of PARENT table to delete rows.
Scenario:
########
C:\MIX\firebird\fb25>isql 192.168.0.201/3252:employee
Database: 192.168.0.201/3252:employee
SQL> create user boss password 'boss'; commit;
SQL> create user junior password 'junior'; commit;
SQL> commit; connect '192.168.0.201/3252:employee' user 'boss' password 'boss';
Database: '192.168.0.201/3252:employee', User: boss
SQL> create table tboss(x int primary key, f01 int);
SQL> insert into tboss values(1, 100);
SQL> insert into tboss values(2, 200);
SQL> insert into tboss values(3, 300);
SQL> commit;
-- indeed not necessary but suppose that DBA gave such priv to BOTH users: tboss and tjunior
-- (or even BOSS really want JUNIOR reads - and only reads - such table);
SQL> grant select on tboss to junior; commit;
SQL> commit; connect '192.168.0.201/3252:employee' user 'junior' password 'junior';
Database: '192.168.0.201/3252:employee', User: junior
SQL>
SQL> show table tboss; -- BTW: why non-priv user can explore DDL of tables ?
X INTEGER Not Null
F01 INTEGER Nullable
CONSTRAINT INTEG_82:
Primary key (X)
SQL> select * from tboss; -- obtain data to write them into 'own` child table
============ ============
1 100
2 200
3 300
-- This statement PASSED OK. Why ?
SQL> create table tjunior(id int primary key, pid int, constraint tjunior_fk_tboss foreign key(pid)
CON> references tboss using index tjunior_fk_idx);
SQL> commit;
SQL> create sequence tjunior; commit;
SQL> insert into tjunior(id, pid) select gen_id(tjunior,1),x from tboss;
SQL> commit;
SQL>
SQL> commit; connect '192.168.0.201/3252:employee' user 'boss' password 'boss';
Database: '192.168.0.201/3252:employee', User: boss
SQL>
SQL> delete from tboss;
Statement failed, SQLSTATE = 23000
violation of FOREIGN KEY constraint "TJUNIOR_FK_TBOSS" on table "TJUNIOR"
-Foreign key references are present for the record
SQL> select * from tjunior;
Statement failed, SQLSTATE = 28000
no permission for read/select access to TABLE TJUNIOR
SQL> alter table tjunior drop constraint TJUNIOR_FK_TBOSS;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ERASE RDB$RELATION_CONSTRAINTS failed
-no permission for control access to TABLE TJUNIOR
-<Missing arg #1 - possibly status vector overflow>
It seems to me that user should NOT have any possibility to create FK with reference to tables in case he has no rights on it (to alter or drop).
The text was updated successfully, but these errors were encountered: