Do not block DDL on RCAC-protected table if rule not affected by change

after alter add column RCAC rule must be dropped and recreated, even if new column is not part of rule

IBM perspective:

  In our ALTER TABLE ADD COLUMN documentation it says:

  https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html

  If a column is added to a table on which a mask or a permission is defined, or to a table that is referenced in the definition of a mask or a permission, that mask or permission is invalidated. Access to a table that activates column access control and a defined invalid mask on it is blocked until the invalid mask is either disabled, dropped, or re-created (SQLSTATE 560D0). Access to a table that activates row access control and a defined invalid row permission on it is blocked until the invalid permission is either disabled, dropped, or re-created (SQLSTATE 560D0).

  The reason we have this behaviour has to do with how Db2 resolves names, and how an add column can impact that. When an mask/permission refers to something like COL1_RCAC, Db2 needs to figure out what that object is. It could be a column, but also a global variable or function (and perhaps others). A column is at the highest order of precedence when resolving the name. This means that if COL1_RCAC previously referred to a global variable, the meaning of the mask/permission would change as we suddenly resolved to the column. So instead the mask/permission is marked invalid and the SECADM needs to investigate to make sure everything is as it should be.

Our perspective:

There is room for improvement. If the mask/permission does not depend on any object with the same name as the column being then this restrictions could be relaxed.

Use case:drop table howardg.st_contract

DB20000I The SQL command completed successfully.

create table howardg.st_contract like athena.st_contract

DB20000I The SQL command completed successfully.

ALTER TABLE howardg.st_contract DEACTIVATE COLUMN ACCESS CONTROL

DB20000I The SQL command completed successfully.

ALTER TABLE howardg.st_contract DEACTIVATE ROW ACCESS CONTROL

DB20000I The SQL command completed successfully.

DROP PERMISSION howardg.st_contract_PERM_RESTRICTION_NY_HP DB21034E

The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "HOWARDG.ST_CONTRACT_PERM_RESTRICTION_NY_HP" is an undefined name. SQLSTATE=42704

CREATE PERMISSION howardg.st_contract_PERM_RESTRICTION_NY_HP ON howardg.st_contract AS T FOR ROWS WHERE …….

DB20000I The SQL command completed successfully.

ALTER TABLE howardg.st_contract ACTIVATE ROW ACCESS CONTROL

DB20000I The SQL command completed successfully.

select 1 from howardg.st_contract with ur limit 1 1 ----------- 0 record(s) selected.

alter table howardg.st_contract add column col1_rcac VARCHAR(1)

DB20000I The SQL command completed successfully. select 1 from howardg.st_contract with ur limit 1 SQL5188N The statement failed because object "HOWARDG.ST_CONTRACT_PERM_RESTRICTION_NY_HP" of type "PERMISSION" is marked invalid. SQLSTATE=560D0