Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Duplicate
-
10.2.1.6, 10.2.2.0, 10.3.1.4
-
None
-
None
-
Any
Description
The RT2FK foreign key constraint is dropped incorrectly. Although column reference privilege for c1 of table user1.rt1 is revoked from PUBLIC, the table user2.rt2 uses column user1.rt1.c2 for its foreign key reference.
ij version 10.3
ij> connect 'wombat;create=true' user 'user1' as user1;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij> drop table user2.rt2;
ERROR 42Y07: Schema 'USER2' does not exist
ij> drop table user1.rt1;
ERROR 42Y07: Schema 'USER1' does not exist
ij> create table rt1 (c1 int primary key not null, c2 int not null unique, c3 int not null);
0 rows inserted/updated/deleted
ij> insert into rt1 values (1,1,1);
1 row inserted/updated/deleted
ij> insert into rt1 values (2,2,2);
1 row inserted/updated/deleted
ij> insert into rt1 values (3,3,3);
1 row inserted/updated/deleted
ij> grant references (c2,c1) on rt1 to public;
0 rows inserted/updated/deleted
ij> select * from sys.syscolperms;
COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
e8d54087-010d-b308-280c-00000040b568|PUBLIC |USER1 |67d0407f-010d-b308-280c-00000040b568|r|
1 row selected
ij> connect 'wombat' user 'user2' as user2;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij(USER2)> create table rt2 (c1 int primary key not null, constraint rt2fk foreign key(c1) references user1.rt1(c2) );
0 rows inserted/updated/deleted
ij(USER2)> select * from sys.sysconstraints;
CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC&
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
88100081-010d-b308-280c-00000040b568|67d0407f-010d-b308-280c-00000040b568|SQL060915115100450 |P|2fb0c07e-010d-b308-280c-00000040b568|E|0
90304082-010d-b308-280c-00000040b568|67d0407f-010d-b308-280c-00000040b568|SQL060915115100451 |U|2fb0c07e-010d-b308-280c-00000040b568|E|1
020e0090-010d-b308-280c-00000040b568|b1c6c08e-010d-b308-280c-00000040b568|SQL060915115100900 |P|c9a3808d-010d-b308-280c-00000040b568|E|0
12564092-010d-b308-280c-00000040b568|b1c6c08e-010d-b308-280c-00000040b568|RT2FK |F|c9a3808d-010d-b308-280c-00000040b568|E|0
4 rows selected
ij(USER2)> insert into rt2 values 4;
ERROR 23503: INSERT on table 'RT2' caused a violation of foreign key constraint 'RT2FK' for key (4). The statement has been rolled back.
ij(USER2)> set connection user1;
ij(USER1)> revoke references (c1) on rt1 from public;
0 rows inserted/updated/deleted
ij(USER1)> select * from sys.syscolperms;
COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
e8d54087-010d-b308-280c-00000040b568|PUBLIC |USER1 |67d0407f-010d-b308-280c-00000040b568|r|
1 row selected
ij(USER1)> select * from sys.sysconstraints;
CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC&
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
88100081-010d-b308-280c-00000040b568|67d0407f-010d-b308-280c-00000040b568|SQL060915115100450 |P|2fb0c07e-010d-b308-280c-00000040b568|E|0
90304082-010d-b308-280c-00000040b568|67d0407f-010d-b308-280c-00000040b568|SQL060915115100451 |U|2fb0c07e-010d-b308-280c-00000040b568|E|0
020e0090-010d-b308-280c-00000040b568|b1c6c08e-010d-b308-280c-00000040b568|SQL060915115100900 |P|c9a3808d-010d-b308-280c-00000040b568|E|0
3 rows selected
ij(USER1)> set connection user2;
ij(USER2)> insert into rt2 values 4;
1 row inserted/updated/deleted
ij(USER2)>
sysinfo:
------------------ Java Information ------------------
Java Version: 1.4.2_12
Java Vendor: Sun Microsystems Inc.
Java home: c:\jdk142\jre
Java classpath: classes;.
OS name: Windows XP
OS architecture: x86
OS version: 5.1
Java user name: Yip
Java user home: C:\Documents and Settings\Yip
Java user dir: C:\work3\derby\trunk
java.specification.name: Java Platform API Specification
java.specification.version: 1.4
--------- Derby Information --------
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[C:\work3\derby\trunk\classes] 10.3.0.0 alpha - (446666)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [English/United States [en_US]]
Found support for locale: [de_DE]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [es]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [fr]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [it]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [ja_JP]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [ko_KR]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [pt_BR]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [zh_CN]
version: 10.3.0.0 alpha - (446666)
Found support for locale: [zh_TW]
version: 10.3.0.0 alpha - (446666)
------------------------------------------------------
Attachments
Issue Links
- relates to
-
-
- Open
-