[ 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
