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 mi...@apache.org on 2006/10/05 22:15:42 UTC
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...
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;