MSSQL行级别访问控制解决方案

原理分析

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行级别访问控制解决方案

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

文章转载自:http://yun.jinre.com/newsinfo/794559.html

上一篇:zookeeper 实现分布式锁安全用法
下一篇:阿里云DDoS高防IP出现卡顿、延迟、访问不通等问题 怎么处理?