[SOLVED] SQL row level security policy

SQL row level security policy

I have a database with following structure:

Data:

Employee	Department
1	1
2	1
3	2
4	2

Users

Manager	Department
yyy	1
yyy	2
xxx	2
zzz	3

So manager yyy, has access to all rows with department 1 or 2. Manager xxx has access to rows with department 2, zzz to department 3.

I'm trying to construct a function and security policy accordingly, but with no success.

This select statement works correct, but I want to implement it as RLS:

select * 
from Data as D
join Users as U on U.department = D.department
where U.manager = USER_NAME()

Can anyone help?

Solution

Your sample data seems incorrectly matched (I see no rows in Data for department 3, so yyy returns all rows). But here is what I think you're looking for:

CREATE FUNCTION dbo.CheckManager
(
  @Department int
)
RETURNS TABLE WITH SCHEMABINDING
AS 
  RETURN
  (
    SELECT Allow = 1
      FROM dbo.Users
      WHERE Department = @Department
        AND Manager = USER_NAME()
  );
GO

CREATE SECURITY POLICY dbo.ManagersPolicy
ADD FILTER PREDICATE dbo.CheckManager(Department)
ON dbo.Data WITH (STATE = ON);
GO