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 ๐