[ http://issues.apache.org/jira/browse/DERBY-1715?page=comments#action_12428698 ] Mamta A. Satoor commented on DERBY-1715: ----------------------------------------
Yip, I think there is a similar issue in Jira DERBY-1631 to track this problem. If you agree, then, maybe we can close either this Jira entry or DERBY-1631 as a duplicate. > Revoke fails when grantee declares additional view that is on top of the view > to be dropped > ------------------------------------------------------------------------------------------- > > Key: DERBY-1715 > URL: http://issues.apache.org/jira/browse/DERBY-1715 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.0.0 > Environment: Sun JDK 1.4.2 > Reporter: Yip Ng > > Grantor attempts to revoke select privilege on a table from a user but the > user have declare multiple views in a hiearchical manner that references one > another. Revoke should succeed in this case but it is not. e.g.: > ij version 10.2 > ij> connect 'jdbc:derby:grt;create=true' user 'user1' as user1; > WARNING 01J14: SQL authorization is being used without first enabling > authentication. > ij> create table t1 (i int); > 0 rows inserted/updated/deleted > ij> insert into t1 values 1; > 1 row inserted/updated/deleted > ij> grant select on t1 to user2; > 0 rows inserted/updated/deleted > ij> connect 'jdbc:derby:grt' user 'user2' as user2; > WARNING 01J14: SQL authorization is being used without first enabling > authentication. > ij(USER2)> select * from user1.t1; > I > ----------- > 1 > 1 row selected > ij(USER2)> create view v1 as select * from user1.t1; > 0 rows inserted/updated/deleted > ij(USER2)> create view v2 as select * from user2.v1; > 0 rows inserted/updated/deleted > ij(USER2)> create view v3 as select * from user2.v2; > 0 rows inserted/updated/deleted > ij(USER2)> set connection user1; > ij(USER1)> revoke select on t1 from user2; > ERROR X0Y23: Operation 'DROP VIEW' cannot be performed on object 'V1' because > VIEW 'V2' is dependent on that object. > ERROR X0Y23: Operation 'DROP VIEW' cannot be performed on object 'V1' because > VIEW 'V3' is dependent on that object. > ij(USER1)> set connection user2; > ij(USER2)> select * from user1.t1; > I > ----------- > 1 > 1 row selected > ij(USER2)> select * from user2.v1; > I > ----------- > 1 > 1 row selected > ij(USER2)> select * from user2.v2; > I > ----------- > 1 > 1 row selected > ij(USER2)> select * from user2.v3; > I > ----------- > 1 > 1 row selected > ij(USER2)> > sysinfo: > ------------------ Java Information ------------------ > Java Version: 1.4.2_12 > Java Vendor: Sun Microsystems Inc. > Java home: C:\Program Files\Java\j2re1.4.2_12 > Java classpath: derby.jar;derbytools.jar > 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\tests\derby-10.2.1.0\lib > 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\tests\derby-10.2.1.0\lib\derby.jar] 10.2.1.0 beta - (430903) > [C:\work3\derby\tests\derby-10.2.1.0\lib\derbytools.jar] 10.2.1.0 beta - > (430903) > ------------------------------------------------------ > ----------------- Locale Information ----------------- > Current Locale : [English/United States [en_US]] > Found support for locale: [de_DE] > version: 10.2.1.0 - (430903) > Found support for locale: [es] > version: 10.2.1.0 - (430903) > Found support for locale: [fr] > version: 10.2.1.0 - (430903) > Found support for locale: [it] > version: 10.2.1.0 - (430903) > Found support for locale: [ja_JP] > version: 10.2.1.0 - (430903) > Found support for locale: [ko_KR] > version: 10.2.1.0 - (430903) > Found support for locale: [pt_BR] > version: 10.2.1.0 - (430903) > Found support for locale: [zh_CN] > version: 10.2.1.0 - (430903) > Found support for locale: [zh_TW] > version: 10.2.1.0 - (430903) > ------------------------------------------------------ -- 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
