sql server中的DDM动态数据屏蔽
原创
sqlserver从2016开始，具备了动态数据屏蔽（也可以叫动态数据掩码DDM全称dynamic data masking）的功能。
动态数据屏蔽 (DDM) 通过对非特权用户屏蔽敏感数据来限制敏感数据的公开。 它可以用于显著简化应用程序中安全性的设计和编码。
官方文档：https://learn.microsoft.com/zh-cn/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16
动态数据屏蔽概述
动态数据掩码允许用户在尽量减少对应用程序层的影响的情况下，指定需要披露的敏感数据量，从而防止对敏感数据的非授权访问。 可以在指定的数据库字段上配置 DDM，在查询结果集中隐藏敏感数据。 使用 DDM 时，数据库中的数据不会更改。 对于现有应用程序而言 DDM 非常易用，因为查询结果中应用了屏蔽规则。 许多应用程序可以屏蔽敏感数据，而无需修改现有查询。
l 一个中央数据掩码策略直接对数据库中的敏感字段起作用。
l 指定有权访问敏感数据的特权用户或角色。
l DDM 采用完全掩码和部分掩码功能，以及用于数值数据的随机掩码。
l 简单的 Transact-SQL 命令定义和管理掩码。
动态数据掩码旨在限制敏感数据的公开，防止没有访问权限的用户查看敏感数据。 动态数据掩码并不是要防止数据库用户直接连接到数据库并运行可以公开敏感数据的详尽查询。 动态数据掩码是对其他 SQL Server 安全功能（审核、加密、行级别安全性等）的补充，强烈建议将此功能与上述功能一起使用，以便更好地保护数据库中的敏感数据。
动态数据掩码在 SQL Server 2016 (13.x) 和 Azure SQL 数据库中提供，使用 Transact-SQL 命令进行配置。
权限
具有表的 SELECT 权限的用户可以查看表数据。 列在被定义为“已屏蔽”后，会显示屏蔽后的数据。 授予用户 UNMASK 权限，以允许其从定义了屏蔽的列中检索未屏蔽数据。
管理用户和角色始终可以通过 CONTROL 权限（其包括 ALTER ANY MASK 和 UNMASK 权限）查看未屏蔽的数据。 管理用户或角色（例如 sysadmin 或 db_owner）按设计具有数据库的 CONTROL 权限，并可查看未屏蔽数据。
无需任何特殊权限即可使用动态数据掩码来创建表，只需标准的 CREATE TABLE 权限以及对架构的 ALTER 权限。
添加、替换或删除对列的屏蔽，需要 ALTER ANY MASK 权限以及对表的 ALTER 权限。 可以将 ALTER ANY MASK 权限授予安全管理人员。
UNMASK 权限不会影响元数据可见性：单纯授予 UNMASK 并不会泄露任何元数据。 UNMASK 将始终需要伴有 SELECT 权限才能有效果。 示例：在数据库范围内授予 UNMASK 并授予单个表的 SELECT，将导致用户只能看到单个表（可从中选择）的元数据，而看不到其他任何元数据。
最佳实践和常规用例
对列进行掩码不会阻止对该列进行更新。 因此，即使用户在查询被屏蔽的列时收到的是被屏蔽的数据，该用户也可以更新这些数据，前提是具有写入权限。 仍需使用适当的访问控制策略来限制更新权限。
使用 SELECT INTO 或 INSERT INTO 将数据从经过屏蔽的列复制到另一表中会导致目标表中显示屏蔽的数据（假定该表是由没有 UNMASK 特权的用户导出的）。
运行 SQL Server 导入和导出时，将应用动态数据屏蔽。 数据库包含已掩码的列将导致导出的数据文件也包含已掩码的数据（假定该文件是由没有 UNMASK 特权的用户导出的），并且导入的数据库将包含已静态掩码的数据。
查询掩码列
使用 sys.masked_columns 视图可查询对其应用了屏蔽函数的表列。 该视图继承自 sys.columns 视图。 该视图会返回 sys.columns 视图中的所有列，以及 is_masked 和 masking_function 列，表明该列是否被屏蔽，以及在该列被屏蔽的情况下定义了什么屏蔽函数。 该视图仅显示在其上应用了掩码函数的列。
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;限制和局限
数据库级别具有 CONTROL SERVER 或 CONTROL 权限的用户可以查看原始形式的掩码数据。 这些用户包括管理员用户或角色，例如 sysadmin、db_owner 等。
若在索引视图中引用基础基表，则不支持动态数据掩码。
示例
从 SQL Server 2022 (16.x) 开始，可通过在数据库的不同级别向未经授权的用户屏蔽敏感数据，来防止对敏感数据进行未经授权的访问并获得控制权。 可以在数据库级别、架构级别、表级别或列级别向用户、数据库角色、Microsoft Entra 标识或 Microsoft Entra 组授予或撤销 UNMASK 权限。 这一增强使得可更精细地控制和限制对数据库中存储的数据进行的未经授权访问，并改进数据安全管理。
创建动态数据掩码
以下示例创建的表使用三种不同类型的动态数据屏蔽。 该示例会对表进行填充，在执行选择操作后即可显示结果。
-- schema to contain user tables
CREATE SCHEMA Data;
GO
-- table with masked columns
CREATE TABLE Data.Membership (
MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
LastName VARCHAR(100) NOT NULL,
Phone VARCHAR(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
DiscountCode SMALLINT MASKED WITH (FUNCTION = 'random(1, 100)') NULL
);
-- inserting sample data
INSERT INTO Data.Membership (FirstName, LastName, Phone, Email, DiscountCode)
VALUES
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10),
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5),
('Shakti', 'Menon', '555.123.4570', 'SMenon@contoso.net', 50),
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net', 40);
GO创建了一个新用户，并向其授予了对表驻留的架构的 SELECT 权限。 执行查询后， MaskingTestUser 看到的是经过屏蔽的数据。
CREATE USER MaskingTestUser WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::Data TO MaskingTestUser;
-- impersonate for testing:
EXECUTE AS USER = 'MaskingTestUser';
SELECT * FROM Data.Membership;
REVERT;结果表明对数据进行了屏蔽，即数据已从：
1 Roberto Tamburello 555.123.4567 RTamburello@contoso.com 10
更改为：
1 Rxxxxxo Tamburello xxxx RXXX@XXXX.com 91
其中，DiscountCode 中的数字是每个查询结果的随机数字。
对现有列添加或编辑掩码
使用 ALTER TABLE 语句可以添加对表中现有列的屏蔽，或者对该列的屏蔽进行编辑。
以下示例向 LastName 列添加了一个屏蔽函数：
ALTER TABLE Data.Membership
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');
以下示例更改了 LastName 列的屏蔽函数：
ALTER TABLE Data.Membership
ALTER COLUMN LastName VARCHAR(100) MASKED WITH (FUNCTION = 'default()');授予查看未掩码数据的权限
授予 UNMASK 权限即可让 MaskingTestUser 查看未经屏蔽的数据。
GRANT UNMASK TO MaskingTestUser;
EXECUTE AS USER = 'MaskingTestUser';
SELECT * FROM Data.Membership;
REVERT;
-- Removing the UNMASK permission
REVOKE UNMASK TO MaskingTestUser;删除动态数据掩码
以下语句将删除上述示例中创建的针对 LastName 列的屏蔽：
ALTER TABLE Data.Membership
ALTER COLUMN LastName DROP MASKED;粒度权限示例
创建架构以包含用户表：
CREATE SCHEMA Data;
GO
创建具有掩码列的表：
CREATE TABLE Data.Membership (
MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
LastName VARCHAR(100) NOT NULL,
Phone VARCHAR(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
DiscountCode SMALLINT MASKED WITH (FUNCTION = 'random(1, 100)') NULL,
BirthDay DATETIME MASKED WITH (FUNCTION = 'default()') NULL
);
插入示例数据：
INSERT INTO Data.Membership (FirstName, LastName, Phone, Email, DiscountCode, BirthDay)
VALUES
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10, '1985-01-25 03:25:05'),
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5, '1990-05-14 11:30:00'),
('Shakti', 'Menon', '555.123.4570', 'SMenon@contoso.net', 50, '2004-02-29 14:20:10'),
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net', 40, '1990-03-01 06:00:00');
创建架构以包含服务表：
CREATE SCHEMA Service;
GO
创建具有掩码列的服务表：
CREATE TABLE Service.Feedback (
MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
Feedback VARCHAR(100) MASKED WITH (FUNCTION = 'default()') NULL,
Rating INT MASKED WITH (FUNCTION = 'default()'),
Received_On DATETIME
);
插入示例数据：
INSERT INTO Service.Feedback(Feedback, Rating, Received_On)
VALUES
('Good', 4, '2022-01-25 11:25:05'),
('Excellent', 5, '2021-12-22 08:10:07'),
('Average', 3, '2021-09-15 09:00:00');
在数据库中创建不同用户：
CREATE USER ServiceAttendant WITHOUT LOGIN;
GO
CREATE USER ServiceLead WITHOUT LOGIN;
GO
CREATE USER ServiceManager WITHOUT LOGIN;
GO
CREATE USER ServiceHead WITHOUT LOGIN;
GO
向数据库中的用户授予读取权限：
ALTER ROLE db_datareader ADD MEMBER ServiceAttendant;
ALTER ROLE db_datareader ADD MEMBER ServiceLead;
ALTER ROLE db_datareader ADD MEMBER ServiceManager;
ALTER ROLE db_datareader ADD MEMBER ServiceHead;
向用户授予不同的 UNMASK 权限：
--Grant column level UNMASK permission to ServiceAttendant
GRANT UNMASK ON Data.Membership(FirstName) TO ServiceAttendant;
-- Grant table level UNMASK permission to ServiceLead
GRANT UNMASK ON Data.Membership TO ServiceLead;
-- Grant schema level UNMASK permission to ServiceManager
GRANT UNMASK ON SCHEMA::Data TO ServiceManager;
GRANT UNMASK ON SCHEMA::Service TO ServiceManager;
--Grant database level UNMASK permission to ServiceHead;
GRANT UNMASK TO ServiceHead;
在用户 ServiceAttendant 的上下文中查询数据：
EXECUTE AS USER = 'ServiceAttendant';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
在用户 ServiceLead 的上下文中查询数据：
EXECUTE AS USER = 'ServiceLead';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
在用户 ServiceManager 的上下文中查询数据：
EXECUTE AS USER = 'ServiceManager';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
在用户 ServiceHead 的上下文下查询数据
EXECUTE AS USER = 'ServiceHead';
SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay
FROM Data.Membership;
SELECT MemberID, Feedback, Rating
FROM Service.Feedback;
REVERT;
若要撤销 UNMASK 权限，请使用以下 T-SQL 语句：
REVOKE UNMASK ON Data.Membership(FirstName) FROM ServiceAttendant;
REVOKE UNMASK ON Data.Membership FROM ServiceLead;
REVOKE UNMASK ON SCHEMA::Data FROM ServiceManager;
REVOKE UNMASK ON SCHEMA::Service FROM ServiceManager;
REVOKE UNMASK FROM ServiceHead;原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
