SQL Server On Linux（10）—— SQL Server On Linux安全性（3）——Dynamic Data Masking

本人新书上市，请多多关照： 《SQL Server On Linux运维实战 2017版从入门到精通》

大家是否已经很熟悉快递单上的手机号中间几位打码的情况？虽然我不清楚别人用的是什么技术，但是在SQL Server 2016开始，使用Dynamic Data Masking（DDM，动态数据屏蔽）就可以实现这种效果，下面来演示一下。

DDM 演示

为了保证环境不受影响，这次使用TempDB来演示。以sa身份登录并执行：

--创建测试数据
USE tempdb;
GO
SELECT TOP (10) 
  ID        = IDENTITY(INT, 1, 1),
  FirstName = RIGHT(o.name, 8), 
  LastName  = LEFT(o.name, 12), 
  Email     = LEFT(o.name, 9) + '@' + RIGHT(o.name, 11) + '.com',
  SSN       = STUFF(STUFF(RIGHT('000000000' 
            + RTRIM(ABS(CHECKSUM(NEWID()))),9),4,0,'-'),7,0,'-'),
  BirthDate = DATEADD(DAY, -ABS(CHECKSUM(NEWID())%10000), o.modify_date)
INTO dbo.DDMDemo
FROM sys.all_objects AS o
ORDER BY NEWID();

这些数据是随机生成的，但是有一定的格式在里面，可以直接select * from DDMDemo来查看，结果如图：

然后我们进行p屏蔽操作：

-- 只显示FisrtName的第一个字符
ALTER TABLE dbo.DDMDemo ALTER COLUMN FirstName 
    ADD MASKED WITH (FUNCTION = 'partial(1, "XXXXX", 0)');
    
--显示lastname的前两个字符和后一个字符
ALTER TABLE dbo.DDMDemo ALTER COLUMN LastName  
    ADD MASKED WITH (FUNCTION = 'partial(2, "XXXXXXXX", 1)');
    
--所有email地址均显示为：nXXX@XXXX.com
ALTER TABLE dbo.DDMDemo ALTER COLUMN Email     
    ADD MASKED WITH (FUNCTION = 'email()');

-- SSNs 显示为 nXX-XX-XXXn
ALTER TABLE dbo.DDMDemo ALTER COLUMN SSN       
    ADD MASKED WITH (FUNCTION = 'partial(1,"XX-XX-XXX",1)');

--BirthDate为1900-01-01 
ALTER TABLE dbo.DDMDemo ALTER COLUMN BirthDate 
    ADD MASKED WITH (FUNCTION = 'default()');

再次以sa执行，结果同上，没有变化，然后创建一个新登录：

CREATE USER DDMUser WITHOUT LOGIN;
GRANT SELECT ON dbo.DDMDemo TO DDMUser;

EXECUTE AS USER = N'DDMUser';
GO
SELECT * FROM dbo.DDMDemo;
GO
--回收权限
REVERT;

结果如下图，是我们想要的结果

DDM介绍

演示完之后，该说点技术细节了。DDM和普通的加密不一样，它不修改数据本身，而是在展示时进行掩盖部分信息。首先建议使用CTP 2.1以上的版本（或者直接使用SQL Server 2019），因为2016 CTP的DDM中存在旁路攻击可以绕过这种屏蔽。

DDM是基于列的屏蔽，支持4类屏蔽类型：

1. 默认：default()，根据数据类型而定，字符串为XXXX，数字为0，日期时间类型为1900-01-01，二进制为0。使用MASKED WITH (FUNCTION = ‘default()’) 。
2. 电子邮件：屏蔽电子邮件的第二个字符到@之间，以及@到.com之间的常量。使用MASKED WITH (FUNCTION = ‘email()’)。
3. 随机：random()，适用于任何数值型，可以指定随机范围，使用MASKED WITH (FUNCTION = ‘random([start range], [end range])’)。
4. 自定义字符串：partial()，公开最前和最后的一个字符，中间使用prefix,[padding],suffix来屏蔽，但是如果字符串原始值太短，则不会显示部分前缀或后缀。MASKED WITH (FUNCTION = ‘partial(prefix,[padding],suffix)’) NULL。

另外要注意一些不常用的数据类型是不能进行屏蔽的：

(var)binary / image、xml、sql_variant、hierarchyid、uniqueidentifier、rowversion (timestamp)、spatial types

修改/移除屏蔽

在建表的时候可以直接指定屏蔽的规则，但是在使用过程中很有可能发生一些变更，这就需要修改甚至移除屏蔽功能，使用ALTER COLUMN命令即可：

--查看原有数据
EXECUTE AS USER = N'DDMUser';
GO
SELECT * FROM dbo.DDMDemo;
GO
REVERT
GO
--修改SSN的掩码规则
ALTER TABLE dbo.DDMDemo ALTER COLUMN SSN  ADD MASKED WITH (FUNCTION = 'partial(2,"X-XX-XXX",1)');
GO
--查看修改后数据
EXECUTE AS USER = N'DDMUser';
GO
SELECT * FROM dbo.DDMDemo;
GO
REVERT

GO

可以看到屏蔽的数据从显示第一个字符变成了显示前两个字符。

对于删除操作，使用DROP MASKED即可，如下：

--查看原有数据
EXECUTE AS USER = N'DDMUser';
GO
SELECT * FROM dbo.DDMDemo;
GO
REVERT
GO
ALTER TABLE dbo.DDMDemo ALTER COLUMN SSN DROP MASKED
GO
--查看修改后数据
EXECUTE AS USER = N'DDMUser';
GO
SELECT * FROM dbo.DDMDemo;
GO
REVERT

下图表明已实现想要的效果。

在现今数据隐私越来越受重视的时代 ，DDM应运而生，其操作简便及对应用修改很少的特性，在新系统中值得评估使用。当然，旧系统中只要支持，也建议在合适的地方使用，毕竟性价比很高。