动态数据屏蔽

项目
04/17/2024

适用于： SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics

图片，其中显示了动态数据掩码。

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

此内容通常适用于动态数据屏蔽概念，且特定于 SQL Server。提供特定于其他平台的内容：

有关 Azure SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics 中的动态数据屏蔽，请参阅SQL 数据库动态数据屏蔽入门。
有关 Microsoft Fabric 中的动态数据屏蔽，请参阅 Fabric 数据仓库中的动态数据屏蔽。

动态数据屏蔽概述

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

一个中央数据掩码策略直接对数据库中的敏感字段起作用。
指定有权访问敏感数据的特权用户或角色。
DDM 采用完全掩码和部分掩码功能，以及用于数值数据的随机掩码。
简单的 Transact-SQL 命令定义和管理掩码。

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

动态数据掩码在 SQL Server 2016 (13.x) 和 Azure SQL 数据库中提供，使用 Transact-SQL 命令进行配置。有关如何使用 Azure 门户来配置动态数据掩码的详细信息，请参阅开始使用 SQL 数据库动态数据掩码（Azure 门户）。

注意

Microsoft Entra ID 以前称为 Azure Active Directory (Azure AD)。

定义动态数据掩码

针对表中的列定义屏蔽规则即可模糊该列中的数据。可以使用五种类型的屏蔽。

函数	说明	示例
默认值	根据指定字段的数据类型进行完全屏蔽。对于字符串数据类型，可使用 `XXXX`（或更少，如果字段大小小于 4 个字符）（char、nchar、varchar、nvarchar、text、ntext）。对于数字数据类型，可使用零值（bigintbitdecimal、 int、 money、 numeric、 smallint、 smallmoney、 tinyint、 float、 real）。对于日期和时间数据类型，可使用 `1900-01-01 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()')`
Random	一种随机屏蔽函数，适用于任何数字类型，可以在指定范围内使用随机值来屏蔽原始值。	定义语法示例： `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)')` 这会将电话号码 `555.123.1234` 转换为 `5XXXXXXX`。其他示例： `ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)')` 这会将电话号码 `555.123.1234` 转换为 `555.1XXXXXXX`。
日期/时间	适用于： SQL Server 2022 (16.x) 使用数据类型 datetime、datetime2、date、time、datetimeoffset、smalldatetime 定义的列的掩码方法。它有助于屏蔽一天中的 `year => datetime("Y")`、`month=> datetime("M")`、`day=>datetime("D")`、`hour=>datetime("h")`、`minute=>datetime("m")` 或 `seconds=>datetime("s")` 部分。	如何屏蔽 datetime 值的年份的示例： `ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("Y")')` 如何屏蔽 datetime 值的月份的示例： `ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("M")')` 如何屏蔽 datetime 值的分钟的示例： `ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("m")')`

权限

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

管理用户和角色始终可以通过 CONTROL 权限（其包括 ALTER ANY MASK 和 UNMASK 权限）查看未屏蔽的数据。管理用户或角色（例如 sysadmin、serveradmin 或 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、serveradmin、db_owner 等。

不能针对以下列类型定义掩码规则：

加密列（始终加密）
FILESTREAM
COLUMN_SET 或属于列集一部分的稀疏列。
不能在计算列上配置掩码，但如果计算列依赖于带有掩码的列，则计算列将返回已掩码数据。
具有数据掩码的列不能作为 FULLTEXT 索引的密钥。
PolyBase 外部表中的列。

如果用户没有 UNMASK 权限，则无法在配置了动态数据掩码的列上执行弃用的 READTEXT、UPDATETEXT 和 WRITETEXT 语句。

添加动态数据掩码是作为对基础表的架构更改实现的，因此无法对具有依赖关系的列（例如计算列引用的列）执行。尝试针对具有依赖关系的列添加动态数据掩码会导致错误：ALTER TABLE ALTER COLUMN _columnname_ failed because one or more objects access this column。若要解决此限制，可先删除依赖项，然后添加动态数据屏蔽，再重新创建依赖项。例如，如果依赖项是由于索引依赖于该列，则可以删除索引，然后添加掩码，再重新创建依赖索引。

每当投影一个表达式并且该表达式引用了为其定义了数据掩码函数的列时，该表达式也将被屏蔽。无论使用何种函数（默认值、电子邮件、随机、自定义字符串）屏蔽引用的列，都将始终使用默认函数屏蔽生成的表达式。

跨两个不同的 Azure SQL 数据库或托管在不同 SQL Server 实例上的数据库的跨数据库查询（涉及针对 MASKED 列的任何类型的比较或联接操作）将不会提供正确的结果。从远程服务器返回的结果已采用 MASKED 形式，并且不适合于本地的任何类型的比较或联接操作。

注意

若在索引视图中引用基础基表，则不支持动态数据掩码。

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

动态数据屏蔽旨在通过在应用程序使用的一组预定义查询中限制数据泄露，来简化应用程序开发。虽然动态数据掩码也可以用于在直接访问生产数据库时防止敏感数据的意外泄露，不过请务必注意，具有即席查询权限的非特权用户可以应用技术来获取对实际数据的访问权限。如果需要授予这类即席访问权限，则应使用审核监视所有数据库活动并缓解这种情况。

例如，请考虑一个数据库主体，该主体具有足够权限来对数据库运行即席查询，尝试“猜测”基础数据并最终推断实际值。假设我们对 [Employee].[Salary] 列定义了一个掩码，此用户直接连接到数据库并开始猜测值，从而最终推断 Employees 表中的 [Salary] 值：

SELECT ID, Name, Salary FROM Employees
WHERE Salary > 99999 and Salary < 100001;

Id 名称薪金

62543 Jane Doe 0

91245 John Smith 0

Id	名称	薪金
62543	Jane Doe	0
91245	John Smith	0

这表明不应单独使用动态数据屏蔽来完全保护敏感数据免受用户对数据库运行的临时查询。这适用于防止意外的敏感数据泄露，但无法防范推断基础数据的恶意企图。

请务必正确管理针对数据库的权限，并且始终遵循最小必需权限原则。此外，请记住启用审核以跟踪对数据库进行的所有活动。

SQL Server 2022 中引入的粒度权限

从 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;

动态数据屏蔽

动态数据屏蔽概述

定义动态数据掩码

权限

最佳实践和常规用例

查询掩码列

限制和局限

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

SQL Server 2022 中引入的粒度权限

示例

创建动态数据掩码

对现有列添加或编辑掩码

授予查看未掩码数据的权限

删除动态数据掩码

粒度权限示例

反馈

反馈

其他资源

动态数据屏蔽

动态数据屏蔽概述

定义动态数据掩码

权限

最佳实践和常规用例

查询掩码列

限制和局限

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

SQL Server 2022 中引入的粒度权限

示例

创建动态数据掩码

对现有列添加或编辑掩码

授予查看未掩码数据的权限

删除动态数据掩码

粒度权限示例

相关内容

反馈

反馈

其他资源