Index: java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java =================================================================== --- java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (revision 452578) +++ java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (working copy) @@ -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); } /** Index: java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java =================================================================== --- java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java (revision 452578) +++ java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java (working copy) @@ -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}, Index: java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java =================================================================== --- java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (revision 452578) +++ java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (working copy) @@ -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 Index: java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java =================================================================== --- java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (revision 452578) +++ java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (working copy) @@ -539,7 +539,27 @@ 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. * * @param routineID The UUID of the routine for which to drop Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (revision 452578) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (working copy) @@ -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; Index: java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out (revision 452578) +++ java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out (working copy) @@ -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)>