title: Row-level security based on IS_MEMBER function
body: <h2>Objective</h2>
  
  <p>I'm implementing row-level security (RLS) at Azure Synapse based on Active Directory (AD) authentication. The idea is that customers added to <code>security_group_A_access</code> have access to all MY_TABLE rows with column <code>CATEGORY = 'A'</code>. See code below.</p>
  
  <h2>Issues</h2>
  
  <ol>
  <li><code>IS_MEMBER</code> function is invoked once per every row, making <code>SELECT DISTINCT CATEGORY FROM MY_TABLE</code> query run more than 3 minutes instead of 1 second. <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/21414e2e-b524-4677-ad79-a665b523ef67/performance-issues-while-using-the-ismember-function-preemptiveoslookupaccountsid-?forum=sqldatabaseengine" rel="nofollow noreferrer">Looks to be a common issue</a>.</li>
  <li>Even by hardcoding <code>fn_predicate</code>'s condition with <code>WHERE CATEGORY = 'A'</code> (remove <code>IS_MEMBER</code> 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.</li>
  </ol>
  
  <h2>Questions</h2>
  
  <ol>
  <li><em>Is there any way to call <code>IS_MEMBER</code> function no more times than there are security groups?</em> Means somehow cache the result or store it to global variable, etc.</li>
  <li><em>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?</em></li>
  </ol>
  
  <h2>Code</h2>
  
  <pre><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
  </code></pre>
  
  <h2>P.S.</h2>
  
  <p>I may rephrase this question to - <em>are there any better options than the 1st one?</em></p>
  
  <p>Three possible approaches:</p>
  
  <ol>
  <li><p>Create view per <code>CATEGORY</code>, and restrict each group to separate view. </p>
  
  <ul>
  <li><em>Pros:</em> The performance of <code>MY_TABLE</code> for PBI reporting isn't impacted </li>
  <li><em>Cons:</em> 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?</li>
  </ul></li>
  <li><p>Apply RLS on top of <code>MY_TABLE</code>. </p>
  
  <ul>
  <li><em>Pros:</em> My current approach. Simple to implement, no additional tables and views. Scalable, in case of more CATEGORIES, groups or rules will be added.</li>
  <li><em>Cons:</em> See "Issues" section. Performance of <code>MY_TABLE</code> is hardly impacted and isn't acceptable</li>
  </ul></li>
  <li><p>Hybrid approach. Create single <code>MY_TABLE_VIEW</code> and apply RLS on top of the view.</p>
  
  <ul>
  <li><em>Pros:</em> all benefits of the 2nd option, plus <code>MY_TABLE</code> performance  for PBI reporting isn't impacted.</li>
  <li><em>Cons:</em> still need to solve <code>IS_MEMBER</code> performance issue, see 1st point from "Issues" section.</li>
  </ul></li>
  </ol>