MSSQL-最佳实践-行级别安全解决方案

2021-08-22 09:01:52

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用户，都能够看到所有的数据，如下：MSSQL-最佳实践-行级别安全解决方案

如此，无法做到表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

结果展示如下图所示：
MSSQL-最佳实践-行级别安全解决方案

从截图来看，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，接下来将这条数据删除了。展示截图如下：
MSSQL-最佳实践-行级别安全解决方案

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

建立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，最后将其删除，整个过程没有报错，均成功执行，如下图所示：
MSSQL-最佳实践-行级别安全解决方案

同样，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：
MSSQL-最佳实践-行级别安全解决方案

但是，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.

错误截图如下所示：
MSSQL-最佳实践-行级别安全解决方案

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

最后总结

本期月报我们分享了使用SQL Server 2016引入的新特性 Row Level Security实现数据访问控制解决方案最佳实践，在用户无需对应用做任何改动的情况下实现表行级别数据查询和操作访问控制，使得最大限度保证表行级别数据安全。

上一篇：MSSQL-最佳实践-如何监控备份还原进度


下一篇：C# DataSet性能最佳实践