db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r428930 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/depend/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/depend/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting...
Date Sat, 05 Aug 2006 00:22:40 GMT
Author: djd
Date: Fri Aug  4 17:22:39 2006
New Revision: 428930

URL: http://svn.apache.org/viewvc?rev=428930&view=rev
Log:
DERBY-1643 In Derby, the execute permission on a routine can't be revoked as long as there
are dependent objects
relying on that permissions. This patch implements that behavior by adding a new revoke invalidation
action
REVOKE_EXECUTE_PRIVILEGE, just for routine revoke. This invalidation gets sent when a revoke
execute is issued.
When dependents, a ViewDescriptor and/or a TriggerDescriptor, get this invalidation action,
they will
raise error that revoke execute can't be performed because there is a dependent object on
that permission. 

Patch submitted by Mamta Satoor

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
Fri Aug  4 17:22:39 2006
@@ -304,7 +304,15 @@
 
 	public static final int TRUNCATE_TABLE = 42;
 	public static final int DROP_SYNONYM = 43;
+	//A generic revoke action for TRIGGER, REFERENCES, SELECT, INSERT,
+	//  UPDATE and DELETE privileges. For all these privilege types,
+	//  a revoke statement causes the dependents to drop
 	public static final int REVOKE_PRIVILEGE = 44;
+	//A special action for revoke execute...restrict privilege. This is
+	//  because when revoke execute ... restrict is issued, the dependents
+	//  need to throw an exception. As long as there are dependent objects
+	//  on the execute privilege, the execute privilege can't be revoked.
+	public static final int REVOKE_EXECUTE_PRIVILEGE = 45;
 
     /**
      * Extensions to this interface may use action codes > MAX_ACTION_CODE without fear
of

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
Fri Aug  4 17:22:39 2006
@@ -553,7 +553,17 @@
 		    case DependencyManager.SET_CONSTRAINTS_DISABLE:
 		    case DependencyManager.SET_TRIGGERS_ENABLE:
 		    case DependencyManager.SET_TRIGGERS_DISABLE:
-				//for now, ignore revoke privilege action
+			//Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+			//In Derby, at this point, a constraint can't depend on a 
+		    //routine and hence a REVOKE_EXECUTE_PRIVILEGE invalidation
+		    //action should never be received by a ConstraintDescriptor.
+		    //Don't know how, but if it ever happened for some mysterious
+		    //reason, it will be caught in the default: case.
+			//
+			//For all the other types of revoke privileges, for instance,
+			//SELECT, UPDATE, DELETE, INSERT, REFERENCES, TRIGGER, we don't 
+			//do anything here and later in makeInvalid, we make the 
+			//ConstraintDescriptor drop itself. 
 		    case DependencyManager.REVOKE_PRIVILEGE:
 				break;
 
@@ -580,8 +590,19 @@
 	public void makeInvalid(int action, LanguageConnectionContext lcc) 
 		throws StandardException
 	{
+    	//Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+    	//This is because, in Derby, at this point, a constraint can't 
+		//depend on a routine and hence a REVOKE_EXECUTE_PRIVILEGE 
+		//invalidation action should never be received by a 
+		//ConstraintDescriptor. Don't know how, but if it ever happened 
+		//for some mysterious reason, it will be caught in prepareToInvalidate 
+		//method
 		if (action == DependencyManager.REVOKE_PRIVILEGE) 
 		{
+			//At this point (Derby 10.2), only a foreign key constraint can
+			//depend on a privilege. None of the other constraint types 
+			//can be dependent on a privilege becuse those constraint types
+			//can not reference a table/routine.
 			DropConstraintConstantAction.dropConstraintAndIndex(
 					getDataDictionary().getDependencyManager(),
 					table,

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
Fri Aug  4 17:22:39 2006
@@ -642,6 +642,15 @@
 		    case DependencyManager.DROP_SYNONYM:
 		    case DependencyManager.DROP_SPS:
 		    case DependencyManager.RENAME:
+		    //Derby supports only RESTRICT form of revoke execute and that
+		    //means that if there are any dependent objects on execute
+		    //permission on routine, revoke execute on that routine should
+		    //fail
+			//For all the other types of revoke privileges, for instance,
+			//SELECT, UPDATE, DELETE, INSERT, REFERENCES, TRIGGER, we don't 
+			//do anything here and later in makeInvalid, we make the 
+			//TriggerDescriptor drop itself. 
+		    case DependencyManager.REVOKE_EXECUTE_PRIVILEGE:
 				DependencyManager dm = getDataDictionary().getDependencyManager();
 				throw StandardException.newException(SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT, 
 									dm.getActionString(action), 
@@ -677,6 +686,12 @@
 		// that we would have to check against. This is hard to maintain,
 		// so don't bother.
 
+    	//Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+    	//This is because Derby supports only RESTRICT form of revoke 
+	    //execute and that means that if there are any dependent 
+	    //objects on execute permission on routine, revoke execute on 
+	    //that routine should fail. This behvaior for revoke execute
+	    //gets implemented in prepareToInvalidate method
 		if (action ==  DependencyManager.REVOKE_PRIVILEGE)
 		{
 		    DropTriggerConstantAction.dropTriggerDescriptor(

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java
Fri Aug  4 17:22:39 2006
@@ -267,7 +267,16 @@
 			*/
 		    case DependencyManager.SET_CONSTRAINTS_ENABLE:
 		    case DependencyManager.SET_TRIGGERS_ENABLE:
-				//ignore revoke privilege action for now
+		    //Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+		    //This is because Derby supports only RESTRICT form of revoke 
+		    //execute and that means that if there are any dependent objects 
+		    //on execute permission on routine, revoke execute on that 
+		    //routine should fail
+		    //
+		    //For all the other types of revoke privileges, for instance,
+		    //SELECT, UPDATE, DELETE, INSERT, REFERENCES, TRIGGER, we don't 
+		    //do anything here and later in makeInvalid, we make the 
+		    //ViewDescriptor drop itself. 
 		    case DependencyManager.REVOKE_PRIVILEGE:
 		    	break;
 
@@ -317,6 +326,12 @@
 			case DependencyManager.TRUNCATE_TABLE:
 				break;
 
+	    	//Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+	    	//This is because Derby supports only RESTRICT form of revoke 
+		    //execute and that means that if there are any dependent 
+		    //objects on execute permission on routine, revoke execute on 
+		    //that routine should fail. This behvaior for revoke execute
+		    //gets implemented in prepareToInvalidate method
 		    case DependencyManager.REVOKE_PRIVILEGE:
 				dropViewWork(getDataDictionary(), 
 						getDataDictionary().getDependencyManager(), lcc,

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
Fri Aug  4 17:22:39 2006
@@ -837,6 +837,9 @@
 
 		    case REVOKE_PRIVILEGE:
 			    return "REVOKE PRIVILEGE";
+
+		    case REVOKE_EXECUTE_PRIVILEGE:
+			    return "REVOKE EXECUTE PRIVILEGE";
 			    			   
 			default:
 				if (SanityManager.DEBUG)

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
Fri Aug  4 17:22:39 2006
@@ -79,7 +79,7 @@
 		{
 			String grantee = (String) itr.next();
 			if (dd.addRemovePermissionsDescriptor( grant, routinePermsDesc, grantee, tc))					
-        		dd.getDependencyManager().invalidateFor(routinePermsDesc, DependencyManager.REVOKE_PRIVILEGE,
lcc);
+        		dd.getDependencyManager().invalidateFor(routinePermsDesc, DependencyManager.REVOKE_EXECUTE_PRIVILEGE,
lcc);
 
 		}
 	} // end of executeConstantAction

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
Fri Aug  4 17:22:39 2006
@@ -710,6 +710,8 @@
 0 rows inserted/updated/deleted
 ij(MAMTA3)> -- Write some views based on a routine
 set connection mamta1;
+ij(MAMTA1)> drop function f_abs1;
+ERROR: Failed with SQLSTATE 42Y55
 ij(MAMTA1)> CREATE FUNCTION F_ABS1(P1 INT)
 	RETURNS INT NO SQL
 	RETURNS NULL ON NULL INPUT
@@ -721,6 +723,8 @@
 -----------
 5          
 1 row selected
+ij(MAMTA1)> drop view v11;
+ERROR: Failed with SQLSTATE X0X05
 ij(MAMTA1)> create view v11(c111) as values mamta1.f_abs1(-5);
 0 rows inserted/updated/deleted
 ij(MAMTA1)> grant select on v11 to mamta2;
@@ -731,6 +735,8 @@
 5          
 1 row selected
 ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop view v24;
+ERROR: Failed with SQLSTATE X0X05
 ij(MAMTA2)> create view v24 as select * from mamta1.v11;
 0 rows inserted/updated/deleted
 ij(MAMTA2)> select * from v24;
@@ -738,6 +744,8 @@
 -----------
 5          
 1 row selected
+ij(MAMTA2)> drop view v25;
+ERROR: Failed with SQLSTATE X0X05
 ij(MAMTA2)> -- following will fail because no execute permissions on mamta1.f_abs1
 create view v25(c251) as (values mamta1.f_abs1(-1));
 ERROR: Failed with SQLSTATE 2850A
@@ -745,14 +753,44 @@
 ij(MAMTA1)> grant execute on function f_abs1 to mamta2;
 0 rows inserted/updated/deleted
 ij(MAMTA1)> set connection mamta2;
-ij(MAMTA2)> create view v25(c251) as (values mamta1.f_abs1(-1));
+ij(MAMTA2)> -- this view creation will pass now because have execute privileges on the
function
+create view v25(c251) as (values mamta1.f_abs1(-1));
 0 rows inserted/updated/deleted
 ij(MAMTA2)> select * from v25;
 C251       
 -----------
 1          
 1 row selected
-ij(MAMTA2)> -- try column level privileges and views
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- try revoke execute privilege. Since there are dependent objects, the revoke
shold fail
+revoke execute on function f_abs1 from mamta2 restrict;
+ERROR: Failed with SQLSTATE X0Y23
+ij(MAMTA1)> -- drop the dependent objects on the execute privilege and then try to revoke
the execute privilege
+set connection mamta2;
+ij(MAMTA2)> drop view v25;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- revoke execute privilege should pass this time because no dependents on
that permission.
+revoke execute on function f_abs1 from mamta2 restrict;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- following select should still pass because v24 is not directly dependent
on the execute permission.
+--   It gets to the routine via view v11 which will be run with definer's privileges and
definer of
+--   view v11 is also the owner of the routine
+select * from v24;
+C111       
+-----------
+5          
+1 row selected
+ij(MAMTA2)> -- cleanup
+drop view v24;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop view v11;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop function f_abs1;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- try column level privileges and views
 -- In this test, user has permission on one column but not on the other
 set connection mamta1;
 ij(MAMTA1)> create table t14(c141 int, c142 int);
@@ -1772,7 +1810,13 @@
 insert into t21ConstraintTest values(3);
 1 row inserted/updated/deleted
 ij(MAMTA2)> -- cleanup
--- Miscellaneous test
+set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Miscellaneous test
 -- test1
 -- Have multiple objects depends on a privilege and make sure they all get dropped when that
privilege is revoked.
 set connection mamta1;
@@ -1879,6 +1923,8 @@
 1 row inserted/updated/deleted
 ij(MAMTA1)> insert into t11TriggerTest values(2);
 1 row inserted/updated/deleted
+ij(MAMTA1)> drop table t12RoutineTest;
+ERROR: Failed with SQLSTATE 42Y55
 ij(MAMTA1)> create table t12RoutineTest (c121 int);
 0 rows inserted/updated/deleted
 ij(MAMTA1)> insert into t12RoutineTest values (1),(2);
@@ -2074,6 +2120,45 @@
 1          
 1          
 4 rows selected
+ij(MAMTA1)> delete from t13TriggerTest;
+4 rows inserted/updated/deleted
+ij(MAMTA1)> -- Trying to revoke execute privilege below will fail because mamta3 has created
a trigger based on that permission.
+-- Derby supports only RESTRICT form of revoke execute. Which means that it can be revoked
only if there are no
+-- objects relying on that permission
+revoke execute on function selectFromSpecificSchema from mamta3 restrict;
+ERROR: Failed with SQLSTATE X0Y25
+ij(MAMTA1)> -- now try the insert and make sure the insert trigger still fires
+set connection mamta2;
+ij(MAMTA2)> insert into mamta3.t31TriggerTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- If number of rows returned by following select is 1, then we know insert
trigger did get fire.
+-- Insert's trigger's action is to insert into following table. 
+select * from t13TriggerTest;
+C131       
+-----------
+1          
+1 row selected
+ij(MAMTA1)> set connection mamta3;
+ij(MAMTA3)> -- drop the trigger manually
+drop trigger tr31t31;
+0 rows inserted/updated/deleted
+ij(MAMTA3)> set connection mamta1;
+ij(MAMTA1)> -- Now, we should be able to revoke execute permission on routine because
there are no dependent objects on that permission
+revoke execute on function selectFromSpecificSchema from mamta3 restrict;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta3;
+ij(MAMTA3)> -- cleanup
+drop table t31TriggerTest;
+0 rows inserted/updated/deleted
+ij(MAMTA3)> set connection mamta1;
+ij(MAMTA1)> -- cleanup
+drop table t12RoutineTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t13TriggerTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop function selectFromSpecificSchema;
+0 rows inserted/updated/deleted
 ij(MAMTA1)> -- Test routine and view combination. Thing to note is views always
 --   run with definer's privileges whereas routines always run with
 --   session user's privileges. So, eventhough a routine might be
@@ -2082,7 +2167,7 @@
 --   privileges will get used.
 set connection mamta1;
 ij(MAMTA1)> drop table t12RoutineTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42Y55
 ij(MAMTA1)> create table t12RoutineTest (c121 int);
 0 rows inserted/updated/deleted
 ij(MAMTA1)> insert into t12RoutineTest values (1),(2);
@@ -2090,7 +2175,7 @@
 ij(MAMTA1)> grant select on t12RoutineTest to mamta3;
 0 rows inserted/updated/deleted
 ij(MAMTA1)> drop function selectFromSpecificSchema;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42X94
 ij(MAMTA1)> CREATE FUNCTION selectFromSpecificSchema (P1 INT)
         RETURNS INT 
         RETURNS NULL ON NULL INPUT
@@ -2154,7 +2239,7 @@
 0 rows inserted/updated/deleted
 ij(MAMTA2)> set connection mamta3;
 ij(MAMTA3)> drop table t31TriggerTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42Y55
 ij(MAMTA3)> create table t31TriggerTest (c311 int);
 0 rows inserted/updated/deleted
 ij(MAMTA3)> grant insert on t31TriggerTest to mamta4;
@@ -2222,7 +2307,7 @@
 ij(MAMTA3)> -- Test constraints
 set connection mamta1;
 ij(MAMTA1)> drop table t11ConstraintTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42Y55
 ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key);
 0 rows inserted/updated/deleted
 ij(MAMTA1)> insert into t11ConstraintTest values(1);
@@ -2233,7 +2318,7 @@
 0 rows inserted/updated/deleted
 ij(MAMTA1)> set connection mamta2;
 ij(MAMTA2)> drop table t21ConstraintTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42Y55
 ij(MAMTA2)> create table t21ConstraintTest (c111 int not null primary key);
 0 rows inserted/updated/deleted
 ij(MAMTA2)> insert into t21ConstraintTest values(1);
@@ -2471,6 +2556,89 @@
 1 row inserted/updated/deleted
 ij(MAMTA1)> -- cleanup
 drop table t11TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Define couple triggers on a table relying on privilege on different tables.
If a revoke is issued, only the dependent triggers
+--   should get dropped, the rest of the triggers should stay active.
+set connection mamta1;
+ij(MAMTA1)> drop table t11TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11TriggerRevokeTest (c111 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11TriggerRevokeTest values(1),(2);
+2 rows inserted/updated/deleted
+ij(MAMTA1)> grant INSERT on t11TriggerRevokeTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t12TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t12TriggerRevokeTest (c121 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t12TriggerRevokeTest values(1),(2);
+2 rows inserted/updated/deleted
+ij(MAMTA1)> grant INSERT on t12TriggerRevokeTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21TriggerRevokeTest (c211 int);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21TriggerRevokeTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr211t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+        insert into mamta1.t11TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> -- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr212t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+        insert into mamta1.t12TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21TriggerRevokeTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- there should be 1 new row in each of the tables because of 2 insert triggers
+select * from t11TriggerRevokeTest;
+C111       
+-----------
+1          
+2          
+99         
+3 rows selected
+ij(MAMTA1)> select * from t12TriggerRevokeTest;
+C121       
+-----------
+1          
+2          
+99         
+3 rows selected
+ij(MAMTA1)> delete from t11TriggerRevokeTest;
+3 rows inserted/updated/deleted
+ij(MAMTA1)> delete from t12TriggerRevokeTest;
+3 rows inserted/updated/deleted
+ij(MAMTA1)> -- only one trigger(tr211t21) should get dropped because of following revoke
+revoke insert on t11TriggerRevokeTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> insert into t21TriggerRevokeTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- there should be no row in this table
+select * from t11TriggerRevokeTest;
+C111       
+-----------
+0 rows selected
+ij(MAMTA1)> -- there should be one new row in mamta1.t12TriggerRevokeTest 
+select * from t12TriggerRevokeTest;
+C121       
+-----------
+99         
+1 row selected
+ij(MAMTA1)> -- cleanup
+set connection mamta2;
+ij(MAMTA2)> drop table t21TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t12TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t11TriggerRevokeTest;
 0 rows inserted/updated/deleted
 ij(MAMTA1)> --- Test automatic dropping of dependent permission descriptors when objects
they refer to is dropped.
 --- Dropping of a table, for example, should drop all table and column permission descriptors
on it.

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
Fri Aug  4 17:22:39 2006
@@ -482,25 +482,49 @@
 
 -- Write some views based on a routine
 set connection mamta1;
+drop function f_abs1;
 CREATE FUNCTION F_ABS1(P1 INT)
 	RETURNS INT NO SQL
 	RETURNS NULL ON NULL INPUT
 	EXTERNAL NAME 'java.lang.Math.abs'
 	LANGUAGE JAVA PARAMETER STYLE JAVA;
 values f_abs1(-5);
+drop view v11;
 create view v11(c111) as values mamta1.f_abs1(-5);
 grant select on v11 to mamta2;
 select * from v11;
 set connection mamta2;
+drop view v24;
 create view v24 as select * from mamta1.v11;
 select * from v24;
+drop view v25;
 -- following will fail because no execute permissions on mamta1.f_abs1
 create view v25(c251) as (values mamta1.f_abs1(-1));
 set connection mamta1;
 grant execute on function f_abs1 to mamta2;
 set connection mamta2;
+-- this view creation will pass now because have execute privileges on the function
 create view v25(c251) as (values mamta1.f_abs1(-1));
 select * from v25;
+set connection mamta1;
+-- try revoke execute privilege. Since there are dependent objects, the revoke shold fail
+revoke execute on function f_abs1 from mamta2 restrict;
+-- drop the dependent objects on the execute privilege and then try to revoke the execute
privilege
+set connection mamta2;
+drop view v25;
+set connection mamta1;
+-- revoke execute privilege should pass this time because no dependents on that permission.
+revoke execute on function f_abs1 from mamta2 restrict;
+set connection mamta2;
+-- following select should still pass because v24 is not directly dependent on the execute
permission.
+--   It gets to the routine via view v11 which will be run with definer's privileges and
definer of
+--   view v11 is also the owner of the routine
+select * from v24;
+-- cleanup
+drop view v24;
+set connection mamta1;
+drop view v11;
+drop function f_abs1;
 
 -- try column level privileges and views
 -- In this test, user has permission on one column but not on the other
@@ -1170,6 +1194,10 @@
 -- Will be fixed in a subsequent patch
 insert into t21ConstraintTest values(3);
 -- cleanup
+set connection mamta2;
+drop table t21ConstraintTest;
+set connection mamta1;
+drop table t11ConstraintTest;
 
 -- Miscellaneous test
 -- test1
@@ -1233,6 +1261,7 @@
 create table t11TriggerTest (c111 int not null primary key);
 insert into t11TriggerTest values(1);
 insert into t11TriggerTest values(2);
+drop table t12RoutineTest;
 create table t12RoutineTest (c121 int);
 insert into t12RoutineTest values (1),(2);
 grant select on t11TriggerTest to mamta2;
@@ -1327,6 +1356,32 @@
 set connection mamta1;
 select * from t12RoutineTest;
 select * from t13TriggerTest;
+delete from t13TriggerTest;
+-- Trying to revoke execute privilege below will fail because mamta3 has created a trigger
based on that permission.
+-- Derby supports only RESTRICT form of revoke execute. Which means that it can be revoked
only if there are no
+-- objects relying on that permission
+revoke execute on function selectFromSpecificSchema from mamta3 restrict;
+-- now try the insert and make sure the insert trigger still fires
+set connection mamta2;
+insert into mamta3.t31TriggerTest values(1);
+set connection mamta1;
+-- If number of rows returned by following select is 1, then we know insert trigger did get
fire.
+-- Insert's trigger's action is to insert into following table. 
+select * from t13TriggerTest;
+set connection mamta3;
+-- drop the trigger manually
+drop trigger tr31t31;
+set connection mamta1;
+-- Now, we should be able to revoke execute permission on routine because there are no dependent
objects on that permission
+revoke execute on function selectFromSpecificSchema from mamta3 restrict;
+set connection mamta3;
+-- cleanup
+drop table t31TriggerTest;
+set connection mamta1;
+-- cleanup
+drop table t12RoutineTest;
+drop table t13TriggerTest;
+drop function selectFromSpecificSchema;
 
 -- Test routine and view combination. Thing to note is views always
 --   run with definer's privileges whereas routines always run with
@@ -1515,6 +1570,7 @@
 drop table t11TriggerRevokeTest;
 
 
+
 -- Define a trigger on a table, then revoke a privilege on the table which trigger doesn't
 -- really depend on. The trigger still gets dropped automatically. This will be fixed in
 -- subsequent patch
@@ -1565,6 +1621,51 @@
 -- following insert won't fire an insert trigger because one doesn't exist
 insert into t11TriggerRevokeTest values(6);
 -- cleanup
+drop table t11TriggerRevokeTest;
+
+
+-- Define couple triggers on a table relying on privilege on different tables. If a revoke
is issued, only the dependent triggers
+--   should get dropped, the rest of the triggers should stay active.
+set connection mamta1;
+drop table t11TriggerRevokeTest;
+create table t11TriggerRevokeTest (c111 int);
+insert into t11TriggerRevokeTest values(1),(2);
+grant INSERT on t11TriggerRevokeTest to mamta2;
+drop table t12TriggerRevokeTest;
+create table t12TriggerRevokeTest (c121 int);
+insert into t12TriggerRevokeTest values(1),(2);
+grant INSERT on t12TriggerRevokeTest to mamta2;
+set connection mamta2;
+drop table t21TriggerRevokeTest;
+create table t21TriggerRevokeTest (c211 int); 
+insert into t21TriggerRevokeTest values(1);
+-- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr211t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+        insert into mamta1.t11TriggerRevokeTest values(99);
+-- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr212t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+        insert into mamta1.t12TriggerRevokeTest values(99);
+insert into t21TriggerRevokeTest values(1);
+set connection mamta1;
+-- there should be 1 new row in each of the tables because of 2 insert triggers
+select * from t11TriggerRevokeTest;
+select * from t12TriggerRevokeTest;
+delete from t11TriggerRevokeTest;
+delete from t12TriggerRevokeTest;
+-- only one trigger(tr211t21) should get dropped because of following revoke
+revoke insert on t11TriggerRevokeTest from mamta2;
+set connection mamta2;
+insert into t21TriggerRevokeTest values(1);
+set connection mamta1;
+-- there should be no row in this table
+select * from t11TriggerRevokeTest;
+-- there should be one new row in mamta1.t12TriggerRevokeTest 
+select * from t12TriggerRevokeTest;
+-- cleanup
+set connection mamta2;
+drop table t21TriggerRevokeTest;
+set connection mamta1;
+drop table t12TriggerRevokeTest;
 drop table t11TriggerRevokeTest;
 
 



Mime
View raw message