cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Warehousing & Analytics
Engage in discussions on data warehousing, analytics, and BI solutions within the Databricks Community. Share insights, tips, and best practices for leveraging data for informed decision-making.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Row filtering based on condition not working

HeathDG1
New Contributor II
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,
1 REPLY 1

Isi
Contributor

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 ๐Ÿ™‚

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group