none
Row Level Security - Policy Enabled but not Filtering RRS feed

  • Question

  • I'm trying to implement row-level security in SQL Server 2016 but am getting stuck.  Below is the code being used for the predicate function and the security policy.  From what I can see, everything looks good, but when SSMS is showing the security policy is enabled, users who should have restricted access to the key dimension can see all of the data.  

    Predicate Function

    CREATE FUNCTION [dbo].[udf_RLSDimAgentIDPredicate] (@DimAgentId int)
    RETURNS table
    WITH SchemaBInding
    AS
    RETURN (select max(fn_securitypredicate_result) fn_securitypredicate_result 
            from
    	(
            --"Regular" Users: Does the AD Group they belong to have access to the incoming companyID?
            SELECT 1 fn_securitypredicate_result
            FROM Dim.Agent a
            INNER JOIN dbo.AgentGroupMapping arm ON a.MasterAgentId = arm.MasterAgentId 
            WHERE IS_MEMBER(arm.ADGroupName) = 1 and a.DimAgentId = @DimAgentId
    
    	UNION
            --"Power" Users and Admins: Do they belong to an AD Group that has access to all companies (designated by -1 in MasterAgentID in dbo.AgentGroupMapping)?
    	SELECT 1
    	FROM Dim.Agent a, dbo.AgentGroupMapping arm
    	WHERE IS_MEMBER(arm.ADGroupName) = 1 and arm.MasterAgentId = -1 and a.DimAgentId = @DimAgentId
    
    	) r
    WHERE r.fn_securitypredicate_result is not null)


    Security Policy

    CREATE SECURITY POLICY [dbo].[DimAgentPredicate] 
    ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Dim].[Agent],
    ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompClaim],
    ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompPremium],
    ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompProducer],
    ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompRegister],
    ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompQuoteSummary],
    ADD FILTER PREDICATE [dbo].[udf_RLSDimAgentIDPredicate]([DimAgentId]) ON [Fact].[WorkersCompExposure]
    WITH (STATE = ON, SCHEMABINDING = ON)

    The odd part that I can't get over is that when the security policy is disabled, any user who runs the following code gets the correct predicate result.  As a power user, 1 is returned for every company in Dim.Agent.  A user with restrictions to certain companies has 1's for just those companies with NULL being returned for everything they don't have access to.

    SELECT a.MasterAgentID, p.fn_securitypredicate_result
    FROM Dim.Agent a
    	OUTER APPLY dbo.udf_RLSDimAgentIDPredicate (a.DimAgentId) p

    A question that regularly runs through my mind is if the security policy is disabled, when I think it should be enabled.  One test I've been doing to verify is to run this same query.  With the policy enabled, it fails, because the function is referencing itself.

    Msg 4429, Level 16, State 1, Procedure udf_RLSDimAgentIDPredicate, Line 5 [Batch Start Line 28]
    View or function 'dbo.udf_RLSDimAgentIDPredicate' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
    Msg 4413, Level 16, State 1, Line 30
    Could not use view or function 'dbo.udf_RLSDimAgentIDPredicate' because of binding errors.

    Does anyone see issues in the code that would be preventing proper filtering?  Or, has anyone encountered something like this in the past, and if so, what was the solution?  Everything seems to be set up, based on the documentation I've found.  What am I missing?

     

    Thanks,

    Erin


    Thursday, June 6, 2019 12:38 PM

All replies

  • Hi,
    You can get some useful information from http://www.youdidwhatwithtsql.com/views-or-functions-cannot-reference-themselves-directly-or-indirectly/519/
    Hope it could help you.
    Monday, June 10, 2019 9:27 AM
  • Thank you for the suggestion.  However, the error message I included IS expected, when the policy is enabled.  It helps me to know for sure the policy is active and should be filtering data.

    The fact that data is not being filtered when the policy is enabled, this is the problem.

    Regards,

    Erin

    Monday, June 10, 2019 1:21 PM
  • This looks spooky:

         SELECT 1
         FROM Dim.Agent a, dbo.AgentGroupMapping arm
         WHERE IS_MEMBER(arm.ADGroupName) = 1 and arm.MasterAgentId = -1 and a.DimAgentId = @DimAgentId

    This is a cartesian join over all rows in Dim.Agent and dbo.AgentGroupMapping. So as long as there is one matching row in each table, this query will always return at least one row.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 10, 2019 9:28 PM