Integrating row and column level security in parent child tables with masking only selected rows

vishal48 — Fri, 02 Aug 2024 09:29:52 GMT

Currently I am working with a project where we need to mask PIIs in few columns for VIP customers only.

Let me explain briefly with example:

Table A: [personid, status, address, UID, VIPFLAG] --> Mask "UID" and "address" only where VIPFLAG is 1

Table B: [personID, phonenumber, countrycode] --> mask PhoneNumber only for PersonIDs where value is 1, in Table A

I tried with masking functions like:

Table A mask:

CREATE FUNCTION vip_filter(VIPFLAG STRING)

RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admins'), True, VIPFLAG !='V');

Table B mask:

CREATE FUNCTION vip_filter_child (PERSONID STRING)

RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admins'), TRUE, PERSONID not in (select distinct PERSONID from Table A where VIPFLAG = 'V')

);

I can apply the filters but when querying i receive this error:

[UNSUPPORTED_NESTED_ROW_OR_COLUMN_ACCESS_POLICY] Table A has a row level security policy or column mask which indirectly refers to another table with a row level security policy or column mask; this is not supported. Call sequence: (Table scan: Table B, Table scan: Table A, Row filter: "vip_filter") SQLSTATE: 0A000

topic Integrating row and column level security in parent child tables with masking only selected rows in Data Engineering

Integrating row and column level security in parent child tables with masking only selected rows