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 22:27:13 UTC
[jira] Created: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Revoking select privilege from a user times out when that user still have a cursor open.
----------------------------------------------------------------------------------------
Key: DERBY-1716
URL: http://issues.apache.org/jira/browse/DERBY-1716
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.0.0
Environment: Sun JDK 1.4.2
Reporter: Yip Ng
Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
open. i.e.:
ij version 10.2
ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij(USER3)> set connection user1;
ij(USER1)> create table t1001 (c varchar(1));
0 rows inserted/updated/deleted
ij(USER1)> insert into t1001 values 'a', 'b', 'c';
3 rows inserted/updated/deleted
ij(USER1)> grant select on t1001 to user3;
0 rows inserted/updated/deleted
ij(USER1)> set connection user3;
ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
ij(USER3)> next crs1;
C
----
a
ij(USER3)> set connection user1;
ij(USER1)> -- revoke select privilege while user3 still have an open cursor
revoke select on t1001 from user3;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(USER1)> select * from syscs_diag.lock_table;
XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
3 rows selected
ij(USER1)> set connection user3;
ij(USER3)> next crs1;
C
----
b
ij(USER3)> next crs1;
C
----
c
ij(USER3)> close crs1;
ij(USER3)>
Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
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] Assigned: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng reassigned DERBY-1716:
-----------------------------
Assignee: Yip Ng
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12437239 ]
Yip Ng commented on DERBY-1716:
-------------------------------
Hi Bryan, thanks for taking the time for reviewing the patch. For the clearCaches() part, it looks like the
permission cache handling are somewhat different from other caches. For instance, when a table gets
dropped, its permission descriptor gets removed from the cache in its constant action(DROP TABLE) via
DataDictionary's dropAllTableAndColPermDescriptors(). So they do get removed from the permission
cache.
I am trying another different approach so please do not commit the current patch just yet. I will post my findings
soon. Thanks.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.4
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-stat01a.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12436416 ]
Yip Ng commented on DERBY-1716:
-------------------------------
I debugged into the code abit today, I don't think the system is fetching the permission descriptor(s)
from the system catalog at compilation time for the stated problem of this jira. During bind phase,
the system just collects various permission(s) that are required to execute the select statement. It is
at the execution time of the statement that the descriptors are fetched from the data dictionary.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng updated DERBY-1716:
--------------------------
Derby Info: [Patch Available]
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.4
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-stat01a.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Resolved: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Mike Matrigali resolved DERBY-1716.
-----------------------------------
Resolution: Fixed
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.0.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.3.0.0
>
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-diff03.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt, derby1716-trunk-stat03.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng updated DERBY-1716:
--------------------------
Affects Version/s: 10.2.2.0
10.3.0.0
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.5, 10.3.0.0, 10.2.2.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12436405 ]
Yip Ng commented on DERBY-1716:
-------------------------------
Bryan, at compilation time, Derby uses an internal system nested transaction. After compilation is completed, this nested system
transaction is committed; therefore, the shared locks for system tables are released. However, at execution time of this jira case,
the activation class is querying the data dictionary for permission since those descriptors are not in the permission cache, therefore,
the shared locks are obtained for those system tables using the user transaction.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12457926 ]
Myrna van Lunteren commented on DERBY-1716:
-------------------------------------------
updated the 10.2 j9_foundation canon with revision 486380.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.3.0.0, 10.2.2.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.3.0.0, 10.2.2.0
>
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-diff03.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt, derby1716-trunk-stat03.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng updated DERBY-1716:
--------------------------
Attachment: derby1716-trunk-stat01a.txt
derby1716-trunk-diff01a.txt
Attaching initial patch derby1716-trunk-diff01a.txt for DERBY-1716. The patch fixes the stated problem of this jira where it now loads the auth id's permission descriptors into the permission cache at compilation time instead, so that at execution time, the system does not have to go through the system table(s) again and hold the shared locks till the end of user transaction. derbyall passes. Appreciate if someone can review the patch. Thanks.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.4
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-stat01a.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Mike Matrigali updated DERBY-1716:
----------------------------------
Fix Version/s: 10.3.0.0
Committed this patch to the trunk.
m3_ibm142:20>svn commit
Sending java\engine\org\apache\derby\impl\sql\conn\GenericAuthorizer.java
Sending java\testing\org\apache\derbyTesting\functionTests\master\grantRevokeDDL.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\grantRevokeDDL.sql
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\grantRevokeDDL_app.properties
Transmitting file data ....
Committed revision 453935.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.0.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.3.0.0
>
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-diff03.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt, derby1716-trunk-stat03.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12436391 ]
Bryan Pendleton commented on DERBY-1716:
----------------------------------------
Yip, are you saying that we use the *user's* transaction to read table and column
permissions information from the data dictionary? I would have expected us
to use some sort of internal "system" transaction for this kind of catalog fetch.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng updated DERBY-1716:
--------------------------
Description:
Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
open. i.e.:
ij version 10.2
ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij(USER3)> set connection user1;
ij(USER1)> create table t1001 (c varchar(1));
0 rows inserted/updated/deleted
ij(USER1)> insert into t1001 values 'a', 'b', 'c';
3 rows inserted/updated/deleted
ij(USER1)> grant select on t1001 to user3;
0 rows inserted/updated/deleted
ij(USER1)> set connection user3;
ij(USER3)> autocommit off;
ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
ij(USER3)> next crs1;
C
----
a
ij(USER3)> set connection user1;
ij(USER1)> -- revoke select privilege while user3 still have an open cursor
revoke select on t1001 from user3;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(USER1)> select * from syscs_diag.lock_table;
XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
3 rows selected
ij(USER1)> set connection user3;
ij(USER3)> next crs1;
C
----
b
ij(USER3)> next crs1;
C
----
c
ij(USER3)> close crs1;
ij(USER3)>
Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
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)
------------------------------------------------------
was:
Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
open. i.e.:
ij version 10.2
ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij(USER3)> set connection user1;
ij(USER1)> create table t1001 (c varchar(1));
0 rows inserted/updated/deleted
ij(USER1)> insert into t1001 values 'a', 'b', 'c';
3 rows inserted/updated/deleted
ij(USER1)> grant select on t1001 to user3;
0 rows inserted/updated/deleted
ij(USER1)> set connection user3;
ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
ij(USER3)> next crs1;
C
----
a
ij(USER3)> set connection user1;
ij(USER1)> -- revoke select privilege while user3 still have an open cursor
revoke select on t1001 from user3;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(USER1)> select * from syscs_diag.lock_table;
XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
3 rows selected
ij(USER1)> set connection user3;
ij(USER3)> next crs1;
C
----
b
ij(USER3)> next crs1;
C
----
c
ij(USER3)> close crs1;
ij(USER3)>
Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
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)
------------------------------------------------------
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Mike Matrigali updated DERBY-1716:
----------------------------------
Derby Info: (was: [Patch Available])
unchecking patch available, contributer has asked for this patch to not be applied yet.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.4
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-stat01a.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng updated DERBY-1716:
--------------------------
Derby Info: [Patch Available]
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.5, 10.3.0.0, 10.2.2.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng updated DERBY-1716:
--------------------------
Attachment: derby1716-trunk-stat02.txt
derby1716-trunk-diff02.txt
Attaching patch derby1716-trunk-diff02.txt for DERBY-1716.
After further analysis, I think the previous patch does not address all cases.
In particular, unlike other descriptors, when privilege(s) get revoked from user,
the statement is not subject to recompilation, so then we are back to square one
since the previous patch attempts to bring in the permission descriptor(s) into
the permission cache at compilation time to avoid reading from system tables at
execution time.
I believe the proper proposal fix is to use internal nested read-only transaction
when the system is reading permission descriptors from the system tables. At a
high level, a statement undergoes the following typical steps for it to get executed
by the system:
1. Statement Compilation Phase
a) Parse the statement
b) Bind the statement and collects required permissions for it to be executed.
c) Optimize the statement
d) Generate the activation for the statement
2. Statement Execution Phase
a) Check if the authoration id has the required privileges to execute the statement.
b) Execute the statement
The problem lies in permissions checking step at statement execution phase. Before a statement can be executed in SQL authorization mode, the authorization id's privileges needs to be check against the permission cache or if the privileges are not available in the cache, the system needs to read this metadata information from the system tables. But the system is using *user transaction* to do this, so the shared locks that got acquired by the user transaction may not get released immediately; therefore, leading to lock timeout when the grantor attempts to revoke the user's privilege. To resolve this issue, the system now will start an internal read-only nested transaction(same lock space as the parent transaction) to read permission related info from the system tables and release the shared locks
as soon as the permissions check is completed before statement execution. This tackles the root of the stated problem. derbyall passes. Appreciate if someone can review the code changes. Thanks.
Other interesting observations while going through the code:
(1) In the current implementation, privileges collection actually ends at the time where the constant action is created and not at bind time. e.g.: DROP TABLE has schema permission collected at that time.
(2) Permissions cache handling are different from other data dictionary caches. The other caches gets *ALL* cleared out either at startReading() or startWriting() time in Data Dictionary depending on the current cache mode. However, only the "affected" cached items in the permissions cache are removed and this logic is handled by the statements themselves and not the data dictionary.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.5, 10.3.0.0, 10.2.2.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12437107 ]
Bryan Pendleton commented on DERBY-1716:
----------------------------------------
Hi Yip, thanks for the patch!
I read through the code and it looks good. Thanks for the clear comments in the
addToPermissionsCache method and for the good tests.
I noticed as part of studying DERBY-1847 that DataDictionaryImpl.clearCaches()
does not clear the DataDictionary permissions cache. This seems wrong to me,
and although it probably isn't *directly* related to this JIRA issue, I still wanted to
bring it up since you've been studying this code, and get your opinion.
Regarding your patch, I noticed you called it an "initial patch". Do you feel that this
patch is ready for commit? Or do you think that there is additional work to do?
Are other reviewers looking at this patch?
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.4
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-stat01a.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Mike Matrigali updated DERBY-1716:
----------------------------------
could you regenerate this patch, I am getting failures in applying the test portions of the patch -
grantRevokeDDL.sql has been a hot file.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.0.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Rick Hillegas updated DERBY-1716:
---------------------------------
Fix Version/s: 10.2.2.0
Ported to 10.2 branch at subversion revision 480148.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.3.0.0, 10.2.2.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.3.0.0, 10.2.2.0
>
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-diff03.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt, derby1716-trunk-stat03.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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] Updated: (DERBY-1716) Revoking select privilege from a user
times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=all ]
Yip Ng updated DERBY-1716:
--------------------------
Attachment: derby1716-trunk-stat03.txt
derby1716-trunk-diff03.txt
Previous patch got stale, attaching derby1716-trunk-diff03.txt.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.3.0.0, 10.2.2.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-diff02.txt, derby1716-trunk-diff03.txt, derby1716-trunk-stat01a.txt, derby1716-trunk-stat02.txt, derby1716-trunk-stat03.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12436281 ]
Yip Ng commented on DERBY-1716:
-------------------------------
The shared lock on SYS.SYSTABLEPERMS is actually obtained by the activation itself during
execution time (at the start of fillResultSet() of the activation class) since the grantee's privileges
are not in the permission cache yet. Thus, the shared locks will last till end of the transaction.
This is done once and those privileges that were loaded from the data dictionary(DD) will be stored
in the permission cache for use later so that the system does not need to load those info again
from the DD. Perhaps these privilege info can be loaded at compilation time instead as an improvement,
so that at execution time, privilege related metadata can be retrieved from the permission cache instead
of from the DD.
protected ResultSet fillResultSet() throws StandardException
{
((Activation) this).getLanguageConnectionContext().getAuthorizer().authorize( (Activation) this, 1 );
return getResultSetFactory().getScrollInsensitiveResultSet( <snip> );
}
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12437108 ]
Bryan Pendleton commented on DERBY-1716:
----------------------------------------
Addendum to previous comment: the patch applies cleanly for me, and I
verified that, without the code change, the test case demonstrates the
bug, and with the code change, the test case passes. I am +1 to commit,
and I propose to commit this change if Yip feels that it is ready for commit.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.4
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby1716-trunk-diff01a.txt, derby1716-trunk-stat01a.txt
>
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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-1716) Revoking select privilege from a
user times out when that user still have a cursor open.
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1716?page=comments#action_12436295 ]
Yip Ng commented on DERBY-1716:
-------------------------------
Note that since the statement may be cached by the system and reuse by other users of different
connections, the suggested scheme of loading privilege(s) at compilation time may not eliminate the
DD access in all cases if the system were to load the privilege info based on the auth id that compiles
the statement.
> Revoking select privilege from a user times out when that user still have a cursor open.
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-1716
> URL: http://issues.apache.org/jira/browse/DERBY-1716
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Sun JDK 1.4.2
> Reporter: Yip Ng
>
> Revoking table select privilege from a user will time out if that user still have an open cursor on that table.
> Hence, a database owner will not be able to revoke select privilege from any user(s) if they still have a cursor
> open. i.e.:
> ij version 10.2
> ij> connect 'jdbc:derby:cs1;create=true' user 'user1' as user1;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> connect 'jdbc:derby:cs1' user 'user3' as user3;
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(USER3)> set connection user1;
> ij(USER1)> create table t1001 (c varchar(1));
> 0 rows inserted/updated/deleted
> ij(USER1)> insert into t1001 values 'a', 'b', 'c';
> 3 rows inserted/updated/deleted
> ij(USER1)> grant select on t1001 to user3;
> 0 rows inserted/updated/deleted
> ij(USER1)> set connection user3;
> ij(USER3)> autocommit off;
> ij(USER3)> GET CURSOR crs1 AS 'select * from user1.t1001';
> ij(USER3)> next crs1;
> C
> ----
> a
> ij(USER3)> set connection user1;
> ij(USER1)> -- revoke select privilege while user3 still have an open cursor
> revoke select on t1001 from user3;
> ERROR 40XL1: A lock could not be obtained within the time requested
> ij(USER1)> select * from syscs_diag.lock_table;
> XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 130 |TABLE|IS |SYSTABLEPERMS |Tablelock |GRANT|S |4 |NULL
> 130 |ROW |S |SYSTABLEPERMS |(1,7) |GRANT|S |2 |NULL
> 130 |TABLE|IS |T1001 |Tablelock |GRANT|T |1 |NULL
> 3 rows selected
> ij(USER1)> set connection user3;
> ij(USER3)> next crs1;
> C
> ----
> b
> ij(USER3)> next crs1;
> C
> ----
> c
> ij(USER3)> close crs1;
> ij(USER3)>
> Is there a reason why Derby still keep shared locks on SYS.SYSTABLEPERMS during fetch?
> 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