Using Row Level Security to protect the table that tracks row level securities

Viewed 40

The general setup of my RLS is that I have a table like Orders:

OrderID  Customer
1        John
2        Bill

And a table, RowLevelSecurityPermissions, that tracks which principal is allowed to see which order:

Principal  Table   ID
admin      Orders  -1              --can see everything
sales1     Orders  1               --can only see order 1
sales2     Orders  2               --can only see order 2

RLS functions look like:

CREATE FUNCTION sec.OrdersRLSFilter(@OrderId AS int) RETURNS TABLE WITH SCHEMABINDING AS RETURN 

    SELECT 1 AS R
    WHERE EXISTS (
        SELECT null 
        FROM sec.RowLevelSecurityPermissions 
        WHERE Username = USER_NAME() AND TableName = 'Orders' AND Id IN (-1, @OrderId)
    )


CREATE SECURITY POLICY OrdersSecurityPolicy
ADD FILTER PREDICATE sec.OrdersRLSFilter(OrderId)
ON dbo.Orders
WITH (STATE = ON);

I also wanted to protect the RowLevelSecurityPermissions table in the same way so any principal doing a select * from it would only see their own rows:

CREATE FUNCTION sec.RowLevelSecurityPermissionsRLSFilter(@Principal AS VARCHAR) RETURNS TABLE WITH SCHEMABINDING AS 

    RETURN SELECT 1 AS R WHERE USER_NAME() IN ('admin', @Principal)

CREATE SECURITY POLICY RowLevelSecurityPermissionsSecurityPolicy
ADD FILTER PREDICATE sec.RowLevelSecurityPermissionsRLSFilter(Principal)
ON sec.RowLevelSecurityPermissions
WITH (STATE = ON);

I thought this would be the effective equivalent of

select * from sec.RowLevelSecurityPermissions WHERE USER_NAME() IN ('admin', Principal)

which (as a query) does work if logged in as sales1; it shows me only sales1's rows (and if logged in as admin it shows me all)

However, when I go the RLS approach and do a SELECT * FROM sec.RowLevelSecurityPermissions I get no rows if logged in as sales1; where did my logic break down?

0 Answers
Related