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
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
