Row-level security based on IS_MEMBER function

Question

Objective

I'm implementing row-level security (RLS) at Azure Synapse based on Active Directory (AD) authentication. The idea is that customers added to security_group_A_access have access to all MY_TABLE rows with column CATEGORY = 'A'. See code below.

Issues

1. IS_MEMBER function is invoked once per every row, making SELECT DISTINCT CATEGORY FROM MY_TABLE query run more than 3 minutes instead of 1 second. Looks to be a common issue.
2. Even by hardcoding fn_predicate's condition with WHERE CATEGORY = 'A' (remove IS_MEMBER invocation) querying becomes slower. The RLS tables have up to billion of rows and are used for Power BI reports, so performance/response time is very critical for them.

Questions

1. Is there any way to call IS_MEMBER function no more times than there are security groups? Means somehow cache the result or store it to global variable, etc.
2. Is RLS a common approach for billion-rows table with critical performance? Or it's a common practice to create a view per each AD security group?

Code

CREATE SCHEMA [security]
GO

CREATE FUNCTION [security].[fn_predicate](@category_column as varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN SELECT 1 AS fn_predicate
  WHERE IS_MEMBER(CONCAT('security_group_', @category_column, '_access')) = 1
GO

GRANT SELECT ON [security].[fn_predicate] TO security_group_A_access
GRANT SELECT ON [security].[fn_predicate] TO security_group_B_access
GO

CREATE SECURITY POLICY MY_TABLE_FILTER
ADD FILTER PREDICATE [security].[fn_predicate]([CATEGORY])
ON [dbo].[MY_TABLE]
WITH (STATE = ON)
GO

P.S.

I may rephrase this question to - are there any better options than the 1st one?

Three possible approaches:

1. Create view per CATEGORY, and restrict each group to separate view.

   • Pros: The performance of MY_TABLE for PBI reporting isn't impacted
   • Cons: Number of views. Suppose you have categories A, B, C, D, E. Than you need 5 views. What if there is a user that consists in groups A and C?

2. Apply RLS on top of MY_TABLE.

   • Pros: My current approach. Simple to implement, no additional tables and views. Scalable, in case of more CATEGORIES, groups or rules will be added.
   • Cons: See "Issues" section. Performance of MY_TABLE is hardly impacted and isn't acceptable

3. Hybrid approach. Create single MY_TABLE_VIEW and apply RLS on top of the view.

   • Pros: all benefits of the 2nd option, plus MY_TABLE performance for PBI reporting isn't impacted.
   • Cons: still need to solve IS_MEMBER performance issue, see 1st point from "Issues" section.

Answer 1

I know this is a fairly old post but I just wanted to share my solution. I followed Pattern #2 from Microsoft's suggested patterns.

https://learn.microsoft.com/en-us/archive/blogs/sqlsecurity/row-level-security-performance-and-common-patterns#pattern-2-row-assignments-in-a-lookup-table

This works using a lookup table. To do this I created a OwnerAssignments table that contains the name of each AD group that is used in the application. Then I also have an Owner column on my table that I'm applying RLS to.

My predicate function looks like this

CREATE FUNCTION Security.tvf_securitypredicateOwner(@Owner AS VARCHAR(256))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result FROM [Security].OwnerAssignments
        WHERE IS_MEMBER(Name) = 1
        AND Name = @Owner

Lastly I apply the policy like this

CREATE SECURITY POLICY MyTableFilter
ADD FILTER PREDICATE Security.tvf_securitypredicateOwner([Owner])
ON dbo.MyTable
WITH (STATE = ON);
GO

This greatly improved performance. It brought a query that took close to a minute to run down to a fraction of a second.