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.

Answer 1

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