Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 3121 invoked from network); 16 Sep 2006 20:44:13 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 16 Sep 2006 20:44:13 -0000 Received: (qmail 41441 invoked by uid 500); 16 Sep 2006 20:44:12 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 41414 invoked by uid 500); 16 Sep 2006 20:44:12 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 41404 invoked by uid 99); 16 Sep 2006 20:44:12 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 16 Sep 2006 13:44:12 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received: from [209.237.227.198] (HELO brutus.apache.org) (209.237.227.198) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 16 Sep 2006 13:44:11 -0700 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id C2C66714310 for ; Sat, 16 Sep 2006 20:40:22 +0000 (GMT) Message-ID: <12165997.1158439222768.JavaMail.jira@brutus> Date: Sat, 16 Sep 2006 13:40:22 -0700 (PDT) From: "Rajesh Kartha (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (DERBY-1857) Constraint got dropped incorrectly when a reference privilege is revoked. In-Reply-To: <2591588.1158350302378.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N [ 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