[ http://issues.apache.org/jira/browse/DERBY-1857?page=comments#action_12435272 ] Rajesh Kartha commented on DERBY-1857: --------------------------------------
Thanks a lot Yip, for those references, indeed helpful. I was a bit confused on that behaviour of revoking column references, since on DB2, it did not let me. db2 => revoke references (c1) on rt1 from public DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0206N "C1" is not valid in the context where it is used. SQLSTATE=42703 On a similar note, it will be very useful to know what the SQL standard states about dropping foreign key constraints when references on a table are revoked. Currently Derby drops fk constraints when references are revoked, whereas in some databases the constraints are not dropped. > Constraint got dropped incorrectly when a reference privilege is revoked. > ------------------------------------------------------------------------- > > Key: DERBY-1857 > URL: http://issues.apache.org/jira/browse/DERBY-1857 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.1.0, 10.3.0.0, 10.2.2.0 > Environment: Any > Reporter: Yip Ng > > 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|{0, 1} > 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} > 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) > ------------------------------------------------------ -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
