azure云数据库

In this article, we will review Dynamic Data Masking in the Azure SQL database. Dynamic Data Masking is a feature to limit the sensitive data to the non-privileged users by hiding the data of a column. Data is not masked and stored in the disk. Masking is applied to the result set returned to the user. The users with “db_owner” permission on “UNMASK” permission can still see the unmasked data when they query the table.

在本文中，我们将回顾Azure SQL数据库中的动态数据屏蔽。 动态数据屏蔽是一项功能，它通过隐藏列的数据来将敏感数据限制为非特权用户。 数据不会被屏蔽并存储在磁盘中。 屏蔽应用于返回给用户的结果集。 具有“ UNMASK”权限的“ db_owner”权限的用户在查询表时仍可以看到未屏蔽的数据。

You can also go over articles here, to learn about data masking to protect sensitive data in on-prem SQL Server databases.

您还可以在此处阅读文章“ 以了解有关数据屏蔽以保护本地SQL Server数据库中的敏感数据的信息。

Let us see how to apply Dynamic Data Masking in the Azure SQL database using the Azure portal.

让我们看看如何使用Azure门户在Azure SQL数据库中应用动态数据屏蔽。

从Azure门户设置动态数据屏蔽 (Setting up Dynamic Data Masking from Azure portal)

Log in to the Azure portal and click on SQL Databases. Click on the database where you are going to set up Dynamic Data Masking.

登录到Azure门户，然后单击“ SQL数据库”。 单击要在其中设置动态数据屏蔽的数据库。

Under the security section, click on Dynamic Data Masking. This page shows the list of tables and columns in the database that is recommended for masking.

在安全性部分下，点击动态数据屏蔽 。 此页面显示了建议用于屏蔽的数据库中的表和列的列表。

Click on Add mask button to mask the column using the default Dynamic Data Masking function. Click on Save to apply the masking.

单击添加蒙版按钮以使用默认的动态数据蒙版功能对列进行蒙版。 单击保存以应用遮罩。

Log in to the Azure SQL Server using SQL Server management studio and execute the below query using the admin user and the user who has db_owner permission.

使用SQL Server Management Studio登录到Azure SQL Server，并使用admin用户和具有db_owner权限的用户执行以下查询。

select top 5 CustomerID,EmailAddress,CompanyName from SalesLT.Customer

We will be still able to see the unmasked data as we have the appropriate permissions to view data.

由于我们拥有查看数据的适当权限，因此我们仍然可以看到未屏蔽的数据。

Let us create a login, user and give the user data reader access.

让我们创建一个用户登录名，并授予用户数据读取器访问权限。

Execute the following script on the master database to create a login and a user. If you wish to, replace the login and the password name of your choice.

在master数据库上执行以下脚本，以创建登录名和用户。 如果需要，请替换您选择的登录名和密码名。

CREATE LOGIN testuser  WITH PASSWORD = 'password' GO create user testuser for login testuser

Execute the following script in the database that you used for dynamic data masking. This creates a user “testuser” for the login “testuser” and the user is added to the db_datareader role.

在用于动态数据屏蔽的数据库中执行以下脚本。 这将为登录名“ testuser”创建一个用户“ testuser”，并将该用户添加到db_datareader角色。

create user testuser for login testuser ALTER ROLE db_datareader ADD MEMBER [testuser] ;  GO

Log in to the Azure SQL server using the login we created above and execute the below query.

使用上面创建的登录名登录到Azure SQL服务器，然后执行以下查询。

select top 5 CustomerID,EmailAddress,CompanyName from SalesLT.Customer

We can see only limited data when the query is executed as a user with read-only access. When default masked function is used, the masked value is dependent on the data type. For a string value, the default masking function uses XXXX to mask the values.

当以具有只读访问权限的用户身份执行查询时，我们只能看到有限的数据。 使用默认的屏蔽功能时，屏蔽值取决于数据类型。 对于字符串值，默认屏蔽功能使用XXXX屏蔽值。

Default dynamic data masking function uses zero when masking is applied on a column with int, bigint, bit, decimal, money, numeric, smallint, smallmoney, tinyint, float, real data types.

当对具有int，bigint，bit，decimal，money，numerical，smallint，smallmoney，tinyint，float和real数据类型的列应用屏蔽时，默认的动态数据屏蔽功能将使用零。

For date/time data types it uses “01-01-1900”.

对于日期/时间数据类型，它使用“ 01-01-1900”。

In case you want to change the default masking function to other masking function, click on the masking rule.

如果要将默认的屏蔽功能更改为其他屏蔽功能，请单击屏蔽规则。

Select the function you want and click on Update.

选择所需的功能，然后单击更新。

Close the rule windows and click on Save to change the rule from default function to email function.

关闭规则窗口，然后单击“保存”以将规则从默认功能更改为电子邮件功能。

Log in to the Azure SQL Server and query the table again to see the data which is masked with email function. The only first character is visible, and the rest of part is masked with xxx@xxxx.com

登录到Azure SQL Server并再次查询该表以查看被电子邮件功能屏蔽的数据。 唯一可见的第一个字符，其余部分用xxx@xxxx.com掩盖

To mask the tables which are not part of the recommendations, click on Add mask.

要屏蔽不属于建议范围的表，请单击添加掩码。

Select the schema to populate the list of the tables in the selected schema. Select the table and column you want to mask. Select the dynamic data masking function and click on Add and then Save.

选择架构以填充所选架构中的表列表。 选择要屏蔽的表和列。 选择动态数据屏蔽功能，然后单击添加 ，然后单击保存 。

In this case, I have chosen the random format and gave the range from 9 to 10. Now the EmpID column is masked with a random value which is between the given range.

在这种情况下，我选择了随机格式，范围从9到10。现在EmpID列被给定范围之间的随机值所掩盖。

使用自定义值屏蔽字符串值 (Masking string value with custom value)

We can mask the string value with the format we want. Below is the example of masking string value with a custom format.

我们可以使用所需的格式屏蔽字符串值。 下面是使用自定义格式屏蔽字符串值的示例。

Select custom string option from the masking functions and specify the values for exposed prefix, padding string, and exposed suffix fields.

从掩码功能中选择“自定义字符串”选项，然后指定公开的前缀，填充字符串和公开的后缀字段的值。

In this case, I want to expose the first two characters and the last 4 characters of the string and mask the string with “X-X-X-X”. Please refer to the below image for the result set with the custom format we used above.

在这种情况下，我要显示字符串的前两个字符和后四个字符，并用“ XXXX”屏蔽字符串。 请参考下图以获取我们上面使用的自定义格式的结果集。

XML类型的遮罩栏 (Masking column with XML type)

Only default dynamic data masking function can be used to mask the column with XML type. The masked value will show on only one tag <masked />. Please refer to the below image for masked XML value.

仅默认的动态数据屏蔽功能可用于屏蔽XML类型的列。 被屏蔽的值将仅显示在一个<masked />标签上。 请参考下图以了解屏蔽的XML值。

删除列上的遮罩 (Removing masking on a column)

To remove masking on a column from the Azure portal, click on the masking rule. In Edit Masking rule window, click on the Delete button and then click on Save.

若要从Azure门户删除列上的掩码，请单击掩码规则。 在“编辑屏蔽规则”窗口中，单击“ 删除”按钮，然后单击“ 保存”。

授予访问权限以查看屏蔽的数据 (Grant access to users to view masked data)

Administrators and database owners can see the data on masked columns as usual. To give access to other users, just enter the user names separated by a comma in the text box just under the masking rules and click on Save. Please refer to the below image.

管理员和数据库所有者可以照常在屏蔽列上查看数据。 要授予其他用户访问权限，只需在屏蔽规则下方的文本框中输入用逗号分隔的用户名，然后单击保存 。 请参考下图。

结论 (Conclusion)

In this article, we explored how to apply Dynamic Data Masking on Azure SQL database using the Azure portal with different masking functions and data types. In case you have any questions, please feel free to ask in the comment section below.

在本文中，我们探讨了如何使用具有不同屏蔽功能和数据类型的Azure门户在Azure SQL数据库上应用动态数据屏蔽。 如果您有任何疑问，请随时在下面的评论部分中提问。

To continue your learning about Azure SQL, please refer to the .

要继续学习Azure SQL，请参考 。

翻译自:

azure云数据库