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] Created: (DERBY-1857) Constraint got dropped incorrectly when a reference privilege is revoked.
Date Fri, 15 Sep 2006 19:58:22 GMT
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.2.2.0, 10.3.0.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