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

Antoine_B
Contributor

‎07-31-2024 06:12 AM

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 🙂
0 Kudos

Antoine_B
Contributor

‎07-31-2024 07:23 AM

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 ?

0 Kudos

Antoine_B
Contributor

‎08-01-2024 05:15 AM

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);



View solution in original post

0 Kudos