topic Re: Row Filter on Unity Catalog Tables based on Unity Catalog group appartenance in Data Engineering

Row Filter on Unity Catalog Tables based on Unity Catalog group appartenance

Antoine_B — Wed, 31 Jul 2024 13:12:49 GMT

Hello,

I would like to prevent users belonging to a given Unity Catalog group ('restricted_users_group') to access some rows of a Unity Catalog Table.

For now, I was able to define a Row Filter function to prevent a list of users to access some rows, thanks to this documentation.
Here is my current function:

-- apply Row Filter only for user restricted@users.com. Filter is disabled for other users

CREATE FUNCTION rd.my_schema.my_row_filter(filter_column INTEGER) RETURNS BOOLEAN

RETURN IF(CURRENT_USER() = 'restricted@users.com', filter_column IN (15, 16, 17), true);

Here is how I apply this Row Filter function to two of my sensitive tables:

ALTER TABLE rd.my_schema.my_table_1 SET ROW FILTER rd.my_schema.my_row_filter ON (id_col);
ALTER TABLE rd.my_schema.my_table_2 SET ROW FILTER rd.my_schema.my_row_filter ON (id_col);

But I would like some help to adapt this function to work with Unity Catalog groups instead of users.
Because I would like to avoid editing my Row Filter function each time a new user is added to this group ('restricted_users_group').

Thanks 🙂

Re: Row Filter on Unity Catalog Tables based on Unity Catalog group appartenance

Antoine_B — Wed, 31 Jul 2024 14:23:17 GMT

I saw the tricks of mapping tables: https://docs.databricks.com/en/tables/row-and-column-filters.html#mapping-table-examples

This means I have to create a Job to keep my mapping table up to date with users in the Unity Catalog group.
I keep this solution in mind, but I wonder if something more integrated in Row Filters functions exists, without the need of a mapping table ?

Re: Row Filter on Unity Catalog Tables based on Unity Catalog group appartenance

Antoine_B — Thu, 01 Aug 2024 12:15:44 GMT

Ok, so this problem needs no tricks. All was in the documentation
I did not know about the function IS_ACCOUNT_GROUP_MEMBER().

So this Row Filter function did the job:

CREATE FUNCTION rd.my_schema.my_row_filter(filter_column INTEGER) RETURNS BOOLEAN

RETURN IF(IS_ACCOUNT_GROUP_MEMBER('restricted_users_group'), filter_column IN (15, 16, 17), true);