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/18 03:28:13 UTC
[jira] Created: (DERBY-1723) Database owner revokes select
privilege from a schema owner but owner is still able to select
Database owner revokes select privilege from a schema owner but owner is still able to select
---------------------------------------------------------------------------------------------
Key: DERBY-1723
URL: http://issues.apache.org/jira/browse/DERBY-1723
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.1.0
Environment: Sun JDK 1.4.2
Reporter: Yip Ng
Database owner attempts to revoke select privilege from a schema owner's own table but the owner later can still select from the revoked table. Behavior is inconsistent. e.g.:
ij version 10.2
ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij> connect 'jdbc:derby:wombat' user 'user2' as user2;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij(USER2)> create table tshared0 (i int);
0 rows inserted/updated/deleted
ij(USER2)> -- db owner tries to revoke select access from schema owner user2
set connection user1;
ij(USER1)> revoke select on user2.tshared0 from user2;
0 rows inserted/updated/deleted
ij(USER1)> set connection user2;
ij(USER2)> select * from user2.tshared0;
I
-----------
0 rows 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-1723) Database owner revokes select
privilege from a schema owner but owner is still able to select
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1723?page=comments#action_12429061 ]
Yip Ng commented on DERBY-1723:
-------------------------------
Yes, a warning on the revoke will make the behavior more clearer. However, the point of this scenario is to determine what a database owner can do. In the v5 spec of Grant and Revoke, under the database owner section, it states the following:
"User creating a database is referred to as Database Owner. A database owner has more privileges than a normal user of a database. Database owners alone can create multiple schemas in that database or create a schema to be owned by another user. She can also grant or revoke any object privilege on any database object to any user and can access all objects in the database without any explicit granting of access. It is also not possible to revoke any privilege from database owners. Database owners assume the authorizationId of other users while operating in their user schemas. Objects created by database owners in other user schemas would be owned by that user."
So, in the above scenario, a database owner was not able to revoke any object privilege on any database object to any user as what the spec states.
> Database owner revokes select privilege from a schema owner but owner is still able to select
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-1723
> URL: http://issues.apache.org/jira/browse/DERBY-1723
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Database owner attempts to revoke select privilege from a schema owner's own table but the owner later can still select from the revoked table. Behavior is inconsistent. e.g.:
> ij version 10.2
> ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:wombat' user 'user2' as user2;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER2)> create table tshared0 (i int);
> 0 rows inserted/updated/deleted
> ij(USER2)> -- db owner tries to revoke select access from schema owner user2
> set connection user1;
> ij(USER1)> revoke select on user2.tshared0 from user2;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user2;
> ij(USER2)> select * from user2.tshared0;
> I
> -----------
> 0 rows 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-1723) Database owner revokes select privilege
from a schema owner but owner is still able to select
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1723?page=all ]
Yip Ng closed DERBY-1723.
-------------------------
Resolution: Duplicate
I am used to the other RDBMSes where the DBA or database owner is able to revoke even the schema owner but since this is as designed, I am ok with closing it as duplicate to DERBY-1538.
> Database owner revokes select privilege from a schema owner but owner is still able to select
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-1723
> URL: http://issues.apache.org/jira/browse/DERBY-1723
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Database owner attempts to revoke select privilege from a schema owner's own table but the owner later can still select from the revoked table. Behavior is inconsistent. e.g.:
> ij version 10.2
> ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:wombat' user 'user2' as user2;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER2)> create table tshared0 (i int);
> 0 rows inserted/updated/deleted
> ij(USER2)> -- db owner tries to revoke select access from schema owner user2
> set connection user1;
> ij(USER1)> revoke select on user2.tshared0 from user2;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user2;
> ij(USER2)> select * from user2.tshared0;
> I
> -----------
> 0 rows 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-1723) Database owner revokes select
privilege from a schema owner but owner is still able to select
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1723?page=comments#action_12429153 ]
Satheesh Bandaram commented on DERBY-1723:
------------------------------------------
No one can revoke a privilege from object owner, including the owner herself or even the database owner. Deepa is right.. fixing DERBY-1538 will address this also. If anything thinks spec needs to be more clear, we can make it.
Hard to imagine every combination for the spec. May be documentation to be added needs to cover all these cases. I would mark this as a DUPLICATE.
> Database owner revokes select privilege from a schema owner but owner is still able to select
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-1723
> URL: http://issues.apache.org/jira/browse/DERBY-1723
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Database owner attempts to revoke select privilege from a schema owner's own table but the owner later can still select from the revoked table. Behavior is inconsistent. e.g.:
> ij version 10.2
> ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:wombat' user 'user2' as user2;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER2)> create table tshared0 (i int);
> 0 rows inserted/updated/deleted
> ij(USER2)> -- db owner tries to revoke select access from schema owner user2
> set connection user1;
> ij(USER1)> revoke select on user2.tshared0 from user2;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user2;
> ij(USER2)> select * from user2.tshared0;
> I
> -----------
> 0 rows 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-1723) Database owner revokes select
privilege from a schema owner but owner is still able to select
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1723?page=comments#action_12429048 ]
Deepa Remesh commented on DERBY-1723:
-------------------------------------
I think this behaviour will be clearer if we raise a warning when no privileges are revoked. It will also solve the inconsistent behaviour you found during self privilege revocation (DERBY-1538). It looks like DERBY-1582 will solve both these issues, right?
> Database owner revokes select privilege from a schema owner but owner is still able to select
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-1723
> URL: http://issues.apache.org/jira/browse/DERBY-1723
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Database owner attempts to revoke select privilege from a schema owner's own table but the owner later can still select from the revoked table. Behavior is inconsistent. e.g.:
> ij version 10.2
> ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:wombat' user 'user2' as user2;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER2)> create table tshared0 (i int);
> 0 rows inserted/updated/deleted
> ij(USER2)> -- db owner tries to revoke select access from schema owner user2
> set connection user1;
> ij(USER1)> revoke select on user2.tshared0 from user2;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user2;
> ij(USER2)> select * from user2.tshared0;
> I
> -----------
> 0 rows 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