SQL Server SQL row level security policy

tcomlyy6 于 12个月前发布在其他

关注(0)|答案(1)|浏览(99)

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?

sql-server

来源：https://stackoverflow.com/questions/77017749/sql-row-level-security-policy

1条答案

按热度按时间

i7uq4tfw1#

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

赞(0）回复(0）举报 12个月前

我来回答

SQL Server SQL row level security policy

1条答案

相关问题

热门标签

最新问答