db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [jira] Closed: (DERBY-1857) Constraint got dropped incorrectly when a reference privilege is revoked.
Date Tue, 19 Sep 2006 14:37:22 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1857?page=all ]

Yip Ng closed DERBY-1857.
-------------------------

    Resolution: Duplicate

Duplicate to DERBY-1782.

> 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

        

Mime
View raw message