Request to improve the RCAC masking rule behaviour in DB2 UDB

Hi,

We are working with Romeo Lupascu <romeol@ca.ibm.com> from IBM on this, please sync with him asap as we are looking for immediate fix to this limitation as this is impacting many client processes.

Per client:-
A more expected behavior is to have the masked value to be inserted into the session table rather than failing with an error.
If this sql is running by an app, the app code such as java/python can select the data into a file or memory and then insert back into a session table. Doing it inside a stored procedure directly by using ‘insert into select from' to be more efficient should be behave the same.

==

RCAC masking :-

The select works but if we try to do insert in session table from the masked column table then it gives an error.

CREATE MASK SCHEMA.TABNAME_TNL_ID ON SCHEMA.TABNAME T FOR COLUMN TNL_ID RETURN CASE WHEN ( (VERIFY_ROLE_FOR_USER(SESSION_USER, 'SCHEMA_WRITER') = 1 ) OR (VERIFY_ ROLE_FOR_USER(SESSION_USER, 'ROLEAAA_XXXSCHEMA') = 1 )OR (VERIFY_ROLE_FOR_USER (SESSION_USER,'ROLEREADCONFIDENTIAL_XXXSCHEMA') = 1 ) ) THEN TNL_ID WHEN ( ( VERIFY_ROLE_FOR_USER (SESSION_USER,'SCHEMA_READ_ALL_CLIENTS') = 1 ) OR ( ( ( VERIFY_ROLE_FOR_USER (SESSION_USER,'SCHEMA_READ_UNRESTRI CTED_CLIENTS_ONLY') = 1) OR ( VERIFY_ROLE_FOR_USER (SESSION_USER,'ROLEREAD_XXXSCHEMA') = 1 ) ) AND ( COALESCE(TNL_ID,'') NOT IN (8' )) ) ) THEN TNL_ID ELSE ‘MSCLIENT' END enable

When we run the below :
db2 "declare global temporary table session.TABNAME (tnl_id varchar(20)) with replace on commit preserve rows not logged"
db2 "insert into session.TABNAME select tnl_id from SCHEMA.v_TABNAME limit 10"

We get the below error :
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20478N The statement failed because the column mask "SCHEMA.TABNAME_TNL_ID"
defined for column "SCHEMA.TABNAME.TNL_ID" exists and the column mask cannot be
applied or the column mask conflicts with the failed statement. Reason code
"30" SQLSTATE=428HD

Explanation of the above error :
30

An INSERT or UPDATE operation uses a masked value returned from
the column mask "<mask-name>" for "<column-name>". The
expression specified in the THEN or ELSE clause of the column
mask definition that is used to return the masked value is not
a simple reference to the column "<column-name>". For the
specified INSERT or UPDATE operation, the return expression in
the column mask definition must be a simple reference to the
column for which the mask is defined.

The statement cannot be processed.

User response:

* Remove the reference to the column in the INSERT or UPDATE operation
and retry the operation.
* Contact the Security Administrator to have the return expression in
the column mask definition modified.

sqlcode: -20478


Note : A simple select works fine. If we replace ‘MSCLIENT' with TNL_ID (ie there is no manipulation) then insert also works fine .