SQL Server Row Level Security with condition

Question

I am trying to implement RLS on a Table (fin.RLS_Data) with following Function. I have another table stg.UserAccess to control who can access the Data Table (RLS_Data).

I am testing this on

Microsoft SQL Server 2022 (RTM-CU5) (KB5026806) - 16.0.4045.3 (X64)
    May 26 2023 12:52:08
    Copyright (C) 2022 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>

UserAccess :

RLS_Data :

Following function works perfectly for readonly access users.

CREATE FUNCTION rls.fn_Data_Predicate
         (@DataProviderID AS INT, @TableName sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 as Result
    FROM [fin].[RLS_Data] d
    INNER JOIN [stg].[UserAccess] e ON e.DataProviderId = d.DataProviderID
    WHERE d.DataProviderID = @DataProviderID
        AND e.[User Email] = CURRENT_USER
        AND e.TableName = @TableName 
        AND e.isAuthorized = 1
);

I want to have some exception where admin user (isAdmin) can have full access to the Data Table (RLS_Data).

user2 who is admin user should have full access to Data Table (RLS_Data), but user1 need to have access to data for DataProviderId = 10 in Data Table (RLS_Data)

I tried following

SELECT 1 as Result
FROM [fin].[RLS_Data] d
INNER JOIN [stg].[UserAccess] e ON e.DataProviderId = d.DataProviderID
WHERE e.[User Email] = CURRENT_USER 
    AND ( e.isAdmin = 1 
        OR (d.DataProviderID = @DataProviderID
            AND e.TableName = @TableName  
            AND e.isAuthorized = 1
            )
        )

This still isn't returning data for all DataProviderID if user has isAdmin set to true because of INNER JOIN [stg].[UserAccess] e ON e.DataProviderId = d.DataProviderID

I am open to solution where I have to hardcode the admin users inside function. Looking for a function which can do following , nut not sure how to implement this in function

IF CURRENT_USER IN ('dbo', 'DBENTPBIPROD')
    SELECT 1 as Result
    FROM [fin].[RLS_Data] d
ELSE
    SELECT 1 as Result
    FROM [fin].[RLS_Data] d
    INNER JOIN [stg].[UserAccess] e ON e.DataProviderId = d.DataProviderId
    WHERE d. DataProviderID = e.DataProviderId
        AND e.[User Email] = CURRENT_USER
        AND e.TableName = 'RLS_Data'
        AND e.isAuthorized = 1

Any better way to do this ?

Answer 1

You only need to check the relation by provider if the user is not an admin, so you can say this without touching the RLS_Data table again (since the function gets that DataProviderId from the predicate, right?):

SELECT 1 as Result
FROM stg.UserAccess AS u
WHERE u.[User Email] = CURRENT_USER
  AND
  (
    (
      /* admin can see everything regardless of provider: */
      u.IsAdmin = 1 
    )
    OR
    (
      /* only certain users are authorized: */
      u.DataProviderId = @DataProviderId
      AND u.TableName = @TableName
      AND u.isAuthorized = 1
    )
  );

I've validated that this works. First, I created three logins and added them to a new database:

CREATE LOGIN bob_the_admin   WITH PASSWORD = 'boo', CHECK_POLICY = OFF;
CREATE LOGIN jim_the_peon    WITH PASSWORD = 'boo', CHECK_POLICY = OFF;
CREATE LOGIN ted_the_outcast WITH PASSWORD = 'boo', CHECK_POLICY = OFF;
GO
CREATE DATABASE foo;
GO
USE foo;
GO
CREATE USER bob_the_admin;
CREATE USER jim_the_peon;
CREATE USER ted_the_outcast;
GO
ALTER ROLE db_datareader ADD MEMBER bob_the_admin;
ALTER ROLE db_datareader ADD MEMBER jim_the_peon;
ALTER ROLE db_datareader ADD MEMBER ted_the_outcast;

Then created these tables and populated them with data:

CREATE TABLE dbo.RLS_Data
(ProductId int, DistributorId int, DataProviderId int);

INSERT dbo.RLS_Data VALUES(54069,10000001,10),(50444,10000002,40),
      (86603,10000003,10),(44753,10000004,40),(86589,10000005,10);

CREATE TABLE dbo.UserAccess
(
  Id int identity(1,1),
  [User Email] sysname,
  DataProviderId int,
  TableName sysname,
  isAuthorized bit,
  isAdmin bit
);

INSERT dbo.UserAccess VALUES
('bob_the_admin', 10, 'RLS_Data', 1, 1),
('jim_the_peon',  40, 'RLS_Data', 1, 0);

Then created this function and security policy:

CREATE OR ALTER FUNCTION dbo.FN_RLS_LetThemIn
(
    @DataProviderId int,
    @TableName sysname
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN 
   (
     SELECT 1 as Result
       FROM dbo.UserAccess AS u
       WHERE u.[User Email] = CURRENT_USER
       AND
       (
         (
           u.IsAdmin = 1 
         )
         OR
         (
           u.DataProviderId = @DataProviderId
           AND u.TableName = @TableName
           AND u.isAuthorized = 1
         )
       )
   );
GO

CREATE SECURITY POLICY RLS_LetThemIn
    ADD FILTER PREDICATE dbo.FN_RLS_LetThemIn(DataProviderID, N'RLS_Data')
    ON [dbo].[RLS_Data]
    WITH (STATE = ON);

When running as bob the admin, they get all the rows:

When running as jim the peon, they just get 40:

And when running as ted the outcast, they get nothing:

Same results for ted the outcast if I insert this row (explicitly not authorized):

INSERT dbo.UserAccess VALUES
('ted_the_outcast', 10, 'RLS_Data', 0, 0);

Please actually try implementing this before responding with comments that are really hard to understand, like "I want data from [fin].[RLS_Data] to be returned." The function doesn't return data from that table, it merely acts as a gatekeeper to which rows the current user can access. It's right there in the first line:

SELECT 1 as Result

That's all the function returns. A constant. Stop thinking about it like a join or a function that actually returns your data. It sits there and waits for users to query data from the source table, then the function only exists to tell the query which rows that user can access.