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?