User with privilege to CREATE table can add foreign key on his table that
references to the other table (parent) on which he has no rights
------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4986
URL: http://tracker.firebirdsql.org/browse/CORE-4986
Project: Firebird Core
Issue Type: Bug
Reporter: 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)
--
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
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
