You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2006/07/30 01:13:00 UTC
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...
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)>