db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r425836 - 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/catalog/ engine/org/apache/derby/impl/sql/depend/ engine/org/apache/derby/impl/sq...
Date Wed, 26 Jul 2006 20:36:42 GMT
Author: djd
Date: Wed Jul 26 13:36:41 2006
New Revision: 425836

URL: http://svn.apache.org/viewvc?rev=425836&view=rev
Log:
DERBY-1539 Supports basic revoke functionality for triggers.
If revoke statement finds a trigger dependent on the table/column/routine
on which privilege is being revoked, the trigger will be dropped automatically.

Patch contributed 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/DataDictionary.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/catalog/DataDictionaryImpl.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/DropTriggerConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -31,7 +31,7 @@
 	Dependency Manager Interface
 	<p>
 	The dependency manager tracks needs that dependents have of providers. This
-	is a general purpose interface interface which is associated with a
+	is a general purpose interface which is associated with a
 	DataDictinary object; infact the dependencymanager is really the
 	datadictionary keeping track of dependcies between objects that it handles
 	(descriptors) as well as prepared statements.
@@ -304,6 +304,7 @@
 
 	public static final int TRUNCATE_TABLE = 42;
 	public static final int DROP_SYNONYM = 43;
+	public static final int REVOKE_PRIVILEGE = 44;
 
     /**
      * 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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -29,15 +29,10 @@
 
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.services.sanity.SanityManager;
-import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.catalog.DependableFinder;
 import org.apache.derby.catalog.Dependable;
 import org.apache.derby.iapi.services.io.StoredFormatIds;
-import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.sql.depend.DependencyManager;
-import org.apache.derby.iapi.sql.depend.Dependent;
-import org.apache.derby.iapi.sql.depend.Dependency;
-import org.apache.derby.iapi.sql.depend.Provider;
 import	org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
 
 /**
@@ -556,6 +551,8 @@
 		    case DependencyManager.SET_CONSTRAINTS_DISABLE:
 		    case DependencyManager.SET_TRIGGERS_ENABLE:
 		    case DependencyManager.SET_TRIGGERS_DISABLE:
+				//for now, ignore revoke privilege action
+		    case DependencyManager.REVOKE_PRIVILEGE:
 				break;
 
 			/*
@@ -581,6 +578,12 @@
 	public void makeInvalid(int action, LanguageConnectionContext lcc) 
 		throws StandardException
 	{
+		if (action == DependencyManager.REVOKE_PRIVILEGE) 
+		{
+			//for now, ignore revoke privilege action
+			return;
+		}
+
 		/*
 		** SET_CONSTRAINTS/TRIGGERS is the only valid action
 		*/

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=425836&r1=425835&r2=425836&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
Wed Jul 26 13:36:41 2006
@@ -1562,8 +1562,11 @@
      * @param grantee
      * @param tc
      *
+     * @return True means revoke has removed a privilege from system
+     * table and hence the caller of this method should send invalidation 
+     * actions to PermssionDescriptor's dependents.
      */
-    public void addRemovePermissionsDescriptor( boolean add,
+    public boolean addRemovePermissionsDescriptor( boolean add,
                                                  PermissionsDescriptor perm,
                                                  String grantee,
                                                  TransactionController tc)

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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -24,7 +24,6 @@
 import org.apache.derby.iapi.sql.depend.Dependent;
 import org.apache.derby.iapi.sql.depend.Provider;
 import org.apache.derby.iapi.error.StandardException;
-import org.apache.derby.catalog.ReferencedColumns;
 import org.apache.derby.catalog.UUID;
 import java.sql.Timestamp;
 
@@ -34,14 +33,12 @@
 import org.apache.derby.catalog.DependableFinder;
 import org.apache.derby.catalog.Dependable;
 import org.apache.derby.iapi.services.io.StoredFormatIds;
-import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.sql.depend.DependencyManager;
-import org.apache.derby.iapi.sql.depend.Dependent;
-import org.apache.derby.iapi.sql.depend.Dependency;
-import org.apache.derby.iapi.sql.depend.Provider;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
-import org.apache.derby.iapi.services.context.ContextManager;
 import org.apache.derby.iapi.services.context.ContextService;
+
+import org.apache.derby.impl.sql.execute.DropTriggerConstantAction;
+
 import java.io.ObjectOutput;
 import java.io.ObjectInput;
 import java.io.IOException;
@@ -634,8 +631,6 @@
 		LanguageConnectionContext	lcc
 	) throws StandardException
 	{
-		
-
 		switch (action)
 		{
 			/*
@@ -652,7 +647,7 @@
 									dm.getActionString(action), 
 									p.getObjectName(), "TRIGGER", name);
 
-			/*
+				/*
 			** The trigger descriptor depends on the trigger table.
 			** This means that we get called whenever anything happens
 			** to the trigger table. There are so many cases where this
@@ -681,6 +676,15 @@
 		// the trigger table, so there is a very large number of actions
 		// that we would have to check against. This is hard to maintain,
 		// so don't bother.
+
+		if (action ==  DependencyManager.REVOKE_PRIVILEGE)
+		{
+		    DropTriggerConstantAction.dropTriggerDescriptor(
+				lcc,getDataDictionary().getDependencyManager(), 
+				getDataDictionary(), lcc.getTransactionExecute(), this,
+				null);
+		    return;
+		}
 	}
 
 	/**

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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -266,7 +266,9 @@
 			*/
 		    case DependencyManager.SET_CONSTRAINTS_ENABLE:
 		    case DependencyManager.SET_TRIGGERS_ENABLE:
-				break;
+				//ignore revoke privilege action for now
+		    case DependencyManager.REVOKE_PRIVILEGE:
+		    	break;
 
 		    default:
 
@@ -312,6 +314,8 @@
 			case DependencyManager.UPDATE_STATISTICS:
 			case DependencyManager.DROP_STATISTICS:
 			case DependencyManager.TRUNCATE_TABLE:
+				//ignore revoke privilege action for now
+		    case DependencyManager.REVOKE_PRIVILEGE:
 				break;
 		
 		    default:

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=425836&r1=425835&r2=425836&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Wed Jul 26 13:36:41 2006
@@ -9920,14 +9920,19 @@
      * @param grantee
      * @param tc
      *
+     * @return True means revoke has removed a privilege from system
+     * table and hence the caller of this method should send invalidation 
+     * actions to PermssionDescriptor's dependents.
      */
-    public void addRemovePermissionsDescriptor( boolean add,
+    public boolean addRemovePermissionsDescriptor( boolean add,
                                                 PermissionsDescriptor perm,
                                                 String grantee,
                                                 TransactionController tc)
         throws StandardException
     {
-		// It is possible for grant statements to look like following
+        int catalogNumber = perm.getCatalogNumber();
+
+        // It is possible for grant statements to look like following
 		//   grant execute on function f_abs to mamata2, mamata3;
 		//   grant all privileges on t11 to mamata2, mamata3;
 		// This means that dd.addRemovePermissionsDescriptor will be called
@@ -9937,15 +9942,15 @@
 		// into the correct system table for the permission descriptor, the 
     	// permission descriptor's uuid gets populated with the uuid of 
     	// the row that just got inserted into the system table for mamta2
-		// Now, before dd.addRemovePermissionsDescriptor leaves so it can
-    	// get called for MAMTA3, we should reset the Permission Descriptor's 
-    	// uuid to null or otherwise, for the next call to 
-    	// dd.addRemovePermissionDescriptor, we will think that there is a 
-    	// duplicate row getting inserted for the same uuid.
-		// Same logic applies to ColPermsDescriptor
-    	
-        int catalogNumber = perm.getCatalogNumber();
-
+    	// Now, when dd.addRemovePermissionsDescriptor gets called again for
+    	// mamta3, the permission descriptor's uuid will still be set to
+    	// the uuid that was used for mamta2. If we do not reset the
+    	// uuid to null, we will think that there is a duplicate row getting
+    	// inserted for the same uuid. In order to get around this, we should 
+    	// reset the UUID of passed PermissionDescriptor everytime this method 
+    	// is called. This way, there will be no leftover values from previous
+    	// call of this method.
+    	perm.setUUID(null);    	
         perm.setGrantee( grantee);
         TabInfo ti = getNonCoreTI( catalogNumber);
         PermissionsCatalogRowFactory rf = (PermissionsCatalogRowFactory) ti.getCatalogRowFactory();
@@ -9975,7 +9980,7 @@
             	//No need to reset permission descriptor's uuid because
             	//no row was ever found in system catalog for the given
             	//permission and hence uuid can't be non-null
-                return;
+                return false;
             //We didn't find an entry in system catalog and this is grant so 
             //so that means we have to enter a new row in system catalog for
             //this grant.
@@ -9987,15 +9992,6 @@
         }
         else
         {
-        	if (!add)
-        	{
-        		//set the uuid of the passed permission descriptor to 
-        		//corresponding rows's uuid in permissions system table. The
-        		//permission descriptor's uuid is required to have the 
-        		//dependency manager send the revoke privilege action to
-        		//all the dependent objects on that permission descriptor.
-        		rf.setUUIDOfThePassedDescriptor(existingRow, perm);
-        	}
             // add/remove these permissions to/from the existing permissions
             boolean[] colsChanged = new boolean[ existingRow.nColumns()];
             boolean[] indicesToUpdate = new boolean[ rf.getNumIndexes()];
@@ -10006,11 +10002,19 @@
                 changedColCount = rf.removePermissions( existingRow, perm, colsChanged);
             if( changedColCount == 0)
             {
-            	//grant/revoke privilege didn't change anything and hence just
-            	//return after resetting the uuid in the permission descriptor
-            	perm.setUUID(null);
-                return;            	
+            	//grant/revoke privilege didn't change anything and hence 
+            	//just return
+                return false;            	
             }
+        	if (!add)
+        	{
+        		//set the uuid of the passed permission descriptor to 
+        		//corresponding rows's uuid in permissions system table. The
+        		//permission descriptor's uuid is required to have the 
+        		//dependency manager send the revoke privilege action to
+        		//all the dependent objects on that permission descriptor.
+        		rf.setUUIDOfThePassedDescriptor(existingRow, perm);
+        	}
             if( changedColCount < 0)
             {
                 // No permissions left in the current row
@@ -10039,8 +10043,12 @@
         Cacheable cacheEntry = getPermissionsCache().findCached( perm);
         if( cacheEntry != null)
             getPermissionsCache().remove( cacheEntry);
-    	//Before leaving, reset the uuid in the permission descriptor
-    	perm.setUUID(null);
+
+        //If we are dealing with grant, then the caller does not need to send 
+        //any invalidation actions to anyone and hence return false
+        if (add)
+        	return false;
+        return true;
     } // end of addPermissionsDescriptor
 
     /**
@@ -10086,7 +10094,7 @@
      */
     ColPermsDescriptor getUncachedColPermsDescriptor( ColPermsDescriptor key)
         throws StandardException
-    {
+    {                                                                       
     	if (key.getObjectID() == null)
     	{
     		//the COLPERMSID for SYSCOLPERMS is not known, so use tableid,

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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -835,6 +835,9 @@
 		    case DROP_SYNONYM:
 			    return "DROP SYNONYM";
 
+		    case REVOKE_PRIVILEGE:
+			    return "REVOKE PRIVILEGE";
+			    			   
 			default:
 				if (SanityManager.DEBUG)
 				{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTriggerConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTriggerConstantAction.java?rev=425836&r1=425835&r2=425836&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTriggerConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTriggerConstantAction.java
Wed Jul 26 13:36:41 2006
@@ -51,7 +51,7 @@
  *
  *	@author Jamie
  */
-class DropTriggerConstantAction extends DDLSingleTableConstantAction
+public class DropTriggerConstantAction extends DDLSingleTableConstantAction
 {
 
 	private final String			triggerName;
@@ -151,7 +151,7 @@
 		dropTriggerDescriptor(lcc, dm, dd, tc, triggerd, activation);
 	}
 
-	static void dropTriggerDescriptor
+	public static void dropTriggerDescriptor
 	(
 		LanguageConnectionContext	lcc,
 		DependencyManager 			dm,

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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -24,6 +24,7 @@
 import org.apache.derby.iapi.services.sanity.SanityManager;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
 import org.apache.derby.iapi.store.access.TransactionController;
+import org.apache.derby.iapi.sql.depend.DependencyManager;
 import org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor;
 import org.apache.derby.iapi.sql.dictionary.AliasDescriptor;
 import org.apache.derby.iapi.sql.dictionary.StatementRoutinePermission;
@@ -77,7 +78,9 @@
 		for( Iterator itr = grantees.iterator(); itr.hasNext();)
 		{
 			String grantee = (String) itr.next();
-			dd.addRemovePermissionsDescriptor( grant, routinePermsDesc, grantee, tc);
+			if (dd.addRemovePermissionsDescriptor( grant, routinePermsDesc, grantee, tc))					
+        		dd.getDependencyManager().invalidateFor(routinePermsDesc, DependencyManager.REVOKE_PRIVILEGE,
lcc);
+
 		}
 	} // end of executeConstantAction
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java?rev=425836&r1=425835&r2=425836&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java
Wed Jul 26 13:36:41 2006
@@ -26,6 +26,7 @@
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
 import org.apache.derby.iapi.store.access.TransactionController;
+import org.apache.derby.iapi.sql.depend.DependencyManager;
 import org.apache.derby.iapi.sql.dictionary.PermissionsDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TablePermsDescriptor;
 import org.apache.derby.iapi.sql.dictionary.ColPermsDescriptor;
@@ -128,11 +129,19 @@
 		{
 			String grantee = (String) itr.next();
 			if( tablePermsDesc != null)
-				dd.addRemovePermissionsDescriptor( grant, tablePermsDesc, grantee, tc);
+			{
+				if (dd.addRemovePermissionsDescriptor( grant, tablePermsDesc, grantee, tc))
+				{
+	        		dd.getDependencyManager().invalidateFor(tablePermsDesc, DependencyManager.REVOKE_PRIVILEGE,
lcc);
+				}
+			}
 			for( int i = 0; i < columnBitSets.length; i++)
 			{
 				if( colPermsDescs[i] != null)
-					dd.addRemovePermissionsDescriptor( grant, colPermsDescs[i], grantee, tc);					
+				{
+					if (dd.addRemovePermissionsDescriptor( grant, colPermsDescs[i], grantee, tc))					
+		        		dd.getDependencyManager().invalidateFor(colPermsDescs[i], DependencyManager.REVOKE_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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -1266,4 +1266,179 @@
 0 rows inserted/updated/deleted
 ij(MAMTA3)> alter table t31constrainttest add foreign key (c311, c312) references mamta1.t11constrainttest;
 0 rows inserted/updated/deleted
-ij(MAMTA3)> 
+ij(MAMTA3)> -- revoke of TRIGGERS and other privileges should drop dependent triggers
+set connection mamta1;
+ij(MAMTA1)> drop table t11TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11TriggerRevokeTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11TriggerRevokeTest values(1),(2);
+2 rows inserted/updated/deleted
+ij(MAMTA1)> -- mamta2 is later going to create an insert trigger on t11TriggerRevokeTest

+grant TRIGGER on t11TriggerRevokeTest 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)> -- following will pass because mamta2 has trigger permission on mamta1.t11TriggerRevokeTest
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> -- no data in the table in which trigger is going to insert
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+0 rows selected
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- insert trigger will fire
+insert into t11TriggerRevokeTest values(3);
+1 row inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- trigger inserted one row into following table
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+99         
+1 row selected
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- this revoke is going to drop dependent trigger
+revoke trigger on t11TriggerRevokeTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(4);
+1 row inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- no more rows inserted since last check
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+99         
+1 row selected
+ij(MAMTA2)> -- following attempt to create insert trigger again will fail because trigger
privilege has been revoked.
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+ERROR: Failed with SQLSTATE 28506
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> grant trigger on t11TriggerRevokeTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- following attempt to create insert trigger again will pass because mamta2
has got the necessary trigger privilege.
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> select * from t21TriggerRevokeTest;
+C211       
+-----------
+99         
+1 row selected
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- insert trigger should get fired
+insert into t11TriggerRevokeTest values(5);
+1 row inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- Should be one more row since last check because insert trigger is back
in action
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+99         
+99         
+2 rows selected
+ij(MAMTA2)> drop table t21TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- this revoke is going to drop dependent trigger
+revoke trigger on t11TriggerRevokeTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(6);
+1 row inserted/updated/deleted
+ij(MAMTA1)> -- cleanup
+drop table t11TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- 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
+set connection mamta1;
+ij(MAMTA1)> drop table t11TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11TriggerRevokeTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11TriggerRevokeTest values(1),(2);
+2 rows inserted/updated/deleted
+ij(MAMTA1)> grant SELECT on t11TriggerRevokeTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- mamta2 is later going to create an insert trigger on t11TriggerRevokeTest

+grant TRIGGER on t11TriggerRevokeTest 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)> -- following will pass because mamta2 has trigger permission on mamta1.t11TriggerRevokeTest
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> -- no data in the table in which trigger is going to insert
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+0 rows selected
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- insert trigger will fire
+insert into t11TriggerRevokeTest values(3);
+1 row inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- trigger inserted one row into following table
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+99         
+1 row selected
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- this revoke is going to drop dependent trigger on the table although dependent
trigger does not
+-- need this particular permission 
+-- WILL FIX THIS IN A SUBSEQUENT PATCH****************************************************************************************
+revoke SELECT on t11TriggerRevokeTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(4);
+1 row inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- no more rows inserted since last check
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+99         
+1 row selected
+ij(MAMTA2)> -- following attempt to create insert trigger again will pas because TRIGGER
privilege was never revoked.
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- insert trigger should get fired
+insert into t11TriggerRevokeTest values(5);
+1 row inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- Should be one more row since last check because insert trigger is back
in action
+select * from t21TriggerRevokeTest;
+C211       
+-----------
+99         
+99         
+2 rows selected
+ij(MAMTA2)> drop table t21TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- this revoke is going to drop dependent trigger
+revoke trigger on t11TriggerRevokeTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(6);
+1 row inserted/updated/deleted
+ij(MAMTA1)> -- cleanup
+drop table t11TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> 

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=425836&r1=425835&r2=425836&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
Wed Jul 26 13:36:41 2006
@@ -791,3 +791,110 @@
 alter table t31constrainttest add foreign key (c311, c312) references mamta1.t11constrainttest;
 
 
+-- revoke of TRIGGERS and other privileges should drop dependent triggers
+set connection mamta1;
+drop table t11TriggerRevokeTest;
+create table t11TriggerRevokeTest (c111 int not null primary key);
+insert into t11TriggerRevokeTest values(1),(2);
+-- mamta2 is later going to create an insert trigger on t11TriggerRevokeTest 
+grant TRIGGER on t11TriggerRevokeTest to mamta2;
+set connection mamta2;
+drop table t21TriggerRevokeTest;
+create table t21TriggerRevokeTest (c211 int); 
+-- following will pass because mamta2 has trigger permission on mamta1.t11TriggerRevokeTest
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+-- no data in the table in which trigger is going to insert
+select * from t21TriggerRevokeTest;
+set connection mamta1;
+-- insert trigger will fire
+insert into t11TriggerRevokeTest values(3);
+set connection mamta2;
+-- trigger inserted one row into following table
+select * from t21TriggerRevokeTest;
+set connection mamta1;
+-- this revoke is going to drop dependent trigger
+revoke trigger on t11TriggerRevokeTest from mamta2;
+-- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(4);
+set connection mamta2;
+-- no more rows inserted since last check
+select * from t21TriggerRevokeTest;
+-- following attempt to create insert trigger again will fail because trigger privilege has
been revoked.
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+set connection mamta1;
+grant trigger on t11TriggerRevokeTest to mamta2;
+set connection mamta2;
+-- following attempt to create insert trigger again will pass because mamta2 has got the
necessary trigger privilege.
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+select * from t21TriggerRevokeTest;
+set connection mamta1;
+-- insert trigger should get fired
+insert into t11TriggerRevokeTest values(5);
+set connection mamta2;
+-- Should be one more row since last check because insert trigger is back in action
+select * from t21TriggerRevokeTest;
+drop table t21TriggerRevokeTest;
+set connection mamta1;
+-- this revoke is going to drop dependent trigger
+revoke trigger on t11TriggerRevokeTest from mamta2;
+-- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(6);
+-- cleanup
+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
+set connection mamta1;
+drop table t11TriggerRevokeTest;
+create table t11TriggerRevokeTest (c111 int not null primary key);
+insert into t11TriggerRevokeTest values(1),(2);
+grant SELECT on t11TriggerRevokeTest to mamta2;
+-- mamta2 is later going to create an insert trigger on t11TriggerRevokeTest 
+grant TRIGGER on t11TriggerRevokeTest to mamta2;
+set connection mamta2;
+drop table t21TriggerRevokeTest;
+create table t21TriggerRevokeTest (c211 int); 
+-- following will pass because mamta2 has trigger permission on mamta1.t11TriggerRevokeTest
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+-- no data in the table in which trigger is going to insert
+select * from t21TriggerRevokeTest;
+set connection mamta1;
+-- insert trigger will fire
+insert into t11TriggerRevokeTest values(3);
+set connection mamta2;
+-- trigger inserted one row into following table
+select * from t21TriggerRevokeTest;
+set connection mamta1;
+-- this revoke is going to drop dependent trigger on the table although dependent trigger
does not
+-- need this particular permission 
+-- WILL FIX THIS IN A SUBSEQUENT PATCH****************************************************************************************
+revoke SELECT on t11TriggerRevokeTest from mamta2;
+-- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(4);
+set connection mamta2;
+-- no more rows inserted since last check
+select * from t21TriggerRevokeTest;
+-- following attempt to create insert trigger again will pas because TRIGGER privilege was
never revoked.
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
+        insert into t21TriggerRevokeTest values(99);
+set connection mamta1;
+-- insert trigger should get fired
+insert into t11TriggerRevokeTest values(5);
+set connection mamta2;
+-- Should be one more row since last check because insert trigger is back in action
+select * from t21TriggerRevokeTest;
+drop table t21TriggerRevokeTest;
+set connection mamta1;
+-- this revoke is going to drop dependent trigger
+revoke trigger on t11TriggerRevokeTest from mamta2;
+-- following insert won't fire an insert trigger because one doesn't exist
+insert into t11TriggerRevokeTest values(6);
+-- cleanup
+drop table t11TriggerRevokeTest;
+



Mime
View raw message