数据库安全之数据掩码：SQL新功能之动态数据掩码（SQL2016&AZURE SQL）



我们在开发上都有这样的需求：对部分敏感数据进行屏蔽，如身份证信息，名字的一部分等

显示如：

 

在开发上，一般来是取出相应的文字后进行替换，如可以用下面的方法。

 

function plusXing (str,StartLen,endLen) {

    var len = str.length-StartLen -endLen;

    var xing = '';

    for (vari=0;i<len;i++) {

       xing+='*';

    }

    return str.substr(0,StartLen)+xing+str.substr(str.length-endLen);

}

如输入 plusXing(“18023456789”,3,4)

返回结果：180****6789

但是这样的方法存在安全隐患，因为读取出来的源数据是明文。因此在SQL2016和Azure SQL中有了一个数据掩码（Azure SQL翻译为动态数据掩码，Technet上面翻译为动态数据屏蔽）的新功能帮助解决这样的问题。

下面我们来看看能实现什么好玩的功能：

先在本地做个测试：

创建一张表

create TABLE Membership 

  (MemberID int IDENTITY PRIMARY KEY, 

   FirstName varchar(100) MASKED WITH (FUNCTION = 'default()') NULL,   ----启用默认掩码

   LastName varchar(100) NOT NULL, 

   Phone# varchar(13) MASKED WITH   (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  ---启动动态屏蔽，从第一个字符开始使用XXXXX替换，最后不显示

   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);   ----启用email掩码

   插入数据：

  INSERT Membership (FirstName, LastName, Phone#, Email) VALUES  

('张', '三丰', '13980040000', 'zhangsanfeng@contoso.com'), 

('王', '一箭', '13698000000', 'wangyijian@contoso.com.co'), 

('独孤', '九剑', '13599999999', 'dugujiujian@contoso.net'); 

SELECT * FROM Membership; 

分配一个testuser用户赋予查询权限

CREATE USER TestUser WITHOUT LOGIN; 

GRANT SELECT ON Membership TO TestUser; 

使用testuser身份进行查询

EXECUTE AS USER = 'TestUser'; 

SELECT * FROM Membership; 

REVERT; 

结果如下：

 

更改掩码

ALTER TABLE Membership 

ALTER COLUMN Phone# varchar(13) MASKED WITH (FUNCTION = 'partial(3,"****",4)');  ---更改为从第三位开始，使用*来做掩码，最后显示4位

 

ALTER TABLE Membership 

ALTER COLUMN Phone# varchar(13) MASKED WITH (FUNCTION = 'partial(3,"****",5)');  ---更改为从第三位开始，使用*来做掩码，，最后显示5位

 

 

授权查看未经屏蔽数据的权限

授予 UNMASK 权限即可让 TestUser 查看未经屏蔽的数据

GRANT UNMASK TO TestUser; 

EXECUTE AS USER = 'TestUser'; 

SELECT * FROM Membership; 

REVERT;  

 

 

删除未屏蔽权限

-- Removingthe UNMASK permission 

REVOKE UNMASK TO TestUser; 

 

 

删除动态数据屏蔽

以下语句将删除上述示例中创建的针对 FirstName列的屏蔽：

ALTER TABLE Membership  

ALTER COLUMN FirstName DROP MASKED; 

 

 

查询屏蔽列

通过以下语句可以查询到那些列使用了屏蔽

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function 

FROM sys.masked_columnsAS c 

JOIN sys.tables AS tbl  

    ON c.[object_id] = tbl.[object_id] 

WHERE is_masked = 1; 

 

以上是在SQL 2016上完成的，再来看看azure SQL上怎么玩

第一种方法也使用上面的代码，用SQLmanagement studio 连接azureSQL 如图

 

 

一样执行之前的代码，看看结果

create TABLE Membership 

  (MemberID int IDENTITY PRIMARY KEY, 

   FirstName varchar(100) MASKED WITH (FUNCTION = 'default()') NULL,   ----启用默认掩码

   LastName varchar(100) NOT NULL, 

   Phone# varchar(13) MASKED WITH   (FUNCTION = 'partial(3,"****",4)') NULL,  ---启动动态屏蔽，

   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);   ----启用email掩码

  

    INSERT Membership (FirstName, LastName, Phone#, Email) VALUES  

('张', '三丰', '13980040000', 'zhangsanfeng@contoso.com'), 

('王', '一箭', '13698000000', 'wangyijian@contoso.com.co'), 

('独孤', '九剑', '13599999999', 'dugujiujian@contoso.net'); 

SELECT * FROM Membership; 

 

CREATE USER TestUser WITHOUT LOGIN; 

GRANT SELECT ON MembershipTO TestUser; 

 

EXECUTE AS USER = 'TestUser'; 

SELECT * FROM Membership; 

REVERT; 

 

 

 

查看掩码情况

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function 

FROM sys.masked_columnsAS c 

JOIN sys.tables AS tbl  

    ON c.[object_id] = tbl.[object_id] 

WHERE is_masked = 1

 

第二种方法，在ARM 界面下，选择Azure SQL，数据的选项里面就有动态数据掩码选项。

 

点击后

 

已经展示了使用个掩码的情况，也可以在此界面进行添加，更加简单。

 

 

 

 

也可以在这个界面进行修改

 

 

 

SQL 数据库动态数据掩码通过对非特权用户模糊化敏感数据来限制此类数据的泄露。

•不对其进行屏蔽的 SQL 用户 - 一组可以在 SQL 查询结果中获取非屏蔽数据的 SQL 用户或 AAD 标识。 请注意，始终不会对拥有管理员权限的用户进行屏蔽，这些用户可以查看没有任何屏蔽的原始数据。

•屏蔽规则 - 一组规则，定义将要屏蔽的指定字段，以及要使用的屏蔽函数。 可以使用数据库架构名称、表名称和列名称定义指定的字段。

•屏蔽函数 - 一组方法，用于控制不同情况下的数据透露。

 

 

函数	说明	示例
默认	根据指定字段的数据类型进行完全屏蔽。 对于字符串数据类型，可以使用 XXXX 或者在字段不到 4 个字符长的情况下使用更少的 X（char、nchar、varchar、nvarchar、text、ntext）。 对于数字数据类型，可使用零值（bigint****bit****decimal、int、money、numeric、smallint、smallmoney、tinyint、float、real）。 对于日期和时间数据类型，可使用 01.01.1900 00:00:00.0000000（date、datetime2、datetime、datetimeoffset、smalldatetime、time）。 对于二进制数据类型，可使用单字节的 ASCII 值 0（binary、varbinary、image）。	列定义语法示例： Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL ALTER 语法示例： ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')
电子邮件	该屏蔽方法公开电子邮件地址的第一个字母，以及电子邮件地址格式中的常量后缀“.com”。 。 aXXX@XXXX.com。	定义语法示例： Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL ALTER 语法示例：ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'）
随机	一种随机屏蔽函数，适用于任何数字类型，可以在指定范围内使用随机值来屏蔽原始值。	定义语法示例： Account_Number bigint MASKED WITH (FUNCTION = 'random([start range], [end range])') ALTER 语法示例： ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')
自定义字符串	该屏蔽方法公开第一个和最后一个字母，在中间添加自定义填充字符串。 prefix,[padding],suffix 注意：如果因原始值太短而无法进行完整的屏蔽，则不会公开部分前缀或后缀。	定义语法示例： FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)') NULL ALTER 语法示例： ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') 其他示例：  ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)')  ALTER COLUMN [Social Security Number] ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')

 

Permissions

不需任何特殊权限即可使用动态数据屏蔽来创建表，只需标准的 CREATE TABLE 权限以及对架构的 ALTER 权限。

添加、替换或删除对列的屏蔽，需要 ALTER ANY MASK 权限以及对表的 ALTER 权限。 可以将 ALTER ANY MASK 权限授予安全负责人。

具有表的 SELECT 权限的用户可以查看表数据。列在被定义为“已屏蔽”后，将显示屏蔽后的数据。 对于需要从定义了屏蔽的列中检索非屏蔽数据的用户，可授予其 UNMASK 权限。

针对数据库的 CONTROL 权限包括 ALTER ANYMASK 和 UNMASK 权限。

  

安全说明：可使用推断或暴力技术绕过屏蔽

上面的例子中，我可以这样进行暴力推断

也就是说动态掩码是一个障眼法，不能阻止你去窥探数据的本身。上面的例子就是testuser看到数据，但是他可以推测出了姓“张”的数据。

 

总结：

 

动态数据屏蔽 (DDM) 通过对非特权用户屏蔽敏感数据来限制敏感数据的公开。 它可以用于显著简化应用程序中安全性的设计和编码。

动态数据屏蔽允许用户在尽量减少对应用程序层的影响的情况下，指定需要披露的敏感数据，从而防止对敏感数据的非授权访问。 DDM 可以在数据库上进行配置，以隐藏对指定数据库字段进行查询时获得的结果集中的敏感数据，同时不会更改数据库中的数据。 可以轻松地对现有应用程序使用动态数据屏蔽，因为屏蔽规则是应用于查询结果。许多应用程序可以屏蔽敏感数据，而无需修改现有查询。

• 一个中央数据屏蔽策略直接对数据库中的敏感字段起作用。

•  指定有权访问敏感数据的特权用户或角色。

•  DDM 采用完全屏蔽和部分屏蔽功能，以及用于数值数据的随机屏蔽。

•  简单Transact-SQL 命令定义和管理掩码。

   

  例如，呼叫中心支持人员通过身份证号或信用卡号的几个数字就可以辨识呼叫者，但系统不会将这些数据内容完全公开给该支持人员。 可以通过定义屏蔽规则来屏蔽查询结果集中身份证号或信用卡号最后四位数字以外的所有数字。另一个例子就是，在需要进行故障排除时，开发人员可以通过对数据进行适当的数据屏蔽来保护个人身份信息 (PII) 数据，因此可以在不违反遵从性法规的情况下，对生产环境进行查询。

  动态数据屏蔽旨在限制敏感数据的公开，防止没有访问权限的用户查看敏感数据。 动态数据屏蔽并不是要防止数据库用户直接连接到数据库并运行可以公开敏感数据的详尽查询。动态数据屏蔽是对其他 SQL Server 安全功能（审核、加密、行级别安全性…）的补充，因此，强烈建议你将此功能与上述功能一起使用，以便更好地保护数据库中的敏感数据