RCAC rule needs to be dropped and recreaed after adding new column to a table, even if that column is not part of the rule.

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.

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 ( ( VERIFY_ROLE_FOR_USER 　............) ENFORCED FOR ALL ACCESS ENABLE

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