Azure SQL 数据库和 Azure Synapse Analytics 的动态数据掩码Dynamic data masking for Azure SQL Database and Azure Synapse Analytics

SQL 数据库动态数据掩码通过对非特权用户模糊化敏感数据来限制此类数据的泄露。SQL Database dynamic data masking limits sensitive data exposure by masking it to non-privileged users.

动态数据掩码允许客户指定在对应用层产生最小影响的前提下可以透露的敏感数据量,从而帮助防止未经授权的用户访问敏感数据。Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. 它是一种基于策略的安全功能,会在针对指定的数据库字段运行查询后返回的结果集中隐藏敏感数据,同时保持数据库中的数据不变。It's a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

例如,呼叫中心服务代表可以根据呼叫者的信用卡号的多个数字来识别其身份,但这些数据项不应完全透露给服务代表。For example, a service representative at a call center may identify callers by several digits of their credit card number, but those data items should not be fully exposed to the service representative. 可以定义掩码规则,对任意查询的结果集中任何信用卡号除最后四位数以外的其他所有数字进行掩码。A masking rule can be defined that masks all but the last four digits of any credit card number in the result set of any query. 另举一例,通过定义适当的数据掩码来保护个人身份信息 (PII) 数据,开发人员一方面可以查询生产环境以进行故障排除,同时又不违反法规遵从性要求。As another example, an appropriate data mask can be defined to protect personally identifiable information (PII) data, so that a developer can query production environments for troubleshooting purposes without violating compliance regulations.

动态数据掩码基础知识Dynamic data masking basics

通过在 SQL 数据库配置边栏选项卡或设置边栏选项卡中选择“动态数据掩码”操作,在 Azure 门户中设置动态数据掩码策略。You set up a dynamic data masking policy in the Azure portal by selecting the dynamic data masking operation in your SQL Database configuration blade or settings blade. 不能使用门户为 Azure Synapse 设置此功能(请使用 Powershell 或 REST API)This feature cannot be set by using portal for Azure Synapse (Please use Powershell or REST API)

动态数据掩码权限Dynamic data masking permissions

Azure SQL 数据库管理员、服务器管理员或 SQL 安全管理员角色可以配置动态数据掩码。Dynamic data masking can be configured by the Azure SQL Database admin, server admin, or SQL Security Manager roles.

动态数据掩码策略Dynamic data masking policy

  • 不对其进行掩码的 SQL 用户 - 一组可以在 SQL 查询结果中获取非掩码数据的 SQL 用户或 AAD 标识。SQL users excluded from masking - A set of SQL users or AAD identities that get unmasked data in the SQL query results. 始终不会对拥有管理员权限的用户进行掩码,这些用户可以看到没有任何掩码的原始数据。Users with administrator privileges are always excluded from masking, and see the original data without any mask.
  • 掩码规则 - 一组规则,定义要掩码的指定字段,以及使用的掩码函数。Masking rules - A set of rules that define the designated fields to be masked and the masking function that is used. 可以使用数据库架构名称、表名称和列名称定义指定的字段。The designated fields can be defined using a database schema name, table name, and column name.
  • 掩码函数 - 一组方法,用于控制不同情况下的数据透露。Masking functions - A set of methods that control the exposure of data for different scenarios.
掩码函数Masking Function 掩码逻辑Masking Logic
默认Default 根据指定字段的数据类型完全掩码Full masking according to the data types of the designated fields

对于字符串数据类型(nchar、ntext、nvarchar),使用 XXXX;如果字段大小小于 4 个字符,则使用更少的 X。• Use XXXX or fewer Xs if the size of the field is less than 4 characters for string data types (nchar, ntext, nvarchar).
• 对于数字数据类型(bigint、bit、decimal、int、money、numeric、smallint、smallmoney、tinyint、float、real),使用零值。• Use a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).
对于日期/时间数据类型(date、datetime2、datetime、datetimeoffset、smalldatetime、time),使用 1900-01-01。• Use 01-01-1900 for date/time data types (date, datetime2, datetime, datetimeoffset, smalldatetime, time).
• 对于 SQL 变量,使用当前类型的默认值。• For SQL variant, the default value of the current type is used.
• 对于 XML,使用文档 <masked/>。• For XML the document <masked/> is used.
• 对于特殊数据类型(timestamp、table、hierarchyid、GUID、binary、image、varbinary 空间类型),将使用空值。• Use an empty value for special data types (timestamp table, hierarchyid, GUID, binary, image, varbinary spatial types).
信用卡Credit card 此掩码方法公开指定字段的最后四位数,并添加一个信用卡格式的常量字符串作为前缀。Masking method, which exposes the last four digits of the designated fields and adds a constant string as a prefix in the form of a credit card.

XXXX-XXXX-XXXX-1234XXXX-XXXX-XXXX-1234
电子邮件Email 此掩码方法公开第一个字母并将域替换为 XXX.com,并使用一个电子邮件地址格式的常量字符串作为前缀。Masking method, which exposes the first letter and replaces the domain with XXX.com using a constant string prefix in the form of an email address.

aXX@XXXX.com
随机数Random number 此掩码方法根据选定边界和实际数据类型生成随机数Masking method, which generates a random number according to the selected boundaries and actual data types. 如果指定的边界相等,则掩码函数是常数。If the designated boundaries are equal, then the masking function is a constant number.

导航窗格Navigation pane
自定义文本Custom text 此掩码方法公开第一个和最后一个字符,并在中间添加一个自定义填充字符串。Masking method, which exposes the first and last characters and adds a custom padding string in the middle. 如果原始字符串短于公开的前缀和后缀,则只使用填充字符串。If the original string is shorter than the exposed prefix and suffix, only the padding string is used.
前缀 [填充] 后缀prefix[padding]suffix

导航窗格Navigation pane

DDM 建议引擎会将数据库中的某些字段标记为可能的敏感字段,可以考虑对这些字段进行掩码。The DDM recommendations engine, flags certain fields from your database as potentially sensitive fields, which may be good candidates for masking. 在门户的“动态数据掩码”边栏选项卡中,会看到针对数据库建议的列。In the Dynamic Data Masking blade in the portal, you will see the recommended columns for your database. 用户只需针对一个或多个列单击“添加掩码” ,单击“保存” ,即可对这些字段应用掩码。All you need to do is click Add Mask for one or more columns and then Save to apply a mask for these fields.

使用 PowerShell cmdlet 为数据库设置动态数据掩码Set up dynamic data masking for your database using PowerShell cmdlets

请参阅 Azure SQL 数据库 CmdletSee Azure SQL Database Cmdlets.

使用 REST API 为数据库设置动态数据掩码Set up dynamic data masking for your database using REST API

请参阅对 Azure SQL 数据库的操作See Operations for Azure SQL Database.