Index: java/engine/org/apache/derby/impl/sql/conn/GenericAuthorizer.java =================================================================== --- java/engine/org/apache/derby/impl/sql/conn/GenericAuthorizer.java (revision 453692) +++ java/engine/org/apache/derby/impl/sql/conn/GenericAuthorizer.java (working copy) @@ -153,14 +153,57 @@ if( requiredPermissionsList != null && ! requiredPermissionsList.isEmpty() && !authorizationId.equals(dd.getAuthorizationDatabaseOwner())) { - for( Iterator iter = requiredPermissionsList.iterator(); - iter.hasNext();) + int ddMode = dd.startReading(lcc); + + /* + * The system may need to read the permission descriptor(s) + * from the system table(s) if they are not available in the + * permission cache. So start an internal read-only nested + * transaction for this. + * + * The reason to use a nested transaction here is to not hold + * locks on system tables on a user transaction. e.g.: when + * attempting to revoke an user, the statement may time out + * since the user-to-be-revoked transaction may have acquired + * shared locks on the permission system tables; hence, this + * may not be desirable. + * + * All locks acquired by StatementPermission object's check() + * method will be released when the system ends the nested + * transaction. + * + * In Derby, the locks from read nested transactions come from + * the same space as the parent transaction; hence, they do not + * conflict with parent locks. + */ + lcc.beginNestedTransaction(true); + + try { - ((StatementPermission) iter.next()).check( lcc, authorizationId, false); - } + try + { + // perform the permission checking + for (Iterator iter = requiredPermissionsList.iterator(); + iter.hasNext();) + { + ((StatementPermission) iter.next()).check(lcc, + authorizationId, false); + } + } + finally + { + dd.doneReading(ddMode, lcc); + } + } + finally + { + // make sure we commit; otherwise, we will end up with + // mismatch nested level in the language connection context. + lcc.commitNestedTransaction(); + } } - } - } + } + } private static StandardException externalRoutineException(int operation, int sqlAllowed) { @@ -282,4 +325,5 @@ if (userAccessLevel == NO_ACCESS) throw StandardException.newException(SQLState.AUTH_DATABASE_CONNECTION_REFUSED); } + } Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties (revision 453692) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties (working copy) @@ -2,6 +2,9 @@ ij.showNoConnectionsAtStart=true derby.database.sqlAuthorization=true +derby.locks.deadlockTimeout=5 +derby.locks.waitTimeout=2 + useextdirs=true # DataSource properties, only used if ij.dataSource is set Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (revision 453692) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (working copy) @@ -1978,3 +1978,52 @@ drop table t1Derby1847; set connection mamta3; select c3 from mamta2.t1Derby1847; + +-- DERBY-1716 +-- Revoking select privilege from a user times out when that user still have +-- a cursor open before the patch. +set connection user1; +drop table t1; +create table t1 (c varchar(1)); +insert into t1 values 'a', 'b', 'c'; +grant select on t1 to user2; +set connection user2; +autocommit off; +GET CURSOR crs1 AS 'select * from user1.t1'; +next crs1; +set connection user1; +-- should succeed without blocking +revoke select on t1 from user2; +set connection user2; +-- still ok to fetch. +next crs1; +next crs1; +close crs1; +commit; +-- should fail since select privilege got revoked +GET CURSOR crs1 AS 'select * from user1.t1'; +next crs1; +close crs1; +autocommit on; +-- repeat the scenario +set connection user1; +grant select on t1 to user2; +set connection user2; +autocommit off; +GET CURSOR crs1 AS 'select * from user1.t1'; +next crs1; +set connection user1; +-- should succeed without blocking +revoke select on t1 from user2; +set connection user2; +-- still ok to fetch. +next crs1; +next crs1; +close crs1; +commit; +-- should fail since select privilege got revoked +GET CURSOR crs1 AS 'select * from user1.t1'; +next crs1; +close crs1; +autocommit on; +set connection user1; Index: java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out (revision 453692) +++ java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out (working copy) @@ -3135,4 +3135,83 @@ ij(MAMTA2)> set connection mamta3; ij(MAMTA3)> select c3 from mamta2.t1Derby1847; ERROR: Failed with SQLSTATE 42X05 -ij(MAMTA3)> +ij(MAMTA3)> -- DERBY-1716 +-- Revoking select privilege from a user times out when that user still have +-- a cursor open before the patch. +set connection user1; +ij(USER1)> drop table t1; +ERROR: Failed with SQLSTATE 42Y55 +ij(USER1)> create table t1 (c varchar(1)); +0 rows inserted/updated/deleted +ij(USER1)> insert into t1 values 'a', 'b', 'c'; +3 rows inserted/updated/deleted +ij(USER1)> grant select on t1 to user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> autocommit off; +ij(USER2)> GET CURSOR crs1 AS 'select * from user1.t1'; +ij(USER2)> next crs1; +C +---- +a +ij(USER2)> set connection user1; +ij(USER1)> -- should succeed without blocking +revoke select on t1 from user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> -- still ok to fetch. +next crs1; +C +---- +b +ij(USER2)> next crs1; +C +---- +c +ij(USER2)> close crs1; +ij(USER2)> commit; +ij(USER2)> -- should fail since select privilege got revoked +GET CURSOR crs1 AS 'select * from user1.t1'; +ERROR: Failed with SQLSTATE 28508 +ij(USER2)> next crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> close crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> autocommit on; +ij(USER2)> -- repeat the scenario +set connection user1; +ij(USER1)> grant select on t1 to user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> autocommit off; +ij(USER2)> GET CURSOR crs1 AS 'select * from user1.t1'; +ij(USER2)> next crs1; +C +---- +a +ij(USER2)> set connection user1; +ij(USER1)> -- should succeed without blocking +revoke select on t1 from user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> -- still ok to fetch. +next crs1; +C +---- +b +ij(USER2)> next crs1; +C +---- +c +ij(USER2)> close crs1; +ij(USER2)> commit; +ij(USER2)> -- should fail since select privilege got revoked +GET CURSOR crs1 AS 'select * from user1.t1'; +ERROR: Failed with SQLSTATE 28508 +ij(USER2)> next crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> close crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> autocommit on; +ij(USER2)> set connection user1; +ij(USER1)>