Interim DBSecurity deliver for December 1) Implement REVOKE ROLE RESTRICT. Previously dependent objects were not detected. Launchpad bug #1370739. 2) REVOKE ROLE with a list of grantees would fail for all grantees after the first. Now works for the entire list. Launchpad bug #1375494. 3) SHOWDDL ROLE now shows the GRANTED BY clause if the grantor is not DB__ROOT. Launchpad bug #1374586. 4) Component privilege names can now be reserved names. Launchpad bug 5) Added tests to catman1/test135 for privileges and RI constraints. 6) Added support for REVOKE RESTRICT for RI constraints. 7) Added support for USAGE privilege for sequence generator.
This code has been reviewed by the database security team but additional input is encouraged and welcomed.
Fix for initialize authorization failure and reenabled catman1 TEST133:
Initialize authorization creates a set of metadata tables and then loads data into the OBJECT_PRIVILEGES table to specify object ownership values It also loads data into the ROLE_USAGE table to specify role ownership values. Sometimes these insert..selects fail with an error 8102. This change does not fix the 8102 problem but fixes the code so the insert .. select succeeds. Bascially the insert was changed to an upsert and sanity checks placed around calls to make sure the correct number of rows were processed.
insertSelect code changes:
Added select count(*) from target table to make sure target table empty Changed insert to an upsert command After upsert added select count(*) on target table to get rows found Compare rows in target table with expected rows -> return an error if not equal.
This fixes the problem. The upsert ignores duplicate rows so we avoid the 8102 error. The inserted versus expected number of rows make sure the correct number of rows were processed.