You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rajesh Kartha (JIRA)" <de...@db.apache.org> on 2006/09/16 00:28:22 UTC
[jira] Commented: (DERBY-1857) Constraint got dropped incorrectly
when a reference privilege is revoked.
[ http://issues.apache.org/jira/browse/DERBY-1857?page=comments#action_12435141 ]
Rajesh Kartha commented on DERBY-1857:
--------------------------------------
ij(USER1)> revoke references (c1) on rt1 from public;
Shouldn' t this revoke statement fail, I think the revoke reference should have happened at the table level and not at the column level.
I would expect "revoke references on rt1 from public; " only to work.
Also looking at the function test org\apache\derbyTesting\functionTests\tests\lang\grantRevokeDDL.sql, it seems it is
expected to drop the foreign key constraints referencing the table when a revoke on the table is executed.
<snip>
set connection mamta2;
-- the following revoke should drop the foreign key reference by column t31ConstraintTest.c312
revoke references on t21ConstraintTest from mamta3;
set connection mamta3;
-- verify that foreign key reference by column t31ConstraintTest.c312 got dropped by inserting a row.
-- following should pass
insert into t31ConstraintTest values(1,3);
</snip>
Correct me, but the issue would be disallowing revoke on column references and throw an exception.
> 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