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 "Yip Ng (JIRA)" <de...@db.apache.org> on 2006/08/17 18:44:13 UTC
[jira] Created: (DERBY-1715) Revoke fails when grantee declares
additional view that is on top of the view to be dropped
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
[jira] Commented: (DERBY-1715) Revoke fails when grantee declares
additional view that is on top of the view to be dropped
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Closed: (DERBY-1715) Revoke fails when grantee declares
additional view that is on top of the view to be dropped
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1715?page=all ]
Yip Ng closed DERBY-1715.
-------------------------
Resolution: Duplicate
Duplicate to DERBY-1631.
> 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