db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r453747 - in /db/derby/code/branches/10.2/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/o...
Date Fri, 06 Oct 2006 19:54:24 GMT
Author: mikem
Date: Fri Oct  6 12:54:23 2006
New Revision: 453747

URL: http://svn.apache.org/viewvc?view=rev&rev=453747
Log:
DERBY-1847
contributed by Mamta Satoor
backporting via merge change 453352 from trunk to 10.2 branch.

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/branches/10.2/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?view=diff&rev=453747&r1=453746&r2=453747
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
Fri Oct  6 12:54:23 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/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?view=diff&rev=453747&r1=453746&r2=453747
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Fri Oct  6 12:54:23 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/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java?view=diff&rev=453747&r1=453746&r2=453747
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java
Fri Oct  6 12:54:23 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/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?view=diff&rev=453747&r1=453746&r2=453747
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Fri Oct  6 12:54:23 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/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?view=diff&rev=453747&r1=453746&r2=453747
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
Fri Oct  6 12:54:23 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/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?view=diff&rev=453747&r1=453746&r2=453747
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
Fri Oct  6 12:54:23 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