使用SQL Server 2016屏蔽敏感数据
4.19/5 (13投票s)
如何使用动态数据屏蔽从外部世界屏蔽SQL Server 2016中的敏感数据
引言
截至撰写本文时，最新、最安全、功能最强大的 SQL Server 已发布两个多月。它具有许多功能，可用于使您的数据和应用程序更安全、更易于维护，并能根据您的需求轻松检索。它引入了诸如 Always Encrypted 等客户端安全功能以及数据掩码等服务器端安全功能。在本文中，我们将探讨动态数据掩码如何帮助保护我们的数据。
以前，我们会通过在应用程序端应用逻辑或在 SQL Server 端替换字符，然后将掩码后的数据返回给应用程序来掩码敏感数据。在前一种情况下，风险在于
- 应用程序未能掩码数据时
- 通过网络传输的敏感数据
- SQL Server 中所有具有
SELECT权限的用户仍然可以访问未掩码的数据
在后一种情况下，前两种风险被消除，但 SQL Server 用户仍有可能通过其检索数据的应用程序泄露敏感数据。
应用程序未能掩码数据时通过网络传输的敏感数据- SQL Server 中所有具有
SELECT权限的用户仍然可以访问未掩码的数据
因此，让我们快速进入下一节，了解动态数据掩码是什么以及它如何帮助消除所有这些未掩码数据泄露的可能性。
数据掩码
如果您在网上搜索数据掩码的定义，您会看到各种描述。最简单直接的定义是
“数据掩码是使用随机字符或数据隐藏原始数据的过程。”
--维基百科
如果我们进一步研究，会发现有各种可用的数据掩码技术，例如
- 洗牌 (Shuffling)：打乱值的字符顺序。
示例：12345 -----> 35312 - 空值 (Nulling)：用空值 (hash) 符号替换值中的字符。
示例：12345 -----> ###45 - 替换 (Substitution)：使用替换表中的另一个值替换原始值。
示例：Suvendu Giri -----> John Ptak - 掩码 (Masking Out)：掩码数据的一个完整部分或一个选择性部分。
示例：suvendu@mydomain.com -----> suvendu@xxxx.xxx
等等。
考虑到数据掩码的多种技术，它们可以分为三种类型
- 静态数据掩码 (Static Data Masking)：生产环境中的原始数据被更改为掩码后的数据
- 即时数据掩码 (On-the-fly Data masking)：将数据从一个源复制到另一个源，并在后者上进行掩码
- 动态数据掩码 (Dynamic data masking)：在运行时动态进行掩码
动态数据屏蔽
“动态数据掩码通过向非特权用户掩码敏感数据来限制敏感数据的暴露。动态数据掩码通过允许客户指定敏感数据的披露程度，同时对应用程序层的影响最小，从而帮助防止对敏感数据的未经授权访问。它是一项数据保护功能，可在查询指定数据库字段的结果集中隐藏敏感数据，但数据库中的数据不会被更改。”
--- MSDN
因此，动态数据掩码在流式传输时会改变非特权用户的查询结果，而不会改变生产数据库中的数据。
MySQL 中的数据掩码
据我所知，直到最近的 MySQL 版本，还没有内置的机制提供动态数据掩码功能。MySQL 用户可能的选择是
- 使用第三方工具
- 使用自定义脚本/函数
示例
SELECT '****' AS PhoneNumber
FROM `Employees`
--Input: 1234567890 Output: ****
SELECT REPEAT('*', CHAR_LENGTH(PhoneNumber) - 6) AS PhoneNumber
FROM `Employees`
--Input: 1234567890 Output: **********
SELECT CONCAT(SUBSTR(PhoneNumber, 1, 4), _
REPEAT('*', CHAR_LENGTH(PhoneNumber) - 4)) AS PhoneNumber
FROM `Employees`
--Input: 1234567890 Output: 1234******
PostgreSQL 中的数据掩码
与 MySQL 相同。它们似乎仍然缺乏此功能。但是，我们可以利用一些自定义脚本或用户定义函数。
Azure SQL Database 也支持与 SQL Server 类似的动态数据掩码，而且我听说我的朋友们表示 Oracle 提供某种形式的数据掩码机制，尽管我从未用过它。
SQL Server 2016 中的动态数据屏蔽
SQL Server 2016 中提供了四种掩码函数，用于在查询返回数据时以不同方式掩码数据。它们是
默认值
当您希望掩码所有字符时，可以使用默认掩码函数。掩码字符和查询返回的掩码字符数由以下逻辑确定
- XXXX – 如果数据长度大于或等于四，则为四个 X
- 对于数值数据类型，为 0
- 对于二进制数据类型，为 0
- 对于 datetime 数据类型，为 01/01/1900
语法
MASKED WITH (FUNCTION = 'default()')
创建表
CREATE TABLE [dbo].[Employee](
[SecretCode] VARCHAR(10) MASKED WITH (FUNCTION = 'default()'),
-- other fields
修改列
ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SecretCode] ADD MASKED WITH (FUNCTION = 'default()');
示例
让我们使用 Table 变量来检查这些功能。
DECLARE @Employees AS TABLE
(
SecretCode VARCHAR(10) MASKED WITH (FUNCTION = 'default()'),
NetSalary MONEY MASKED WITH (FUNCTION = 'default()'),
DateOfBirth DATETIME MASKED WITH (FUNCTION = 'default()')
)
INSERT INTO @Employees
SELECT 'ABCDEFGHIJ', 80000,'1990-05-21'
SELECT * FROM @Employees
结果
SecretCode NetSalary DateOfBirth
xxxx 0.00 1900-01-01 00:00:00.000
注意：您需要使用非管理员用户进行检查，因为管理员用户被授予查看所有数据的权限。因此，如果您使用管理员用户执行此操作，则不会看到任何区别。假设您的非管理员用户是‘demouser’，则在查询的顶部添加以下行。
EXECUTE AS USER='DemoUser'
电子邮件
它会部分掩码电子邮件 ID 的字符。值得注意的是，它掩码了电子邮件 ID 的字符以及长度，使得无法从显示的少数字符中预测出电子邮件 ID。
语法
MASKED WITH (FUNCTION = 'email()')
示例
让我们修改同一个示例，如下添加一个电子邮件列
DECLARE @Employees AS TABLE
(
SecretCode VARCHAR(10) MASKED WITH (FUNCTION = 'default()'),
NetSalary MONEY MASKED WITH (FUNCTION = 'default()'),
DateOfBirth DATETIME MASKED WITH (FUNCTION = 'default()'),
EmailID VARCHAR(50) MASKED WITH (FUNCTION = 'email()')
)
INSERT INTO @Employees
SELECT 'ABCDEFGHIJ', 80000,'1990-05-21', 'suvendugiri@mydomain.in'
SELECT * FROM @Employees
结果
SecretCode NetSalary DateOfBirth EmailID
xxxx 0.00 1900-01-01 00:00:00.000 sXXX@XXXX.com
部分
它根据传递给掩码函数的自定义输入来掩码数据。它会部分掩码数据。需要将三个参数传递给此掩码函数，例如
Prefix：要从开头显示的字符数Padding：您需要在前缀和后缀之间显示的字符Suffix：要从结尾显示的字符数
语法
MASKED WITH (FUNCTION = 'partial(prefix,padding,suffix)')
示例
进一步在同一示例中添加一个新列，如下所示
DECLARE @Employees AS TABLE
(
SecretCode VARCHAR(10) MASKED WITH (FUNCTION = 'default()'),
NetSalary MONEY MASKED WITH (FUNCTION = 'default()'),
DateOfBirth DATETIME MASKED WITH (FUNCTION = 'default()'),
EmailID VARCHAR(50) MASKED WITH (FUNCTION = 'email()'),
FullName VARCHAR(50) MASKED WITH (FUNCTION = 'partial(2,"***",2)')
)
INSERT INTO @Employees
SELECT 'ABCDEFGHIJ', 80000,'1990-05-21', 'suvendugiri@mydomain.in','Suvendu Shekhar Giri'
SELECT * FROM @Employees
结果
SecretCode NetSalary DateOfBirth EmailID FullName
xxxx 0.00 1900-01-01 00:00:00.000 sXXX@XXXX.com Su***ri
随机
根据提供的上限和下限参数，使用随机值掩码任何数值。
语法
MASKED WITH (FUNCTION = ‘random(lower_bound,upper_bound)
示例
DECLARE @Employees AS TABLE
(
SecretCode VARCHAR(10) MASKED WITH (FUNCTION = 'default()'),
NetSalary MONEY MASKED WITH (FUNCTION = 'default()'),
DateOfBirth DATETIME MASKED WITH (FUNCTION = 'default()'),
EmailID VARCHAR(50) MASKED WITH (FUNCTION = 'email()'),
FullName VARCHAR(50) MASKED WITH (FUNCTION = 'partial(2,"***",2)'),
Age INT MASKED WITH (FUNCTION = 'random(90,100)')
)
INSERT INTO @Employees
SELECT 'ABCDEFGHIJ', 80000,'1990-05-21', 'suvendugiri@mydomain.in','Suvendu Shekhar Giri',32
SELECT * FROM @Employees
结果
SecretCode NetSalary DateOfBirth EmailID FullName Age
xxxx 0.00 1900-01-01 00:00:00.000 sXXX@XXXX.com Su***ri 93
如果您希望用户像管理员一样查看数据库中存储的值，则可以使用以下命令授予用户访问权限
GRANT UNMASK TO DemoUser
您可以通过以下命令重新应用掩码数据的限制
REVOKE UNMASK TO DemoUser
要从列中删除掩码函数，您可以像删除其他约束一样删除它，例如
ALTER TABLE Employees
ALTER COLUMN FullName DROP MASKED
结论
SQL Server 2016 中的动态数据掩码确实是一项非常有前途的功能，它将有助于避免编写大量自定义脚本来掩码敏感数据。基于角色/访问权限的视图数据非常有意义且非常有用。目前，用户访问权限可以授予到数据库。如果有一种基于表或模式授予此访问权限的机制，那就太好了。
请在下方的评论区留下您的反馈、评论或建议。
感谢阅读。：)
历史
- 2016 年 8 月 12 日：首次发布