SQL Server - Row Level security using Cross Apply

Question

I'm developing a filter predicate for Row Level Security in SQL Server/Azure SQL Database.

Application logic related to the visibility coins required that a lot of tables must be read in order to understand if, a determined user, can read or less a row. I develop the following logic:

An inline table value function for the filter predicate; -- Inside it, a CTE to get all the profiles for the user. The results of this CTE must be joined with a set of Inline Table valued functions using CROSS APPLY operator.

Following the code:

   CREATE FUNCTION [scr].[prj_Projects](@ProjectId INT, @FilterId1 INT, @FilterId2 INT, @FilterId3 INT, @FilterId4 INT, @FilterId5 INT, @FilterId6 INT)  
   RETURNS TABLE  
WITH SCHEMABINDING  
AS  
   RETURN (  
        WITH UserProfiles AS (
            SELECT up.Id
            FROM dbo.users u
                INNER JOIN dbo.UsersProfiles up ON up.UserId = u.Id
                INNER JOIN dbo.Profiles p ON p.id = up.ProfileId
            WHERE SESSION_CONTEXT(N'UserId') = u.Id
        )
            SELECT Result = 1
             FROM UserProfiles up
                CROSS APPLY [scr].[prj_ProfilesFilter1](up.Id, @FilterId1) 
                CROSS APPLY [scr].[prj_ProfilesFilter2](up.Id, @FilterId2) 
                CROSS APPLY [scr].[prj_ProfilesFilter3](up.Id, @FilterId3) 
                CROSS APPLY [scr].[prj_ProfilesFilter4](up.Id, @FilterId4) 
                CROSS APPLY [scr].[prj_ProfilesFilter5](up.Id, @FilterId5) 
                CROSS APPLY [scr].[prj_ProfilesFilter6](up.Id, @FilterId6) 
      )
                                                  
GO

Following the query for one ITVF (they have all the same structure).

CREATE OR ALTER FUNCTION [scr].[prj_ProfilesFilter1] (@UserProfileId INTEGER, @FilterId1 INTEGER)
    RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    WITH UserProfile AS (
        SELECT DISTINCT upba.FilterId1
        FROM dbo.UsersProfilesFilters upba
        WHERE upba.UserProfileId = @UserProfileId 
    ), Datas AS (

        SELECT b.Id
        FROM dbo.Filters1 b
        INNER JOIN UserProfile c ON c.FilterId1 = b.Id

        UNION ALL

        SELECT b.Id
        FROM dbo.Filters1 b
        WHERE NOT EXISTS (SELECT 1 FROM UserProfile) 
          
        UNION ALL
        
        SELECT -1
        WHERE NOT EXISTS (SELECT 1 FROM UserProfile)

    ) SELECT Id
     FROM Datas d
     WHERE d.Id = ISNULL(@FilterId1 , -1)
    
)
GO

I thought the design would have been ok, but unfortunately the performances are very bad. Is not related to the execution plan (I see only seek and no scan for example), but the problem is related to the high number of SCAN COUNT and LOGICAL READS that the query perform (very very high). It's strange because each cross apply returns only ONE ROW and there are only set operation.

Do you have any ideas on how to avoid this high number of logical reads? I think it's a bug related to RLS

UPDATE: Here the execution plan of the query: https://www.brentozar.com/pastetheplan/?id=r1mHXespO As I said, the problem is related to the number of logical reads and scan count that the query perform, because the execution plan seems ok.

If all your filter queries have the same structure, why are you calling it 6 times and not just passing 6 values? — iamdave
Also, doesn't the WHERE d.Id = ISNULL(@FilterId1 , d.Id) part of your query not default to granting access where a filter is not included? Are you sure this is the desired behaviour? — iamdave
They have the same structure/logic, but applied on different tables. Imagine that you can see a row if you are english or if you work in a predetermined company. They are different criterias, but the structure of the ITVF is the same. I avoid to paste everything. — MFF
I updated the scripts (I pasted the old one) with the correct logic. If the parameter is null, you can see the row only if your profile is not listed for this specific filter (the reason of UNION ALL statement). — MFF
The second and third parts of the union all seem to contradict each other as they both cover the same case, and arguably you are doing it wrong by using the lack of any filters as an indication that every filter should be applied. Just add every filter to each user. Also why do you have distinct surely they should be unique values anyway? — Charlieface

MFF MFF · Accepted Answer · 2021-07-16T07:17:52

Ok, I figured out the problem: the result of SESSION_CONTEXT procedure must be casted, otherwise SQL Server cannot do correct assumption related to the cardinality of the query. Casting SESSION_CONTEXT, the performance became extremely good.

WHERE CAST(SESSION_CONTEXT(N'UserMail') AS NVARCHAR(255) = u.Email

SQL Server - Row Level security using Cross Apply

1 Answers