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

Data Engineering

Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.

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