| Key: |
DERBY-1716
|
| Type: |
Bug
|
| Status: |
Closed
|
| Resolution: |
Fixed
|
| Priority: |
Major
|
| Assignee: |
Yip Ng
|
| Reporter: |
Yip Ng
|
| Votes: |
0
|
| Watchers: |
0
|
|
If you were logged in you would be able to see more operations.
|
|
|
|
File Attachments:
|
|
|
Environment:
|
Sun JDK 1.4.2
|
|
Issue Links:
|
Reference
|
|
|
|
This issue is related to:
|
|
DERBY-464
Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configurations.
|
|
|
|
|
|
|
| Issue & fix info: |
Patch Available
|
| Resolution Date: |
10/Oct/06 05:42 AM
|
|
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)
------------------------------------------------------
|
|
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)
------------------------------------------------------
|
Show » |
| Repository |
Revision |
Date |
User |
Message |
| ASF |
#453935 |
Sat Oct 07 15:33:24 UTC 2006 |
mikem |
DERBY-1716
contributed by Yip Ng
patch: derby1716-trunk-diff03.txt
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.
|
| Files Changed |
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
MODIFY
/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericAuthorizer.java
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
|
|
Revoking select privilege from a user times out when that user still have a cursor open.
