db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r453352 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apach...
Date Thu, 05 Oct 2006 20:15:42 GMT
Author: mikem
Date: Thu Oct  5 13:15:41 2006
New Revision: 453352

URL: http://svn.apache.org/viewvc?view=rev&rev=453352
Log:
DERBY-1847
contributed by Mamta Satoor
patch: DERBY1846_V1_diff_AddColumnAndGrantRevoke.txt

 To recap the problem, in SQL Authorization mode, when a new column is added to a table, the
rows in SYSCOLPERMS for the table in question were not getting updated to incorporate the
new column. This caused ASSERT failure when a non-table owner attempted to select the new
column. 

 Some background information on system table involved: SYSCOLPERMS keeps track of column level
privileges on a given table. One of the columns in SYSCOLPERMS is "COLUMNS" and it has a bit
map to show which columns have the given permission granted on them. When a new column is
added to the user table, the "COLUMNS" need to be expanded by one bit and that bit should
be initialized to zero since no privileges have been granted on that column at the ALTER TABLE...ADD
COLUMN time. 

 I have fixed this problem by having AlterTableConstantAction.addNewColumnToTable call the
new method in DataDictionary called updateSYSCOLPERMSforAddColumnToUserTable. At this point,
we know of only the TableDescriptor's uuid which can help us determine all the rows in SYSCOLPERMS
for that given table uuid. I get ColPermsDescriptor for each one of those rows and then use
the ColPermsDescriptor's uuid to update the "COLUMNS" column so SYSCOLPERMS is aware of the
newly added column in user table. This fixes the problem because at the time of SELECT, when
we do privilege lookup in SYSCOLPERMS, we have info on the newly added column. 


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.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/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?view=diff&rev=453352&r1=453351&r2=453352
==============================================================================
--- 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
Thu Oct  5 13:15:41 2006
@@ -539,6 +539,26 @@
 	public void	dropAllTableAndColPermDescriptors(UUID tableID, TransactionController tc)
 						throws StandardException;
 
+
+	/**
+	 * Need to update SYSCOLPERMS for a given table because a new column has 
+	 * been added to that table. SYSCOLPERMS has a column called "COLUMNS"
+	 * which is a bit map for all the columns in a given user table. Since
+	 * ALTER TABLE .. ADD COLUMN .. has added one more column, we need to
+	 * expand "COLUMNS" for that new column
+	 *
+	 * Currently, this code gets called during execution phase of
+	 * ALTER TABLE .. ADD COLUMN .. 
+	 *
+	 * @param tableID	The UUID of the table to which a column has been added
+	 * @param tc		TransactionController for the transaction
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void	updateSYSCOLPERMSforAddColumnToUserTable(UUID tableID, TransactionController
tc)
+	throws StandardException;
+	
+	
 	/**
 	 * Drops all routine permission descriptors for the given routine.
 	 *

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?view=diff&rev=453352&r1=453351&r2=453352
==============================================================================
--- 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
Thu Oct  5 13:15:41 2006
@@ -2440,6 +2440,84 @@
 	}
 
 	/**
+	 * Need to update SYSCOLPERMS for a given table because a new column has 
+	 * been added to that table. SYSCOLPERMS has a column called "COLUMNS"
+	 * which is a bit map for all the columns in a given user table. Since
+	 * ALTER TABLE .. ADD COLUMN .. has added one more column, we need to
+	 * expand "COLUMNS" for that new column
+	 *
+	 * Currently, this code gets called during execution phase of
+	 * ALTER TABLE .. ADD COLUMN .. 
+	 *
+	 * @param tableID	The UUID of the table to which a column has been added
+	 * @param tc		TransactionController for the transaction
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void	updateSYSCOLPERMSforAddColumnToUserTable(UUID tableID, TransactionController
tc)
+	throws StandardException
+	{
+		// In Derby authorization mode, permission catalogs may not be present
+		if (!usesSqlAuthorization)
+			return;
+
+		/* This method has 2 steps to it. First get all the ColPermsDescriptor   
+		for given tableid. And next step is to go back to SYSCOLPERMS to find
+		unique row corresponding to each of ColPermsDescriptor and update the
+		"COLUMNS" column in SYSCOLPERMS. The reason for this 2 step process is
+		that SYSCOLPERMS has a non-unique row on "TABLEID" column and hence   
+		we can't get a unique handle on each of the affected row in SYSCOLPERMS
+		using just the "TABLEID" column */
+
+		// First get all the ColPermsDescriptor for the given tableid from   
+		//SYSCOLPERMS using getDescriptorViaIndex(). 
+		List permissionDescriptorsList;//all ColPermsDescriptor for given tableid
+		DataValueDescriptor		tableIDOrderable = getValueAsDVD(tableID);
+		TabInfoImpl	ti = getNonCoreTI(SYSCOLPERMS_CATALOG_NUM);
+		SYSCOLPERMSRowFactory rf = (SYSCOLPERMSRowFactory) ti.getCatalogRowFactory();
+		ExecIndexRow keyRow = exFactory.getIndexableRow(1);
+		keyRow.setColumn(1, tableIDOrderable);
+		permissionDescriptorsList = newSList();
+		getDescriptorViaIndex(
+			SYSCOLPERMSRowFactory.TABLEID_INDEX_NUM,
+			keyRow,
+			(ScanQualifier [][]) null,
+			ti,
+			(TupleDescriptor) null,
+			permissionDescriptorsList,
+			false);
+
+		/* Next, using each of the ColPermDescriptor's uuid, get the unique row 
+		in SYSCOLPERMS and expand the "COLUMNS" column in SYSCOLPERMS to 
+		accomodate the newly added column to the tableid*/
+		ColPermsDescriptor colPermsDescriptor;
+		ExecRow curRow;
+		ExecIndexRow uuidKey;
+		// Not updating any indexes on SYSCOLPERMS
+		boolean[] bArray = new boolean[SYSCOLPERMSRowFactory.TOTAL_NUM_OF_INDEXES];
+		int[] colsToUpdate = {SYSCOLPERMSRowFactory.COLUMNS_COL_NUM};
+		for (Iterator iterator = permissionDescriptorsList.iterator(); iterator.hasNext(); )
+		{
+			colPermsDescriptor = (ColPermsDescriptor) iterator.next();
+			removePermEntryInCache(colPermsDescriptor);
+			uuidKey = rf.buildIndexKeyRow(rf.COLPERMSID_INDEX_NUM, colPermsDescriptor);
+			curRow=ti.getRow(tc, uuidKey, rf.COLPERMSID_INDEX_NUM);
+	        FormatableBitSet columns = (FormatableBitSet) curRow.getColumn( 
+					  SYSCOLPERMSRowFactory.COLUMNS_COL_NUM).getObject();
+	        int currentLength = columns.getLength();
+	        columns.grow(currentLength+1);
+	        curRow.setColumn(SYSCOLPERMSRowFactory.COLUMNS_COL_NUM,
+					  dvf.getDataValue((Object) columns));
+			ti.updateRow(keyRow, curRow,
+					SYSCOLPERMSRowFactory.TABLEID_INDEX_NUM,
+					 bArray, 
+					 colsToUpdate,
+					 tc);
+		}
+	}
+
+	
+	/**
 	 * Remove PermissionsDescriptor from permissions cache if present
 	 */
 	private void removePermEntryInCache(PermissionsDescriptor perm)
@@ -2528,7 +2606,6 @@
 	{
 		ExecRow curRow;
 		PermissionsDescriptor perm;
-		ExecIndexRow newKey;
 		TabInfoImpl	ti = getNonCoreTI(SYSTABLEPERMS_CATALOG_NUM);
 		SYSTABLEPERMSRowFactory rf = (SYSTABLEPERMSRowFactory) ti.getCatalogRowFactory();
 
@@ -2560,7 +2637,6 @@
 	{
 		ExecRow curRow;
 		PermissionsDescriptor perm;
-		ExecIndexRow newKey;
 		TabInfoImpl	ti = getNonCoreTI(SYSCOLPERMS_CATALOG_NUM);
 		SYSCOLPERMSRowFactory rf = (SYSCOLPERMSRowFactory) ti.getCatalogRowFactory();
 
@@ -10223,9 +10299,7 @@
         // Remove cached permissions data. The cache may hold permissions data for this key
even if
         // the row in the permissions table is new. In that case the cache may have an entry
indicating no
         // permissions
-        Cacheable cacheEntry = getPermissionsCache().findCached( perm);
-        if( cacheEntry != null)
-            getPermissionsCache().remove( cacheEntry);
+		removePermEntryInCache(perm);
 
         //If we are dealing with grant, then the caller does not need to send 
         //any invalidation actions to anyone and hence return false

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?view=diff&rev=453352&r1=453351&r2=453352
==============================================================================
--- 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
Thu Oct  5 13:15:41 2006
@@ -64,12 +64,13 @@
     private static final int GRANTOR_COL_NUM = 3;
     private static final int TABLEID_COL_NUM = 4;
     private static final int TYPE_COL_NUM = 5;
-    private static final int COLUMNS_COL_NUM = 6;
+    protected static final int COLUMNS_COL_NUM = 6;
     private static final int COLUMN_COUNT = 6;
 
     static final int GRANTEE_TABLE_TYPE_GRANTOR_INDEX_NUM = 0;
     static final int COLPERMSID_INDEX_NUM = 1;
     static final int TABLEID_INDEX_NUM = 2;
+    protected static final int TOTAL_NUM_OF_INDEXES = 3;
 	private static final int[][] indexColumnPositions = 
 	{ 
 		{ GRANTEE_COL_NUM, TABLEID_COL_NUM, TYPE_COL_NUM, GRANTOR_COL_NUM},

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?view=diff&rev=453352&r1=453351&r2=453352
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Thu Oct  5 13:15:41 2006
@@ -648,6 +648,16 @@
 								columnInfo[ix].defaultInfo.getDefaultText(),
 								lcc);
 		}	
+
+		// Update SYSCOLPERMS table which tracks the permissions granted
+		// at columns level. The sytem table has a bit map of all the columns
+		// in the user table to help determine which columns have the 
+		// permission granted on them. Since we are adding a new column,
+		// that bit map needs to be expanded and initialize the bit for it
+		// to 0 since at the time of ADD COLUMN, no permissions have been
+		// granted on that new column.
+		//
+		dd.updateSYSCOLPERMSforAddColumnToUserTable(td.getUUID(), tc);
 	}
 
 	/**

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?view=diff&rev=453352&r1=453351&r2=453352
==============================================================================
--- 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
Thu Oct  5 13:15:41 2006
@@ -3077,4 +3077,49 @@
 ij(MAMTA1)> set connection mamta3;
 ij(MAMTA3)> create table d1589t31ConstraintTest (c311 int, c312 int, foreign key(c311,
c312) references mamta1.d1589t11ConstraintTest);
 0 rows inserted/updated/deleted
+ij(MAMTA3)> -- DERBY-1847 SELECT statement asserts with XJ001 when attempted to select
a newly added column
+-- Grant access on 2 columns and then add another column to the table. The select on the
new column
+-- by another user should complain about no permissions granted on that new column.
+set connection mamta2;
+ij(MAMTA2)> create table t1Derby1847 (c1 int, c2 int);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> grant select(c1,c2) on t1Derby1847 to mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> alter table t1Derby1847 add c3 int;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- should fail because mamta3 doesn't have any permission on this column in
table mamta2.t1Derby1847
+select c3 from mamta2.t1Derby1847;
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA3)> set connection mamta2;
+ij(MAMTA2)> grant select on t1Derby1847 to mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- should work now because mamta3 got select permission on new column in table
mamta2.t1Derby1847 through table level select permission
+select c3 from mamta2.t1Derby1847;
+C3         
+-----------
+0 rows selected
+ij(MAMTA3)> set connection mamta2;
+ij(MAMTA2)> revoke select on t1Derby1847 from mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- should fail because mamta3 lost it's select permission on new column in
table mamta2.t1Derby1847
+select c3 from mamta2.t1Derby1847;
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA3)> set connection mamta2;
+ij(MAMTA2)> grant select(c3) on t1Derby1847 to mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- should work now because mamta3 got select permission on new column in table
mamta2.t1Derby1847 through column level select permission
+select c3 from mamta2.t1Derby1847;
+C3         
+-----------
+0 rows selected
+ij(MAMTA3)> set connection mamta2;
+ij(MAMTA2)> drop table t1Derby1847;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> select c3 from mamta2.t1Derby1847;
+ERROR: Failed with SQLSTATE 42X05
 ij(MAMTA3)> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?view=diff&rev=453352&r1=453351&r2=453352
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
Thu Oct  5 13:15:41 2006
@@ -1948,3 +1948,33 @@
 grant references(c112) on d1589t11ConstraintTest to PUBLIC;
 set connection mamta3;
 create table d1589t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references
mamta1.d1589t11ConstraintTest); 
+
+-- DERBY-1847 SELECT statement asserts with XJ001 when attempted to select a newly added
column
+-- Grant access on 2 columns and then add another column to the table. The select on the
new column
+-- by another user should complain about no permissions granted on that new column.
+set connection mamta2;
+create table t1Derby1847 (c1 int, c2 int); 
+grant select(c1,c2) on t1Derby1847 to mamta3; 
+alter table t1Derby1847 add c3 int; 
+set connection mamta3;
+-- should fail because mamta3 doesn't have any permission on this column in table mamta2.t1Derby1847
+select c3 from mamta2.t1Derby1847; 
+set connection mamta2;
+grant select on t1Derby1847 to mamta3; 
+set connection mamta3;
+-- should work now because mamta3 got select permission on new column in table mamta2.t1Derby1847
through table level select permission
+select c3 from mamta2.t1Derby1847; 
+set connection mamta2;
+revoke select on t1Derby1847 from mamta3; 
+set connection mamta3;
+-- should fail because mamta3 lost it's select permission on new column in table mamta2.t1Derby1847
+select c3 from mamta2.t1Derby1847; 
+set connection mamta2;
+grant select(c3) on t1Derby1847 to mamta3; 
+set connection mamta3;
+-- should work now because mamta3 got select permission on new column in table mamta2.t1Derby1847
through column level select permission
+select c3 from mamta2.t1Derby1847; 
+set connection mamta2;
+drop table t1Derby1847; 
+set connection mamta3;
+select c3 from mamta2.t1Derby1847; 



Mime
View raw message