db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r426847 [1/2] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/jdbc/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/s...
Date Sat, 29 Jul 2006 23:13:00 GMT
Author: bandaram
Date: Sat Jul 29 16:12:58 2006
New Revision: 426847

URL: http://svn.apache.org/viewvc?rev=426847&view=rev
Log:
DERBY-1543:This patch should address these two left over items in GRANT/REVOKE implementation. Some implementation notes:

1) Now Derby raises an SQLWarning when SQL authorization is ON without authentication at connect time. This is done by checking if AuthenticationService being used is an instance of NoneAuthenticationServiceImpl. Since this is the default authentication service with Derby, it should always be present.

2) Added code to drop permission descriptors from SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS when the object they provide permission for is dropped. This includes tables, views and routines and these descriptors needs to be removed from permissionCache as well.

I have tested the cases when PermissionsDescriptors are in cache also.

Submitted by Satheesh Bandaram (bandaram@gmail.com)

Modified:
    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/ViewDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnection.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/catalog/SYSCOLPERMSRowFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSROUTINEPERMSRowFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTABLEPERMSRowFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/syscat.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/syscat.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/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=426847&r1=426846&r2=426847&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 Sat Jul 29 16:12:58 2006
@@ -541,6 +541,31 @@
 	 */
 	public void	dropAllColumnDescriptors(UUID tableID, TransactionController tc)
 						throws StandardException;
+
+	/**
+	 * Drops all table and column permission descriptors for the given table.
+	 *
+	 * @param tableID	The UUID of the table for which to drop
+	 *			all the table and column permission descriptors
+	 * @param tc		TransactionController for the transaction
+	 *
+	 * @exception StandardException		Thrown on failure
+	 */
+	public void	dropAllTableAndColPermDescriptors(UUID tableID, TransactionController tc)
+						throws StandardException;
+
+	/**
+	 * Drops all routine permission descriptors for the given routine.
+	 *
+	 * @param routineID	The UUID of the routine for which to drop
+	 *			all the permission descriptors
+	 * @param tc		TransactionController for the transaction
+	 *
+	 * @exception StandardException		Thrown on failure
+	 */
+	public void	dropAllRoutinePermDescriptors(UUID routineID, TransactionController tc)
+						throws StandardException;
+
 	/**
 	 * Gets the viewDescriptor for the view with the given UUID.
 	 *

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=426847&r1=426846&r2=426847&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 Sat Jul 29 16:12:58 2006
@@ -390,6 +390,9 @@
 		/* Drop the view */
 		dd.dropViewDescriptor(this, tc);
 
+		/* Drop all table and column permission descriptors */
+		dd.dropAllTableAndColPermDescriptors(td.getUUID(), tc);
+
 		/* Drop the table */
 		dd.dropTableDescriptor(td, sd, tc);
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java Sat Jul 29 16:12:58 2006
@@ -58,6 +58,8 @@
 
 import java.util.Properties;
 
+import org.apache.derby.impl.jdbc.authentication.NoneAuthenticationServiceImpl;
+
 /**
  * Local implementation of Connection for a JDBC driver in 
  * the same process as the database.
@@ -123,6 +125,10 @@
 	private boolean	active;
 	boolean	autoCommit = true;
 	boolean	needCommit;
+
+	// Set to true if NONE authentication is being used
+	private boolean usingNoneAuth;
+
 	/*
      following is a new feature in JDBC3.0 where you can specify the holdability
      of a resultset at the end of the transaction. This gets set by the
@@ -269,6 +275,9 @@
 				throw tr.shutdownDatabaseException();
 			}
 
+			// Raise a warning in sqlAuthorization mode if authentication is not ON
+			if (usingNoneAuth && getLanguageConnection().usesSqlAuthorization())
+				addWarning(EmbedSQLWarning.newEmbedSQLWarning(SQLState.SQL_AUTHORIZATION_WITH_NO_AUTHENTICATION));
 		}
         catch (OutOfMemoryError noMemory)
 		{
@@ -438,6 +447,12 @@
 			throw newSQLException(SQLState.LOGIN_FAILED, MessageService.getTextMessage(MessageId.AUTH_INVALID));
 
 		}
+
+		// If authentication is not on, we have to raise a warning if sqlAuthorization is ON
+		// Since NoneAuthenticationService is the default for Derby, it should be ok to refer
+		// to its implementation here, since it will always be present.
+		if (authenticationService instanceof NoneAuthenticationServiceImpl)
+			usingNoneAuth = true;
 	}
 
     /**

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=426847&r1=426846&r2=426847&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 Sat Jul 29 16:12:58 2006
@@ -2393,6 +2393,81 @@
 	}
 
 	/**
+	 * Drops all table and column permission descriptors for the given table.
+	 *
+	 * @param tableID	The UUID of the table from which to drop
+	 *			all the permission descriptors
+	 * @param tc		TransactionController for the transaction
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void	dropAllTableAndColPermDescriptors(UUID tableID, TransactionController tc)
+		throws StandardException
+	{
+		DataValueDescriptor		tableIdOrderable;
+
+		/* Use tableIDOrderable in both start and stop position for scan. */
+		tableIdOrderable = getValueAsDVD(tableID);
+
+		/* Set up the start/stop position for the scan */
+		ExecIndexRow keyRow = exFactory.getIndexableRow(1);
+		keyRow.setColumn(1, tableIdOrderable);
+
+		dropTablePermDescriptor(tc, keyRow);
+		dropColumnPermDescriptor(tc, keyRow);
+	}
+
+	/**
+	 * Remove PermissionsDescriptor from permissions cache if present
+	 */
+	private void removePermEntryInCache(PermissionsDescriptor perm)
+		throws StandardException
+	{
+		// Remove cached permissions entry if present
+		Cacheable cacheEntry = getPermissionsCache().findCached( perm);
+		if (cacheEntry != null)
+			getPermissionsCache().remove(cacheEntry);
+	}
+
+	/**
+	 * Drops all routine permission descriptors for the given routine.
+	 *
+	 * @param routineID	The UUID of the routine from which to drop
+	 *			all the permission descriptors
+	 * @param tc		TransactionController for the transaction
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void	dropAllRoutinePermDescriptors(UUID routineID, TransactionController tc)
+		throws StandardException
+	{
+		TabInfo	ti = getNonCoreTI(SYSROUTINEPERMS_CATALOG_NUM);
+		SYSROUTINEPERMSRowFactory rf = (SYSROUTINEPERMSRowFactory) ti.getCatalogRowFactory();
+		DataValueDescriptor	routineIdOrderable;
+		ExecRow curRow;
+		PermissionsDescriptor perm;
+
+		/* Use tableIDOrderable in both start and stop position for scan. */
+		routineIdOrderable = getValueAsDVD(routineID);
+
+		/* Set up the start/stop position for the scan */
+		ExecIndexRow keyRow = exFactory.getIndexableRow(1);
+		keyRow.setColumn(1, routineIdOrderable);
+
+		while ((curRow=ti.getRow(tc, keyRow, rf.ALIASID_INDEX_NUM)) != null)
+		{
+			perm = (PermissionsDescriptor)rf.buildDescriptor(curRow, (TupleDescriptor) null, this);
+			removePermEntryInCache(perm);
+
+			// Build new key based on UUID and drop the entry as we want to drop
+			// only this row
+			ExecIndexRow uuidKey;
+			uuidKey = rf.buildIndexKeyRow(rf.ROUTINEPERMSID_INDEX_NUM, perm);
+			ti.deleteRow(tc, uuidKey, rf.ROUTINEPERMSID_INDEX_NUM);
+		}
+	}
+
+	/**
 	 * Delete the appropriate rows from syscolumns when
 	 * dropping 1 or more columns.
 	 * 
@@ -2409,6 +2484,70 @@
 		TabInfo				   ti = coreInfo[SYSCOLUMNS_CORE_NUM];
 
 		ti.deleteRow( tc, keyRow, SYSCOLUMNSRowFactory.SYSCOLUMNS_INDEX1_ID );
+	}
+
+	/**
+	 * Delete the appropriate rows from systableperms when
+	 * dropping a table
+	 * 
+	 * @param tc			The TransactionController
+	 * @param keyRow		Start/stop position.
+	 *
+	 * @exception StandardException		Thrown on failure
+	 */
+	private void dropTablePermDescriptor(
+					TransactionController tc,
+					ExecIndexRow keyRow)
+			throws StandardException
+	{
+		ExecRow curRow;
+		PermissionsDescriptor perm;
+		ExecIndexRow newKey;
+		TabInfo	ti = getNonCoreTI(SYSTABLEPERMS_CATALOG_NUM);
+		SYSTABLEPERMSRowFactory rf = (SYSTABLEPERMSRowFactory) ti.getCatalogRowFactory();
+
+		while ((curRow=ti.getRow(tc, keyRow, rf.TABLEID_INDEX_NUM)) != null)
+		{
+			perm = (PermissionsDescriptor)rf.buildDescriptor(curRow, (TupleDescriptor) null, this);
+			removePermEntryInCache(perm);
+
+			// Build key on UUID and drop the entry as we want to drop only this row
+			ExecIndexRow uuidKey;
+			uuidKey = rf.buildIndexKeyRow(rf.TABLEPERMSID_INDEX_NUM, perm);
+			ti.deleteRow(tc, uuidKey, rf.TABLEPERMSID_INDEX_NUM);
+		}
+	}
+
+	/**
+	 * Delete the appropriate rows from syscolperms when
+	 * dropping a table
+	 * 
+	 * @param tc			The TransactionController
+	 * @param keyRow		Start/stop position.
+	 *
+	 * @exception StandardException		Thrown on failure
+	 */
+	private void dropColumnPermDescriptor(
+					TransactionController tc,
+					ExecIndexRow keyRow)
+			throws StandardException
+	{
+		ExecRow curRow;
+		PermissionsDescriptor perm;
+		ExecIndexRow newKey;
+		TabInfo	ti = getNonCoreTI(SYSCOLPERMS_CATALOG_NUM);
+		SYSCOLPERMSRowFactory rf = (SYSCOLPERMSRowFactory) ti.getCatalogRowFactory();
+
+		while ((curRow=ti.getRow(tc, keyRow, rf.TABLEID_INDEX_NUM)) != null)
+		{
+			perm = (PermissionsDescriptor)rf.buildDescriptor(curRow, (TupleDescriptor) null, this);
+			removePermEntryInCache(perm);
+
+			// Build key on UUID and drop the entry as we want to drop only this row
+			ExecIndexRow uuidKey;
+			uuidKey = rf.buildIndexKeyRow(rf.COLPERMSID_INDEX_NUM, perm);
+			ti.deleteRow(tc, uuidKey, rf.COLPERMSID_INDEX_NUM);
+		}
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java Sat Jul 29 16:12:58 2006
@@ -69,17 +69,20 @@
 
     public static final int GRANTEE_TABLE_TYPE_GRANTOR_INDEX_NUM = 0;
     public static final int COLPERMSID_INDEX_NUM = 1;
+    public static final int TABLEID_INDEX_NUM = 2;
 	private static final int[][] indexColumnPositions = 
 	{ 
 		{ GRANTEE_COL_NUM, TABLEID_COL_NUM, TYPE_COL_NUM, GRANTOR_COL_NUM},
-		{ COLPERMSID_COL_NUM }
+		{ COLPERMSID_COL_NUM },
+		{ TABLEID_COL_NUM }
 	};
 	private static final String[][] indexColumnNames =
 	{
 		{"GRANTEE", "TABLEID", "TYPE", "GRANTOR"},
-		{"COLPERMSID"}
+		{"COLPERMSID"},
+		{"TABLEID"}
 	};
-    private static final boolean[] indexUniqueness = { true, true};
+    private static final boolean[] indexUniqueness = { true, true, false};
 
     private	static final String[] uuids =
     {
@@ -87,6 +90,7 @@
 		,"6074401f-0103-0e39-b8e7-00000010f010"	// heap UUID
 		,"787c0020-0103-0e39-b8e7-00000010f010"	// index1
 		,"c9a3808d-010c-42a2-ae15-0000000f67f8" //index2
+		,"80220011-010c-bc85-060d-000000109ab8" //index3
     };
 
     private SystemColumn[] columnList;
@@ -257,6 +261,9 @@
         case COLPERMSID_INDEX_NUM:
             row.setColumn(1, getDataValueFactory().getNullChar( (StringDataValue) null)); // COLPERMSID
             break;
+        case TABLEID_INDEX_NUM:
+            row.setColumn(1, getDataValueFactory().getNullChar( (StringDataValue) null)); // TABLEID
+            break;
         }
         return row;
     } // end of buildEmptyIndexRow
@@ -293,6 +300,12 @@
             row = getExecutionFactory().getIndexableRow( 1);
             String colPermsUUIDStr = perm.getObjectID().toString();
             row.setColumn(1, getDataValueFactory().getCharDataValue( colPermsUUIDStr));
+            break;
+        case TABLEID_INDEX_NUM:
+            row = getExecutionFactory().getIndexableRow( 1);
+            colPerms = (ColPermsDescriptor) perm;
+            tableUUIDStr = colPerms.getTableUUID().toString();
+            row.setColumn(1, getDataValueFactory().getCharDataValue( tableUUIDStr));
             break;
         }
         return row;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSROUTINEPERMSRowFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSROUTINEPERMSRowFactory.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSROUTINEPERMSRowFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSROUTINEPERMSRowFactory.java Sat Jul 29 16:12:58 2006
@@ -59,17 +59,21 @@
 
     static final int GRANTEE_ALIAS_GRANTOR_INDEX_NUM = 0;
     public static final int ROUTINEPERMSID_INDEX_NUM = 1;
+    public static final int ALIASID_INDEX_NUM = 2;
+
 	private static final int[][] indexColumnPositions = 
 	{ 
 		{ GRANTEE_COL_NUM, ALIASID_COL_NUM, GRANTOR_COL_NUM},
-		{ ROUTINEPERMSID_COL_NUM }
+		{ ROUTINEPERMSID_COL_NUM },
+		{ ALIASID_COL_NUM }
 	};
 	private static final String[][] indexColumnNames =
 	{
 		{"GRANTEE", "ALIASID", "GRANTOR"},
-		{"ROUTINEPERMSID"}
+		{"ROUTINEPERMSID"},
+		{"ALIASID"}
 	};
-    private static final boolean[] indexUniqueness = { true, true };
+    private static final boolean[] indexUniqueness = { true, true, false };
 
     private	static final String[] uuids =
     {
@@ -77,6 +81,7 @@
 		,"185e801c-0103-0e39-b8e7-00000010f010"	// heap UUID
 		,"c065801d-0103-0e39-b8e7-00000010f010"	// index1
 		,"40f70088-010c-4c2f-c8de-0000000f43a0" // index2
+		,"08264012-010c-bc85-060d-000000109ab8" // index3
     };
 
     private SystemColumn[] columnList;
@@ -225,6 +230,9 @@
         case ROUTINEPERMSID_INDEX_NUM:
             row.setColumn(1, getDataValueFactory().getNullChar( (StringDataValue) null)); // ROUTINEPERMSID
             break;
+        case ALIASID_INDEX_NUM:
+            row.setColumn(1, getDataValueFactory().getNullChar( (StringDataValue) null)); // ROUTINEPERMSID
+            break;
         }
         return row;
     } // end of buildEmptyIndexRow
@@ -261,6 +269,11 @@
             row = getExecutionFactory().getIndexableRow( 1);
             String routinePermsUUIDStr = perm.getObjectID().toString();
             row.setColumn(1, getDataValueFactory().getCharDataValue( routinePermsUUIDStr));
+            break;
+        case ALIASID_INDEX_NUM:
+            row = getExecutionFactory().getIndexableRow( 1);
+            routineUUIDStr = ((RoutinePermsDescriptor) perm).getRoutineUUID().toString();
+            row.setColumn(1, getDataValueFactory().getCharDataValue( routineUUIDStr));
             break;
         }
         return row;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTABLEPERMSRowFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTABLEPERMSRowFactory.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTABLEPERMSRowFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSTABLEPERMSRowFactory.java Sat Jul 29 16:12:58 2006
@@ -64,17 +64,20 @@
 
     public static final int GRANTEE_TABLE_GRANTOR_INDEX_NUM = 0;
     public static final int TABLEPERMSID_INDEX_NUM = 1;
+    public static final int TABLEID_INDEX_NUM = 2;
 	private static final int[][] indexColumnPositions = 
 	{ 
 		{ GRANTEE_COL_NUM, TABLEID_COL_NUM, GRANTOR_COL_NUM},
-		{ TABLEPERMSID_COL_NUM }
+		{ TABLEPERMSID_COL_NUM },
+		{ TABLEID_COL_NUM }
 	};
 	private static final String[][] indexColumnNames =
 	{
 		{"GRANTEE", "TABLEID", "GRANTOR"},
-		{"TABLEPERMSID"}
+		{"TABLEPERMSID"},
+		{"TABLEID"}
 	};
-    private static final boolean[] indexUniqueness = { true, true};
+    private static final boolean[] indexUniqueness = { true, true, false};
     
     private	static final String[] uuids =
     {
@@ -82,6 +85,7 @@
 		,"004b0019-0103-0e39-b8e7-00000010f010"	// heap UUID
 		,"c851401a-0103-0e39-b8e7-00000010f010"	// index1
 		,"80220011-010c-426e-c599-0000000f1120"	// index2
+		,"f81e0010-010c-bc85-060d-000000109ab8"	// index3
     };
 
     private SystemColumn[] columnList;
@@ -316,6 +320,9 @@
         case TABLEPERMSID_INDEX_NUM:
             row.setColumn(1, getDataValueFactory().getNullChar( (StringDataValue) null)); // TABLEPERMSID
             break;
+        case TABLEID_INDEX_NUM:
+            row.setColumn(1, getDataValueFactory().getNullChar( (StringDataValue) null)); // TABLEID
+            break;
         }
         return row;
     } // end of buildEmptyIndexRow
@@ -350,6 +357,11 @@
             row = getExecutionFactory().getIndexableRow( 1);
             String tablePermsUUIDStr = perm.getObjectID().toString();
             row.setColumn(1, getDataValueFactory().getCharDataValue( tablePermsUUIDStr));
+            break;
+        case TABLEID_INDEX_NUM:
+            row = getExecutionFactory().getIndexableRow( 1);
+            tableUUIDStr = ((TablePermsDescriptor) perm).getTableUUID().toString();
+            row.setColumn(1, getDataValueFactory().getCharDataValue( tableUUIDStr));
             break;
         }
         return row;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java Sat Jul 29 16:12:58 2006
@@ -21,6 +21,7 @@
 package	org.apache.derby.impl.sql.compile;
 
 import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.reference.SQLState;
 
 import org.apache.derby.impl.sql.execute.PrivilegeInfo;
 import org.apache.derby.impl.sql.execute.TablePrivilegeInfo;
@@ -81,6 +82,12 @@
 		{
 			if( columnLists[ action] != null)
 				columnBitSets[action] = columnLists[ action].bindResultColumnsByName( td, (DMLStatementNode) null);
+
+			// Prevent granting non-SELECT privileges to views
+			if (td.getTableType() == TableDescriptor.VIEW_TYPE && action != TablePrivilegeInfo.SELECT_ACTION)
+				if (actionAllowed[action])
+					throw StandardException.newException(SQLState.AUTH_GRANT_REVOKE_NOT_ALLOWED,
+									td.getQualifiedName());
 		}
 	}
 	

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java Sat Jul 29 16:12:58 2006
@@ -165,6 +165,8 @@
 				TableDescriptor.SYNONYM_TYPE, TableDescriptor.DEFAULT_LOCK_GRANULARITY);
 			dd.dropTableDescriptor(td, sd, tc);
 		}
+		else
+			dd.dropAllRoutinePermDescriptors(ad.getUUID(), tc);
 			
 		/* Drop the alias */
 		dd.dropAliasDescriptor(ad, tc);

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java Sat Jul 29 16:12:58 2006
@@ -215,6 +215,9 @@
 		/* Drop the columns */
 		dd.dropAllColumnDescriptors(tableId, tc);
 
+		/* Drop all table and column permission descriptors */
+		dd.dropAllTableAndColPermDescriptors(tableId, tc);
+
 		/* Drop the constraints */
 		dropAllConstraintDescriptors(td, activation);
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Sat Jul 29 16:12:58 2006
@@ -1128,7 +1128,7 @@
 2850C=User ''{0}'' is not the owner of {1} ''{2}''.''{3}''.
 2850D=User ''{0}'' can not perform the operation in schema ''{1}''.
 2850E=User ''{0}'' can not create schema ''{1}''. Only database owner could issue this statement.
-2850F=Grant or Revoke operation is not allowed on object ''{0}''.
+2850F=Specified grant or revoke operation is not allowed on object ''{0}''.
 04501.C=Database connection refused.
 
 
@@ -1338,6 +1338,7 @@
 01J11=Insensitive updatable result sets are not supported by server; remapping to insensitive read-only cursor
 01J12=Unable to obtain message text from server. See the next exception. The stored procedure SYSIBM.SQLCAMESSAGE is not installed on the server. Please contact your database administrator.
 01J13=Number of rows returned ({0}) is too large to fit in an integer; the value returned will be truncated.
+01J14=SQL authorization is being used without first enabling authentication.
 
 
 XJ001.U=Java exception: ''{1}: {0}''.

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Sat Jul 29 16:12:58 2006
@@ -1580,6 +1580,7 @@
     String INSENSITIVE_UPDATABLE_NOT_SUPPORTED = "01J11";
     String UNABLE_TO_OBTAIN_MESSAGE_TEXT_FROM_SERVER  = "01J12";
     String NUMBER_OF_ROWS_TOO_LARGE_FOR_INT = "01J13";
+	String SQL_AUTHORIZATION_WITH_NO_AUTHENTICATION = "01J14";
 
     String CURSOR_OPERATION_CONFLICT = "01001";
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out Sat Jul 29 16:12:58 2006
@@ -82,6 +82,7 @@
 SYSCHECKS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
+SYSCOLPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLUMNS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLUMNS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCONGLOMERATES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
@@ -100,6 +101,7 @@
 SYSREQUIREDPERM_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSROUTINEPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSROUTINEPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
+SYSROUTINEPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSSCHEMAS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSSCHEMAS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSSTATEMENTS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
@@ -107,6 +109,7 @@
 SYSSTATISTICS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLEPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLEPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
+SYSTABLEPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTRIGGERS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
@@ -283,6 +286,7 @@
 SYSCOLPERMS |0     
 SYSCOLPERMS |1     
 SYSCOLPERMS |1     
+SYSCOLPERMS |1     
 SYSCOLUMNS |0     
 SYSCOLUMNS |1     
 SYSCOLUMNS |1     
@@ -311,6 +315,7 @@
 SYSROUTINEPERMS |0     
 SYSROUTINEPERMS |1     
 SYSROUTINEPERMS |1     
+SYSROUTINEPERMS |1     
 SYSSCHEMAS |0     
 SYSSCHEMAS |1     
 SYSSCHEMAS |1     
@@ -322,6 +327,7 @@
 SYSTABLEPERMS |0     
 SYSTABLEPERMS |1     
 SYSTABLEPERMS |1     
+SYSTABLEPERMS |1     
 SYSTABLES |0     
 SYSTABLES |1     
 SYSTABLES |1     
@@ -506,6 +512,7 @@
 SYSCOLPERMS |0     
 SYSCOLPERMS |1     
 SYSCOLPERMS |1     
+SYSCOLPERMS |1     
 SYSCOLUMNS |0     
 SYSCOLUMNS |1     
 SYSCOLUMNS |1     
@@ -534,6 +541,7 @@
 SYSROUTINEPERMS |0     
 SYSROUTINEPERMS |1     
 SYSROUTINEPERMS |1     
+SYSROUTINEPERMS |1     
 SYSSCHEMAS |0     
 SYSSCHEMAS |1     
 SYSSCHEMAS |1     
@@ -543,6 +551,7 @@
 SYSSTATISTICS |0     
 SYSSTATISTICS |1     
 SYSTABLEPERMS |0     
+SYSTABLEPERMS |1     
 SYSTABLEPERMS |1     
 SYSTABLEPERMS |1     
 SYSTABLES |0     

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/syscat.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/syscat.out?rev=426847&r1=426846&r2=426847&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/syscat.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/syscat.out Sat Jul 29 16:12:58 2006
@@ -82,6 +82,7 @@
 SYSCHECKS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
+SYSCOLPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLUMNS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCOLUMNS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSCONGLOMERATES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
@@ -100,6 +101,7 @@
 SYSREQUIREDPERM_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSROUTINEPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSROUTINEPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
+SYSROUTINEPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSSCHEMAS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSSCHEMAS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSSTATEMENTS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
@@ -107,6 +109,7 @@
 SYSSTATISTICS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLEPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLEPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
+SYSTABLEPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTABLES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
 SYSTRIGGERS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }                                                                                                                                                                                                                                                                                                                                            
@@ -283,6 +286,7 @@
 SYSCOLPERMS |0     
 SYSCOLPERMS |1     
 SYSCOLPERMS |1     
+SYSCOLPERMS |1     
 SYSCOLUMNS |0     
 SYSCOLUMNS |1     
 SYSCOLUMNS |1     
@@ -311,6 +315,7 @@
 SYSROUTINEPERMS |0     
 SYSROUTINEPERMS |1     
 SYSROUTINEPERMS |1     
+SYSROUTINEPERMS |1     
 SYSSCHEMAS |0     
 SYSSCHEMAS |1     
 SYSSCHEMAS |1     
@@ -322,6 +327,7 @@
 SYSTABLEPERMS |0     
 SYSTABLEPERMS |1     
 SYSTABLEPERMS |1     
+SYSTABLEPERMS |1     
 SYSTABLES |0     
 SYSTABLES |1     
 SYSTABLES |1     
@@ -506,6 +512,7 @@
 SYSCOLPERMS |0     
 SYSCOLPERMS |1     
 SYSCOLPERMS |1     
+SYSCOLPERMS |1     
 SYSCOLUMNS |0     
 SYSCOLUMNS |1     
 SYSCOLUMNS |1     
@@ -534,6 +541,7 @@
 SYSROUTINEPERMS |0     
 SYSROUTINEPERMS |1     
 SYSROUTINEPERMS |1     
+SYSROUTINEPERMS |1     
 SYSSCHEMAS |0     
 SYSSCHEMAS |1     
 SYSSCHEMAS |1     
@@ -543,6 +551,7 @@
 SYSSTATISTICS |0     
 SYSSTATISTICS |1     
 SYSTABLEPERMS |0     
+SYSTABLEPERMS |1     
 SYSTABLEPERMS |1     
 SYSTABLEPERMS |1     
 SYSTABLES |0     

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=426847&r1=426846&r2=426847&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 Sat Jul 29 16:12:58 2006
@@ -1,4 +1,5 @@
 ij> connect 'grantRevokeDDL;create=true' user 'satheesh' as satConnection;
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij> -- Test table privileges
 create table satheesh.tsat(i int not null primary key, j int);
 0 rows inserted/updated/deleted
@@ -23,6 +24,7 @@
 xxxxFILTERED-UUIDxxxx|FOO                                                                                                                             |SATHEESH                                                                                                                        |xxxxFILTERED-UUIDxxxx|N|y|y|y|N|N
 2 rows selected
 ij> connect 'grantRevokeDDL' user 'bar' as barConnection;
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(BARCONNECTION)> -- Following revokes should fail. Only owner can revoke permissions
 revoke select on satheesh.tsat from public;
 ERROR: Failed with SQLSTATE 2850C
@@ -83,9 +85,9 @@
 ij(SATCONNECTION)> grant select on v1 to bar;
 0 rows inserted/updated/deleted
 ij(SATCONNECTION)> grant insert on v1 to foo;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 2850F
 ij(SATCONNECTION)> grant update on v1 to public;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 2850F
 ij(SATCONNECTION)> -- Tests for synonym. Not supported currently.
 create synonym mySym for satheesh.tsat;
 0 rows inserted/updated/deleted
@@ -124,6 +126,7 @@
 6 rows selected
 ij(SATCONNECTION)> -- Now connect as different user and try to do DDLs in schema owned by satheesh
 connect 'grantRevokeDDL;user=Swiper' as swiperConnection;
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(SWIPERCONNECTION)> create table swiperTab (i int, j int);
 0 rows inserted/updated/deleted
 ij(SWIPERCONNECTION)> insert into swiperTab values (1,1);
@@ -331,12 +334,14 @@
 ij(SWIPERCONNECTION)> create schema myschema authorization swiper;
 ERROR: Failed with SQLSTATE 2850E
 ij(SWIPERCONNECTION)> connect 'grantRevokeDDL;user=sam';
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(CONNECTION0)> create schema sam authorization swiper;
 ERROR: Failed with SQLSTATE 2850E
 ij(CONNECTION0)> -- Should pass
 create schema authorization sam;
 0 rows inserted/updated/deleted
 ij(CONNECTION0)> connect 'grantRevokeDDL;user=george';
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(CONNECTION1)> create schema george;
 0 rows inserted/updated/deleted
 ij(CONNECTION1)> -- Now try as DBA (satheesh)
@@ -379,6 +384,7 @@
 ERROR: Failed with SQLSTATE 2850E
 ij(SWIPERCONNECTION)> -- Implicit schema creation should only work if creating own schema
 connect 'grantRevokeDDL;user=monica' as monicaConnection;
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(MONICACONNECTION)> create table mywork.t1 ( i int);
 ERROR: Failed with SQLSTATE 2850E
 ij(MONICACONNECTION)> create table monica.shouldPass(c char(10));
@@ -431,6 +437,7 @@
 ERROR: Failed with SQLSTATE 2850F
 ij(SATCONNECTION)> -- Try positive tests
 connect 'grantRevokeDDL;user=sam' as samConnection;
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(SAMCONNECTION)> create table samTable(i int);
 0 rows inserted/updated/deleted
 ij(SAMCONNECTION)> insert into samTable values 1,2,3,4,5,6,7;
@@ -500,6 +507,7 @@
 -- update and insert and no-op rows into SYSTABLEPERMS for different users.
 connect 'grantRevokeDDL;create=true' user 'mamta1' as mamta1;
 WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(MAMTA1)> create table t11 (c111 int not null primary key);
 0 rows inserted/updated/deleted
 ij(MAMTA1)> insert into t11 values(1);
@@ -512,6 +520,7 @@
 0 rows inserted/updated/deleted
 ij(MAMTA1)> connect 'grantRevokeDDL;create=true' user 'mamta2' as mamta2;
 WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(MAMTA2)> select * from mamta1.t11;
 C111       
 -----------
@@ -527,6 +536,7 @@
 2 rows selected
 ij(MAMTA2)> connect 'grantRevokeDDL;create=true' user 'mamta3' as mamta3;
 WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(MAMTA3)> -- following select will fail because no permissions
 select * from mamta1.t11;
 ERROR: Failed with SQLSTATE 28508
@@ -534,6 +544,7 @@
 1 row inserted/updated/deleted
 ij(MAMTA3)> connect 'grantRevokeDDL;create=true' user 'mamta4' as mamta4;
 WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
 ij(MAMTA4)> -- following select will fail because no permissions
 select * from mamta1.t11;
 ERROR: Failed with SQLSTATE 28508
@@ -1441,4 +1452,131 @@
 ij(MAMTA1)> -- cleanup
 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.
+create table newTable(i int, j int, k int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant select, update(j) on newTable to sammy;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant references, delete on newTable to user1;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Try with a view
+create view myView as select * from newTable;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant select on myView to sammy;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> select * from sys.systableperms where grantee='SAMMY' or grantee='USER1';
+TABLEPERMSID                        |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|&|&|&|&|&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|y|N|N|N|N|N
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|y|N|N|N|N|N
+xxxxFILTERED-UUIDxxxx|USER1                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|N|y|N|N|y|N
+3 rows selected
+ij(MAMTA1)> select * from sys.syscolperms where grantee='SAMMY' or grantee='USER1';
+COLPERMSID                          |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|COLUMNS        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|u|{1}            
+1 row selected
+ij(MAMTA1)> drop view myView;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> select * from sys.systableperms where grantee='SAMMY' or grantee='USER1';
+TABLEPERMSID                        |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|&|&|&|&|&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|y|N|N|N|N|N
+xxxxFILTERED-UUIDxxxx|USER1                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|N|y|N|N|y|N
+2 rows selected
+ij(MAMTA1)> drop table newTable;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> select * from sys.systableperms where grantee='SAMMY' or grantee='USER1';
+TABLEPERMSID                        |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|&|&|&|&|&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+0 rows selected
+ij(MAMTA1)> select * from sys.syscolperms where grantee='SAMMY' or grantee='USER1';
+COLPERMSID                          |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|COLUMNS        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+0 rows selected
+ij(MAMTA1)> --- Try droping of a routine with permission descriptors. Should get dropped
+CREATE FUNCTION newFunction(P1 INT)
+        RETURNS INT 
+        RETURNS NULL ON NULL INPUT
+        EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectFromSpecificSchema'
+        LANGUAGE JAVA PARAMETER STYLE JAVA;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant execute on function newFunction to sammy;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant execute on function newFunction(INT) to user3;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> select * from sys.sysroutineperms where grantee='SAMMY' or grantee='USER3';
+ROUTINEPERMSID                      |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |ALIASID                             |&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|N
+xxxxFILTERED-UUIDxxxx|USER3                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|N
+2 rows selected
+ij(MAMTA1)> drop function newFunction;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> select * from sys.sysroutineperms where grantee='SAMMY' or grantee='USER3';
+ROUTINEPERMSID                      |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |ALIASID                             |&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+0 rows selected
+ij(MAMTA1)> -- Try the same tests after a permission descriptor is likely to have been cached
+create table newTable(i int, j int, k int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant select(i,j), delete on newTable to sammy;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> CREATE FUNCTION F_ABS(P1 INT)
+RETURNS INT NO SQL
+RETURNS NULL ON NULL INPUT
+EXTERNAL NAME 'java.lang.Math.abs'
+LANGUAGE JAVA PARAMETER STYLE JAVA;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant execute on function f_abs to sammy;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> select * from sys.sysroutineperms where grantee='SAMMY';
+ROUTINEPERMSID                      |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |ALIASID                             |&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|N
+1 row selected
+ij(MAMTA1)> select * from sys.syscolperms where grantee='SAMMY';
+COLPERMSID                          |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|COLUMNS        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|s|{0, 1}         
+1 row selected
+ij(MAMTA1)> select * from sys.systableperms where grantee='SAMMY';
+TABLEPERMSID                        |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|&|&|&|&|&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SAMMY                                                                                                                           |MAMTA1                                                                                                                          |xxxxFILTERED-UUIDxxxx|N|y|N|N|N|N
+1 row selected
+ij(MAMTA1)> -- Now connect as sammy and access database objects. That should create
+-- PermissionsDescriptors and cache them
+connect 'grantRevokeDDL' user 'sammy' as sammyConnection;
+WARNING 01J14: SQL authorization is being used without first enabling authentication.
+ij(SAMMYCONNECTION)> set schema mamta1;
+0 rows inserted/updated/deleted
+ij(SAMMYCONNECTION)> select i,j from newTable;
+I          |J          
+-----------------------
+0 rows selected
+ij(SAMMYCONNECTION)> values f_abs(-5);
+1          
+-----------
+5          
+1 row selected
+ij(SAMMYCONNECTION)> set connection mamta1;
+ij(MAMTA1)> drop table newTable;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop function f_abs;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Confirm rows in catalogs are gone
+select * from sys.sysroutineperms where grantee='SAMMY';
+ROUTINEPERMSID                      |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |ALIASID                             |&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+0 rows selected
+ij(MAMTA1)> select * from sys.syscolperms where grantee='SAMMY';
+COLPERMSID                          |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|COLUMNS        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+0 rows selected
+ij(MAMTA1)> select * from sys.systableperms where grantee='SAMMY';
+TABLEPERMSID                        |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|&|&|&|&|&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+0 rows selected
 ij(MAMTA1)> 



Mime
View raw message