在很久以前我分享过一篇文章SQL Server使用视图做权限控制来实现行级别数据安全。今天我们把这个问题再次抛出来：不同用户访问同一张表，如何做到不同用户仅能访问属于自己及以下层级的数据。还是举例这个例子，比如：公司有CEO，Manger和普通的employee三种角色，CEO可以查看CEO、Manager和employee层级的数据；Manger只能查看Manger和employee的数据，不能查看CEO层级；而employee只能查看employee的数据，不能查看CEO和Manager级别的数据。今天我们把场景更进一步，要求用户仅能操作（DML）自己及下属的数据，不能跨级操作上层级的数据。

SQL Server 2016版本引入了Row Level Security（以下简称RLS）新特性。这个特性本身不会对数据库中表数据做任何的加密和解密操作，而是基于用户定义的安全策略来限制和过滤表中行级别的数据，使得数据库引擎不会过多的暴露数据行，从而实现了非常简洁的访问控制方法，对用户的应用和客户端完全透明。因此，可以在用户不做任何代码修改情况下，简单两个步骤就可以轻松实现表行级别的数据安全访问控制。

 创建RLS过滤函数：用于控制哪些用户可以查看哪些数据的逻辑控制

 创建表级别的安全策略：用于实现表中数据行级别的安全访问控制

按照问题引入部分的要求，我们需要实现数据查询访问控制和数据操作访问控制两个方面，以下是详细的实现方法步骤。

还是沿用之前文章的测试场景数据，构建测试环境如下：

在没做权限控制的情况下，不论是CEO，Manger还是Employee用户，都能够看到所有的数据，如下：

如此，无法做到表tb_Test_ViewPermission行级别的数据安全，也无法满足我们对数据行级别的查询和操作访问控制要求。

让我们来看看如何实现行级别数据查询安全访问控制，需要实现如下三步：

 建立RLS过滤函数

 建立表级安全策略

建立RLS过滤函数

首先，建立RLS过滤函数，用于实现哪些用户可以查看哪些数据的访问控制逻辑，实现代码如下：

  2. GO
  3. CREATE SCHEMA RLSFilterDemo;  
  4. GO
  6. -- Create filter title function
  7. CREATE FUNCTION RLSFilterDemo.fn_getTitle(@title AS varchar(20))  
  8.     RETURNS TABLE  
  9. WITH SCHEMABINDING  
  10. AS  
  11. RETURN
  12.     SELECT 1 AS result   
  13.     WHERE USER_NAME() IN (
  14.     SELECT A.title 
  15.     FROM dbo.tb_Test_ViewPermission AS A
  16.         INNER JOIN  dbo.tb_Test_ViewPermission AS B
  17.         ON a.level_no <= B.level_no
  18.     WHERE B.title = @title)
  19. GO

稍微解释下代码实现：使用level_no来控制用户访问数据的层级，level_no值越小，层级越高，权限越大。即：level_no为0（对应CEO用户）可以查看level_no为0（对应CEO自己）、1（对应Manger用户）和2（对应Employee普通用户）；level_no为1的能查看自己和Employee的数据；而level_no为2的仅能查看自己的数据行。当我们发现查询的用户和对应的title匹配的时候，我们就认为这个用户有相应的权限，即函数返回值为1，反之，则认为没有权限访问对应的行。

建立表级安全策略

接下来，我们基于前面的过滤函数建立表级别安全策略，并且使得这个安全策略了生效，代码如下：

  1. USE Test
  2. GO
  3. -- create security policy base on the filter function
  4. CREATE SECURITY POLICY TitleFilter  
  5. ADD FILTER PREDICATE RLSFilterDemo.fn_getTitle(title)   
  6. ON dbo.tb_Test_ViewPermission  
  7. WITH (STATE = ON);
  8. GO

验证查询访问控制

最后一步，我们需要对行级别安全的访问控制进行查询验证和测试：

结果展示如下图所示：

02.png

从截图来看，CEO可以查看任何人的数据行；Manger可以查看自己和Employee的数据行；而Employee仅能查看自己的数据。说明已经成功实现了行级别的用户查询访问控制，达到了我们既定的目的。

成功完成数据查询行级别访问控制的实践之后，我们再深入一步实现行级别数据操作访问控制。即：实现用户仅能操作自己及下层级的数据（level_no大于等于自己），而不能操作自己层级之上（level_no小于自己）的数据。如果，我们不做任何的访问控制，任何有权限的用户都可以操作我们这张表的数据，如下：

  1. -- First, take the security policy off.
  2. ALTER SECURITY POLICY TitleFilter
  3. WITH (STATE = OFF);
  4. -- Try to perform the DML action to see the DML permission control
  5. GO
  6. -- grant permissions to all users.
  7. GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO CEO;
  8. GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO Manager;
  9. GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO employee;
  12. USE Test
  14. --try to test INSERT by user employee
  15. EXECUTE AS USER='employee'
  16. SELECT WhoAmI = USER_NAME()
  18. INSERT INTO dbo.tb_Test_ViewPermission
  19. SELECT 'DD',2,'employee','Y','Y','Y',100.0;
  20. SELECT * FROM dbo.tb_Test_ViewPermission;
  22. UPDATE TOP(1) dbo.tb_Test_ViewPermission
  23. SET name = 'EE'
  24. WHERE name = 'DD';
  25. SELECT * FROM dbo.tb_Test_ViewPermission;
  27. DELETE TOP (1)
  28. FROM dbo.tb_Test_ViewPermission
  29. WHERE name = 'EE'
  30. ;
  31. SELECT * FROM dbo.tb_Test_ViewPermission;
  33. REVERT
  34. GO
  35. ;

执行以上语句后，我们发现，employee用户插入一条数据DD，然后更新为EE，接下来将这条数据删除了。展示截图如下：

说明在没有行级别安全访问控制的情况下，任何有权限的用户都可以操作这张表所有的数据，无法实现数据行级别安全。

建立RLS过滤函数

同样，首先建立RLS过滤函数，用于实现哪些用户可以操作哪些数据的访问控制逻辑：

  1. -- Here we go to show how to allow manager and restrict employee dml operation
  2. USE Test
  3. GO
  5. CREATE SCHEMA RLSBlockDemo;
  6. GO
  8. CREATE FUNCTION RLSBlockDemo.fn_getTitle(@title AS varchar(20))
  9.     RETURNS TABLE
  10. WITH SCHEMABINDING
  11. AS
  12.     RETURN 
  13.     SELECT 1 AS result
  14.     SELECT A.title 
  15.     FROM dbo.tb_Test_ViewPermission AS A
  16.         INNER JOIN  dbo.tb_Test_ViewPermission AS B
  18.     WHERE B.title = @title)
  19. GO

建立表级别安全策略

验证操作访问控制

接下来，就是表级别操作的访问控制验证了，Manger用户可以完全操作自己的数据：

  1. USE Test
  2. GO
  3. --try to test INSERT by user Manager
  4. EXECUTE AS USER='Manager'
  5. SELECT WhoAmI = USER_NAME()
  7. INSERT INTO dbo.tb_Test_ViewPermission
  8. SELECT 'DD',1,'Manager','Y','Y','Y',100.0;
  9. SELECT * FROM dbo.tb_Test_ViewPermission;
  11. UPDATE TOP(1) dbo.tb_Test_ViewPermission
  12. SET name = 'EE'
  13. WHERE name = 'DD';
  14. SELECT * FROM dbo.tb_Test_ViewPermission;
  16. DELETE TOP (1)
  17. FROM dbo.tb_Test_ViewPermission
  18. WHERE name = 'EE'
  19. ;
  20. SELECT * FROM dbo.tb_Test_ViewPermission;
  22. REVERT
  23. GO

Manger首先插入了一条DD数据，然后更新为EE，最后将其删除，整个过程没有报错，均成功执行，如下图所示：

04.png

同样，Manger也可以操作Employee数据，即自己层级以下的数据（level_no大于自己）：

  1. USE Test
  2. GO
  3. --It's OK  to INSERT manger record by user Manager
  4. EXECUTE AS USER='Manager'
  5. SELECT WhoAmI = USER_NAME()
  7. INSERT INTO dbo.tb_Test_ViewPermission
  8. SELECT 'EE',2,'employee','Y','Y','N',100.0;
  9. SELECT * FROM dbo.tb_Test_ViewPermission;
  10. REVERT
  11. GO
  12. ;

Manger插入了一条Employee的数据EE：

但是，Manger不能操作自己层级以上的数据（level_no小于自己），比如CEO的数据，如下代码：

Manger试图操作CEO的数据，会报告如下错误：

  1. (1 row affected)
  2. Msg 33504, Level 16, State 1, Line 286

错误截图如下所示：

06.png

在完成数据行级别安全策略之后，我们可以成功实现用户数据行级别操作访问控制安全，达到我们既定安全目标。