[Firebird-devel] [FB-Tracker] Created: (CORE-4308) Prevent user that has no acces rights to some table `T` from creating FOREIGN KEY that references to this `T`.

[Pavel Z. (JIRA) <tr...@fi...>]

Prevent user that has no acces rights to some table `T` from creating FOREIGN KEY that references to this `T`.
--------------------------------------------------------------------------------------------------------------

                 Key: CORE-4308
                 URL: http://tracker.firebirdsql.org/browse/CORE-4308
             Project: Firebird Core
          Issue Type: Bug
            Reporter: Pavel Zotov
            Priority: Minor


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

           X          F01
============ ============
           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).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira