How to do row-level security allowing multiple accessId to match the row's id in SQL Server?-CodePudding

I use a function as BLOCK and FILTER PREDICATE in order to implement row-level security.

CREATE FUNCTION [dbo].[AccessPredicate] 
    (@accessId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN 
        SELECT 1 AS AccessPredicateResult
        WHERE @accessId = CAST(SESSION_CONTEXT(N'accessId ') AS int)

I now need to modify the session context variable to hold any number of accessId. Since it is a SQL_VARIANT type, I cannot use a TABLE variable or custom one. I resorted to a CSV list as NVARCHAR, e.g. 1,2,3

That means I need to update the SELECT query in my function to do a 'WHERE @accessId IN (...)'. Of course I can't just

SELECT 1 AS AccessPredicateResult
WHERE @accessId IN (CAST(SESSION_CONTEXT(N'accessId ') AS VARCHAR)) -- where accessId = '1,2,3'

Without liking the implications, I tried to do that by putting the whole query into a @sql VARCHAR variable and then EXEC sp_executesql @sql but that failed because SQL Server cannot have a DECLARE in a table valued function (for whatever reason!?). Neither does the table valued function allow EXEC a stored procedure as far as my research suggests.

I'm blocked by this, can't think of a way to do this now.

So I've got the allowed accessId in my C# code and somehow need to persist them in the session context (or are there other, well performing, methods?) so that my predicate can use it to confirm the row in question is accessible. What's the best way forward now?

Thanks

CodePudding user response：

You can use STRING_SPLIT in a subquery

CREATE OR ALTER FUNCTION dbo.AccessPredicate
    (@accessId int)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT 1 AS AccessPredicateResult
WHERE @accessId IN (
    SELECT s.value
    FROM STRING_SPLIT(CAST(SESSION_CONTEXT(N'accessId') AS varchar(4000)), ',')
);

I warn you though, that RLS is not foolproof security, and can be circumvented by a user able to craft custom queries.

As a side note: Why does SQL Server not allow variables or EXEC in functions?

EXEC is not allowed because a function must not be side-affecting.

Furthermore, an inline table function must be a single statement, therefore it makes no sense to DECLARE variables. It is effectively a parameterized view, and functions that way as far as the compiler is concerned.

If you do need to "declare" variables, you can use a VALUES virual table, along with CROSS APPLY. For example:

CREATE OR ALTER FUNCTION dbo.AccessPredicate
    (@accessId int)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT c2.Calc2 AS AccessPredicateResult
FROM (VALUES(
    SomeCalculationHere
)) AS c1(Calc1)
CROSS APPLY (VALUES(
    SomeFunction(c1.Calc1)
)) AS c2(Calc2);