title: MSSQL - 最佳实践 - 行级别安全解决方案
author: 风移
摘要
在 SQL Server 安全系列专题月报分享中, 我们已经分享了: 如何使用对称密钥实现 SQL Server 列加密技术, 使用非对称密钥加密方式实现 SQL Server 列加密, 使用混合密钥实现 SQL Server 列加密技术和列加密技术带来的查询性能问题以及相应解决方案四篇文章. 本期月报我们分享使用 SQL Server RLS(Row Level Security)行级别访问控制解决方案最佳实践.
问题引入
在很久以前我分享过一篇文章 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 过滤函数: 用于控制哪些用户可以查看哪些数据的逻辑控制
创建表级别的安全策略: 用于实现表中数据行级别的安全访问控制
实现方法
按照问题引入部分的要求, 我们需要实现数据查询访问控制和数据操作访问控制两个方面, 以下是详细的实现方法步骤.
测试环境准备
还是沿用之前文章的测试场景数据, 构建测试环境如下:
- -- Create Test Database
- IF DB_ID('Test') IS NULL
- CREATE DATABASE Test;
- GO
- USE Test
- GO
- --create three logins(CEO, manager, employee)
- --create login CEO
- IF EXISTS(
- SELECT *
- FROM sys.syslogins
- WHERE name = 'CEO')
- BEGIN
- DROP LOGIN CEO;
- END
- GO
- CREATE LOGIN CEO with password='CEODbo',check_policy = off;
- GO
- --create user CEO
- IF USER_ID('CEO') is not null
- DROP USER CEO;
- GO
- CREATE USER CEO FOR LOGIN CEO;
- GO
- --create login Manager
- IF EXISTS(
- SELECT *
- FROM sys.syslogins
- WHERE name = 'Manager')
- BEGIN
- DROP LOGIN Manager;
- END
- GO
- CREATE LOGIN Manager with password='ManagerDbo',check_policy = off;
- GO
- --create user manager
- IF USER_ID('Manager') is not null
- DROP USER Manager;
- GO
- CREATE USER Manager FOR LOGIN Manager;
- GO
- --create login employee
- IF EXISTS(
- SELECT *
- FROM sys.syslogins
- WHERE name = 'employee')
- BEGIN
- DROP LOGIN employee;
- END
- GO
- CREATE LOGIN employee with password='employeeDbo',check_policy = off;
- GO
- --create user employee
- IF USER_ID('employee') is not null
- DROP USER employee
- GO
- CREATE USER employee FOR LOGIN employee;
- GO
- --create basic TABLE
- IF OBJECT_ID('dbo.tb_Test_ViewPermission','u')is not null
- DROP TABLE dbo.tb_Test_ViewPermission
- ;
- GO
- CREATE TABLE dbo.tb_Test_ViewPermission
- (
- id int identity(1,1) not null primary key
- ,name varchar(20) not null
- ,level_no int not null
- ,title varchar(20) null
- ,viewByCEO char(1) not null
- ,viewByManager char(1) not null
- ,viewByEmployee char(1) not null
- ,salary decimal(9,2) not null
- );
- --data init.
- INSERT INTO dbo.tb_Test_ViewPermission
- SELECT 'AA',0,'CEO','Y','N','N',1000000.0
- union all
- SELECT 'BB',1,'Manager','Y','Y','N',100000.0
- union all
- SELECT 'CC',2,'employee','Y','Y','Y',10000.0
- ;
- GO
- select * from dbo.tb_Test_ViewPermission
在没做权限控制的情况下, 不论是 CEO,Manger 还是 Employee 用户, 都能够看到所有的数据, 如下:
如此, 无法做到表 tb_Test_ViewPermission 行级别的数据安全, 也无法满足我们对数据行级别的查询和操作访问控制要求.
数据查询访问控制
让我们来看看如何实现行级别数据查询安全访问控制, 需要实现如下三步:
建立 RLS 过滤函数
建立表级安全策略
验证查询访问控制
建立 RLS 过滤函数
首先, 建立 RLS 过滤函数, 用于实现哪些用户可以查看哪些数据的访问控制逻辑, 实现代码如下:
- USE Test
- GO
- CREATE SCHEMA RLSFilterDemo;
- GO
- -- Create filter title function
- CREATE FUNCTION RLSFilterDemo.fn_getTitle(@title AS varchar(20))
- RETURNS TABLE
- WITH SCHEMABINDING
- AS
- RETURN
- SELECT 1 AS result
- WHERE USER_NAME() IN (
- SELECT A.title
- FROM dbo.tb_Test_ViewPermission AS A
- INNER JOIN dbo.tb_Test_ViewPermission AS B
- ON a.level_no <= B.level_no
- WHERE B.title = @title)
- 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, 反之, 则认为没有权限访问对应的行.
建立表级安全策略
接下来, 我们基于前面的过滤函数建立表级别安全策略, 并且使得这个安全策略了生效, 代码如下:
- USE Test
- GO
- -- create security policy base on the filter function
- CREATE SECURITY POLICY TitleFilter
- ADD FILTER PREDICATE RLSFilterDemo.fn_getTitle(title)
- ON dbo.tb_Test_ViewPermission
- WITH (STATE = ON);
- GO
验证查询访问控制
最后一步, 我们需要对行级别安全的访问控制进行查询验证和测试:
- USE Test
- GO
- -- grant permissions to three users.
- GRANT SELECT ON dbo.tb_Test_ViewPermission TO CEO;
- GRANT SELECT ON dbo.tb_Test_ViewPermission TO Manager;
- GRANT SELECT ON dbo.tb_Test_ViewPermission TO employee;
- USE Test
- GO
- --CEO can read all of the data
- EXECUTE AS USER='CEO'
- SELECT WhoAmI = USER_NAME()
- SELECT * FROM dbo.tb_Test_ViewPermission
- REVERT;
- GO
- USE Test
- GO
- --Manager can read manager and employee's data, but except CEO's.
- EXECUTE AS USER='Manager'
- SELECT WhoAmI = USER_NAME()
- SELECT * FROM dbo.tb_Test_ViewPermission
- REVERT;
- GO
- USE Test
- GO
- --employee just can read employee's data, couldn't query CEO and Manger's.
- EXECUTE AS USER='employee'
- SELECT WhoAmI = USER_NAME()
- SELECT * FROM dbo.tb_Test_ViewPermission
- REVERT;
- GO
结果展示如下图所示:
从截图来看, CEO 可以查看任何人的数据行; Manger 可以查看自己和 Employee 的数据行; 而 Employee 仅能查看自己的数据. 说明已经成功实现了行级别的用户查询访问控制, 达到了我们既定的目的.
数据操作访问控制
成功完成数据查询行级别访问控制的实践之后, 我们再深入一步实现行级别数据操作访问控制. 即: 实现用户仅能操作自己及下层级的数据 (level_no 大于等于自己), 而不能操作自己层级之上(level_no 小于自己) 的数据. 如果, 我们不做任何的访问控制, 任何有权限的用户都可以操作我们这张表的数据, 如下:
- -- First, take the security policy off.
- ALTER SECURITY POLICY TitleFilter
- WITH (STATE = OFF);
- -- Try to perform the DML action to see the DML permission control
- USE Test
- GO
- -- grant permissions to all users.
- GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO CEO;
- GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO Manager;
- GRANT UPDATE, INSERT, DELETE ON dbo.tb_Test_ViewPermission TO employee;
- USE Test
- GO
- --try to test INSERT by user employee
- EXECUTE AS USER='employee'
- SELECT WhoAmI = USER_NAME()
- INSERT INTO dbo.tb_Test_ViewPermission
- SELECT 'DD',2,'employee','Y','Y','Y',100.0;
- SELECT * FROM dbo.tb_Test_ViewPermission;
- UPDATE TOP(1) dbo.tb_Test_ViewPermission
- SET name = 'EE'
- WHERE name = 'DD';
- SELECT * FROM dbo.tb_Test_ViewPermission;
- DELETE TOP (1)
- FROM dbo.tb_Test_ViewPermission
- WHERE name = 'EE'
- ;
- SELECT * FROM dbo.tb_Test_ViewPermission;
- REVERT
- GO
- ;
执行以上语句后, 我们发现, employee 用户插入一条数据 DD, 然后更新为 EE, 接下来将这条数据删除了. 展示截图如下:
说明在没有行级别安全访问控制的情况下, 任何有权限的用户都可以操作这张表所有的数据, 无法实现数据行级别安全.
建立 RLS 过滤函数
同样, 首先建立 RLS 过滤函数, 用于实现哪些用户可以操作哪些数据的访问控制逻辑:
- -- Here we go to show how to allow manager and restrict employee dml operation
- USE Test
- GO
- CREATE SCHEMA RLSBlockDemo;
- GO
- CREATE FUNCTION RLSBlockDemo.fn_getTitle(@title AS varchar(20))
- RETURNS TABLE
- WITH SCHEMABINDING
- AS
- RETURN
- SELECT 1 AS result
- WHERE USER_NAME() IN (
- SELECT A.title
- FROM dbo.tb_Test_ViewPermission AS A
- INNER JOIN dbo.tb_Test_ViewPermission AS B
- ON a.level_no <= B.level_no
- WHERE B.title = @title)
- GO
建立表级别安全策略
基于 RLS 过滤函数, 建立表级别操作控制的安全策略并且使其启用生效:
- USE Test
- GO
- ALTER SECURITY POLICY TitleFilter
- ADD BLOCK PREDICATE RLSBlockDemo.fn_getTitle(title)
- ON dbo.tb_Test_ViewPermission AFTER INSERT;
- ALTER SECURITY POLICY TitleFilter
- WITH (STATE = ON);
验证操作访问控制
接下来, 就是表级别操作的访问控制验证了, Manger 用户可以完全操作自己的数据:
- USE Test
- GO
- --try to test INSERT by user Manager
- EXECUTE AS USER='Manager'
- SELECT WhoAmI = USER_NAME()
- INSERT INTO dbo.tb_Test_ViewPermission
- SELECT 'DD',1,'Manager','Y','Y','Y',100.0;
- SELECT * FROM dbo.tb_Test_ViewPermission;
- UPDATE TOP(1) dbo.tb_Test_ViewPermission
- SET name = 'EE'
- WHERE name = 'DD';
- SELECT * FROM dbo.tb_Test_ViewPermission;
- DELETE TOP (1)
- FROM dbo.tb_Test_ViewPermission
- WHERE name = 'EE'
- ;
- SELECT * FROM dbo.tb_Test_ViewPermission;
- REVERT
- GO
Manger 首先插入了一条 DD 数据, 然后更新为 EE, 最后将其删除, 整个过程没有报错, 均成功执行, 如下图所示:
同样, Manger 也可以操作 Employee 数据, 即自己层级以下的数据(level_no 大于自己):
- USE Test
- GO
- --It's OK to INSERT manger record by user Manager
- EXECUTE AS USER='Manager'
- SELECT WhoAmI = USER_NAME()
- INSERT INTO dbo.tb_Test_ViewPermission
- SELECT 'EE',2,'employee','Y','Y','N',100.0;
- SELECT * FROM dbo.tb_Test_ViewPermission;
- REVERT
- GO
- ;
Manger 插入了一条 Employee 的数据 EE:
但是, Manger 不能操作自己层级以上的数据(level_no 小于自己), 比如 CEO 的数据, 如下代码:
- USE Test
- GO
- --Failed to INSERT CEO record by user Manager
- EXECUTE AS USER='Manager'
- SELECT WhoAmI = USER_NAME()
- INSERT INTO dbo.tb_Test_ViewPermission
- SELECT 'DD',0,'CEO','Y','Y','Y',100.0;
- REVERT
- GO
- ;
Manger 试图操作 CEO 的数据, 会报告如下错误:
- (1 row affected)
- Msg 33504, Level 16, State 1, Line 286
- The attempted operation failed because the target object 'Test.dbo.tb_Test_ViewPermission' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
- The statement has been terminated.
错误截图如下所示:
在完成数据行级别安全策略之后, 我们可以成功实现用户数据行级别操作访问控制安全, 达到我们既定安全目标.
最后总结
本期月报我们分享了使用 SQL Server 2016 引入的新特性 Row Level Security 实现数据访问控制解决方案最佳实践, 在用户无需对应用做任何改动的情况下实现表行级别数据查询和操作访问控制, 使得最大限度保证表行级别数据安全.
来源: https://yq.aliyun.com/articles/688739