Row filtering based on condition not working

[HeathDG1]

Hi-

We have a delta table in our unity catalog called dream_team.stern_portfolio.location_info.

We are trying to use row level security to filter our data based on a users group membership. This way when users look at out dashboard they can only see the data they are allowed to me (as in only MA data) 

HOWEVER, the row filter is being applied to ALL users, not just the users in the group Stern MA.

PLEASE HELP US.

Below is the SQL code we have used to create and apply the function.

CREATE OR REPLACE FUNCTION dream_team.stern_portfolio.state_filter(state STRING) RETURNS BOOLEAN RETURN

IF(IS_ACCOUNT_GROUP_MEMBER('Stern MA'), state = 'MA', true) -- Apply the row filter to the table ALTER TABLE dream_team.stern_portfolio.location_info SET ROW FILTER dream_team.stern_portfolio.state_filter ON (state);

Thank you,

[Isi]

Hey @HeathDG1 

I think your function isn’t behaving the way you expect because of how the logic is set up:

•If the user is in Stern MA, they only see rows where state = 'MA'  (which is good).
•BUT for everyone else, the function returns true, meaning they see all the data

To properly restrict access for users outside Stern MA, you need to return false by default instead of true:

CREATE OR REPLACE FUNCTION dream_team.stern_portfolio.state_filter(state STRING) 
RETURNS BOOLEAN 
RETURN
IF(IS_ACCOUNT_GROUP_MEMBER('Stern MA'), state = 'MA', false);

• If the user is in Stern MA → They get state = 'MA' (so they only see data for MA).

•If the user is NOT in Stern MA → The function returns false, meaning they see nothing at all.

Once the function is corrected, apply the row filter to your table

To various groups:

CREATE OR REPLACE FUNCTION dream_team.stern_portfolio.state_filter(state STRING) 
RETURNS BOOLEAN 
RETURN
CASE 
    WHEN IS_ACCOUNT_GROUP_MEMBER('Stern MA') THEN state = 'MA'
    WHEN IS_ACCOUNT_GROUP_MEMBER('Stern NY') THEN state = 'NY'
    WHEN IS_ACCOUNT_GROUP_MEMBER('Stern CA') THEN state = 'CA'
    ELSE false
END;

Hope this helps 🙂