SQL Server 2016:如何为规范化表设置行级别安全性 (RLS)

Question

我有三个表MST_EMployee、MST_ProfitCenter和Mapping_Employee_ProfitCenter。我想在Mapping_Employee_ProfitCenter表上设置行级别安全性。

我正在执行的步骤：

步骤 1：创建一个表值函数：

CREATE FUNCTION [RLS].[fn_CanSeeMapping] ( @UserName AS sysname )
RETURNS TABLE
WITH SCHEMABINDING 
AS
   RETURN
        (SELECT 1 AS 'CanSee' 
         WHERE @UserName IN (SELECT emp.ATTRFirstName  
                             FROM dbo.MAPPING_Employee_ProfitCenter map 
                             INNER JOIN dbo.MST_Employee emp ON map.FK_Employee_ID = emp.Id
                             WHERE emp.ATTRFirstName = USER_NAME())

步骤2：创建并添加了对映射表的过滤器，并将用户名作为映射表中存在ID的参数传递。

CREATE SECURITY POLICY [dbo].[Security_Policy] 
ADD FILTER PREDICATE [RLS].[fn_CanSeeMapping]([FK_Employee_ID]) ON  [dbo].[MAPPING_Employee_ProfitCenter] 
ALTER SECURITY POLICY [dbo].[Security_Policy] WITH (STATE = ON)

现在，当我执行以下查询而不是为用户"User1"提供记录时，它为所有用户提供记录。

EXEC ('select * from MAPPING_Employee_ProfitCenter') as user='User1'

如果有人知道如何处理这种情况，请帮助我。

谢谢！

Answer 1

尝试以下操作，这应该是适当的语法：

EXECUTE AS USER = 'User1';  
select * from MAPPING_Employee_ProfitCenter;   
REVERT;