了解SQL Server中的动态数据屏蔽

In this article, I’m going to explain the Dynamic Data Masking feature in SQL Server. As it goes by the name, Dynamic Data Masking is one of the security features that was introduced in SQL Server 2016. In an ever-growing digital era, the security of ones’ data has become one of the most important and expensive deals. Everyone expects their data to be protected as their own asset and like to treat it with the highest efficiency possible.

在本文中，我将解释SQL Server中的动态数据屏蔽功能。 顾名思义，动态数据屏蔽是SQL Server 2016中引入的安全功能之一。在不断发展的数字时代，个人数据的安全性已成为最重要和最昂贵的交易之一。 每个人都希望将自己的数据作为自己的资产加以保护，并希望以尽可能高的效率进行处理。

Dynamic Data Masking protects underlying data in SQL Server by applying masking function dynamically such that the end-users don’t get to see the actual data, but instead sees a masked version of the data. An example of such a use case may be when a contact center representative is tasked to help a bank customer with their financial information, in such cases only a masked version of the account number or the credit card number is visible to the representative.

动态数据屏蔽通过动态应用屏蔽功能来保护SQL Server中的基础数据，这样最终用户就不会看到实际数据，而是看到了数据的屏蔽版本。 这样的用例的一个例子是当联络中心代表受命帮助银行客户提供其财务信息时，在这种情况下，代表只能看到帐号或信用卡号的掩码版本。

Figure 1 – Data Masking Example

图1 –数据屏蔽示例

If you notice the illustration above, you can see on the left hand is the actual account numbers and on the right-hand, the account numbers are displayed, however, some of the numbers are replaced by a specific character, which in this case is “X”. This is done essentially to provide a secure way to represent any confidential information. If this masked representation is provided, it is not an easy task to make up the actual account number from that.

如果您注意到上面的插图，则可以看到左侧是实际的帐号，而右侧则显示了帐号，但是，某些号码被替换为特定的字符，在这种情况下为“X”。 这样做基本上是为了提供一种安全的方式来表示任何机密信息。 如果提供了此掩码表示，从中组成实际的帐号不是一件容易的事。

A few weeks back, I was working with one of my customers and we had a few requirements where I had to have access to the client’s data. As the company was governed under GDPR laws, I requested the team to mask the sensitive information and then transfer the data so that we may continue with the development work.

几周前，我正与一位客户一起工作，我们在必须访问客户数据方面有一些要求。 由于公司受GDPR法律管辖，因此我要求团队掩盖敏感信息，然后传输数据，以便我们继续进行开发工作。

An important point to note here is that Dynamic Data Masking is just a security feature that allows masking on the database layer only. However, the official documentation suggests that it is only primary security and should be used with other features such as Transparent Data Encryption (TDE) or Row Level Security (RLS).

这里要注意的重要一点是，动态数据屏蔽只是一项安全功能，仅允许在数据库层上进行屏蔽。 但是， 官方文档建议它只是主要安全性，应与其他功能（例如透明数据加密（TDE）或行级安全性（RLS））一起使用。

SQL Server中的屏蔽方法 (Masking Methods in SQL Server)

SQL Server already comes with some pre-defined mask functions that we can use while defining dynamic data masking in SQL Server. These are as follows:

SQL Server已经带有一些预定义的掩码函数，我们可以在SQL Server中定义动态数据掩码时使用这些函数。 这些如下：

默认数据屏蔽 (Default Data Masking)

This is a type of data masking that is applied automatically by the database engine based on the column data type. For example, if the data type of a column is a string (text, ntext, char, nchar, varchar, nvarchar), then this is the default masking that will be applied. The same goes for columns which are of numeric data type and other columns with date type data type. The default masking just replaces a few characters with “XXXX” from the data value. The number of “X” depends on the length of the data in the column.

这是一种数据屏蔽，数据库引擎根据列数据类型自动应用。 例如，如果列的数据类型是字符串（文本，ntext，char，nchar，varchar，nvarchar），则这是将应用的默认掩码。 数字数据类型的列和日期类型数据类型的其他列也是如此。 默认掩码仅将数据值中的一些字符替换为“ XXXX”。 “ X”的数量取决于列中数据的长度。

Figure 2 – Default Data Masking

图2 –默认数据屏蔽

部分数据屏蔽 (Partial Data Masking)

Partial Data Masking is also like the one we understood above, except this function accepts a parameter from the user to define how many characters from the columns should be masked. For example, if you see the figure below, the Partial Masking column has few characters from the beginning and end visible, but the characters in the middle have been masked.

部分数据屏蔽也类似于我们上面理解的那样，除了此函数接受用户的参数以定义应屏蔽列中的多少个字符。 例如，如果您看到下图，则“ 部分蒙版”列的开头和结尾处的字符很少，但是中间的字符已被蒙版。

Figure 3 – Partial Data Masking

图3 –部分数据屏蔽

电子邮件数据屏蔽 (Email Data Masking)

This function is specifically used to mask if the column contains an email address. It is not used to mask character or numeric fields. The masked column returns the first character of the email as-is and masks the remaining characters of the field. You can see an illustration in the figure below.

此功能专门用于屏蔽该列是否包含电子邮件地址。 它不用于屏蔽字符或数字字段。 被屏蔽的列按原样返回电子邮件的第一个字符，并屏蔽该字段的其余字符。 您可以在下图中看到一个插图。

Figure 4 – Email Masking

图4 –电子邮件屏蔽

实现动态数据屏蔽 (Implementing Dynamic Data Masking)

Now that we have some basic information about dynamic data masking and the various functions for applying the masking, let us learn how we can implement the same in SQL Server. For the purpose of this article, I’m using SQL Server 2016, but this feature is also available and can be used any versions higher than SQL Server 2016, including SQL Server 2017 and SQL Server 2019.

现在，我们已经掌握了有关动态数据屏蔽以及应用屏蔽的各种功能的一些基本信息，让我们学习如何在SQL Server中实现相同的功能。 就本文而言，我正在使用SQL Server 2016，但此功能也可用，并且可以用于高于SQL Server 2016的任何版本，包括SQL Server 2017和SQL Server 2019。

Let us first set up our dataset with which we will be performing all the operations from now on. You can execute the script below and set up the dataset on your local.

让我们首先设置数据集，从现在开始我们将使用该数据集执行所有操作。 您可以执行以下脚本，并在本地设置数据集。

CREATE TABLE DynamicDataMaskExample(
	[ID]		INT IDENTITY(1,1),
	[Name]		VARCHAR(20),
	[Phone]	BIGINT,
	[Email]	VARCHAR(30)
)
 
INSERT INTO DynamicDataMaskExample ([Name],[Phone],[Email])
VALUES ('John',9876543210,'john.doe@yahoo.com'),('Bob',5879612546,'bob.marley@google.com')

Now that the data is available; you can see that anyone can access this data and view sensitive information.

现在数据可用； 您会看到任何人都可以访问此数据并查看敏感信息。

Figure 5 – Data from Table

图5 –表中的数据

Let us now create a user who will have access to this table only. You can execute the script below to create the user and grant select permissions to it.

现在让我们创建一个只能访问该表的用户。 您可以执行以下脚本来创建用户并为其授予选择权限。

CREATE USER MaskUser WITHOUT LOGIN; 
GRANT SELECT ON DynamicDataMaskExample TO MaskUser;

Let us now apply the default mask function on this table. For this, we will just mask the Name column with the default masking operation as discussed in the section above.

现在让我们在此表上应用默认的遮罩功能。 为此，我们将使用上述部分中讨论的默认屏蔽操作来屏蔽“ 名称”列。

ALTER TABLE DynamicDataMaskExample  
ALTER COLUMN [Name] VARCHAR(20) MASKED WITH (FUNCTION = 'default()');

Now, let us try to see what the new user can see while selecting records from this table.

现在，让我们尝试查看新用户在从该表中选择记录时可以看到的内容。

EXECUTE AS USER = 'MaskUser'
SELECT * FROM DynamicDataMaskExample
REVERT
GO

Figure 6 – Default Masking

图6 –默认屏蔽

As you can see in the figure above, the user sees a masked version of the Name column. This user cannot see the underlying original data as it has been masked using the default function.

如上图所示，用户会看到“ 名称”列的蒙版。 该用户看不到基础原始数据，因为已使用默认功能将其掩盖。

Let us now implement the partial masking function on the Phone column.

现在让我们在“ 电话”列上实现部分屏蔽功能。

ALTER TABLE DynamicDataMaskExample  
ALTER COLUMN [Phone] VARCHAR(20) MASKED WITH (FUNCTION = 'partial(2,"XXXXX",3)');

Figure 7 – Partial Masking

图7 –部分屏蔽

If you observe the figure above, we have applied a partial masking function on the column Phone. This partial function is defined as to mask only those characters from the column as mentioned in the formula. For example, we mentioned to start masking after 2 digits, mask the next five digits with the character “X” and then keep the last 3 digits as it is.

如果您观察到上图，我们在“电话”列上应用了部分遮罩功能。 此部分函数的定义是仅屏蔽公式中提到的列中的那些字符。 例如，我们提到要在2位数字之后开始屏蔽，用字符“ X ”屏蔽后5位数字，然后保持最后3位数字不变。

Finally, let us mask the email column as well, but using the email function. You can hit the script below.

最后，让我们也屏蔽电子邮件列，但使用电子邮件功能。 您可以点击下面的脚本。

ALTER TABLE DynamicDataMaskExample
ALTER COLUMN [Email] VARCHAR(30) MASKED WITH (FUNCTION = 'email()');

Figure 8 – Email Masking

图8 –电子邮件屏蔽

So far, we have explored how to implement the various functions of Dynamic Data Masking in SQL Server and we have seen that the data is masked and only a portion of the actual data is visible to the user. This is useful in providing a security layer dynamically while the underlying data remains the same. As already explained earlier, this feature doesn’t entirely encrypt a column, instead, it just masks the characters of that column.

到目前为止，我们已经探索了如何在SQL Server中实现动态数据屏蔽的各种功能，并且我们已经看到数据被屏蔽了，并且只有一部分实际数据对用户可见。 这对于在基础数据保持不变的同时动态提供安全层很有用。 如前所述，此功能并未完全加密一列，而是仅掩盖了该列的字符。

If you select data from the same table as the original user, you’ll still be able to see the actual data and no masked data at all.

如果您从与原始用户相同的表中选择数据，则仍然可以看到实际数据，而根本看不到任何屏蔽数据。

Figure 9 – No Masked Data

图9 –没有屏蔽的数据

使用动态数据屏蔽检查列 (Check columns with Dynamic Data Masking)

Sometimes, it is possible that you are already working on a database but you’re not aware of the data is already masked or not. In such cases, we can leverage one of the SQL views which SQL Server provides to have an overview on which columns, dynamic data masking has been applied. In order to select data from this view, you can execute the following script.

有时，您可能已经在使用数据库，但是您不知道数据是否已被屏蔽。 在这种情况下，我们可以利用SQL Server提供SQL视图之一来概述应用了动态数据屏蔽的列。 为了从该视图选择数据，您可以执行以下脚本。

SELECT * FROM sys.masked_columns

Figure 10 – Check Masked Columns

图10 –检查被屏蔽的列

结论 (Conclusion)

In this article, we have seen what dynamic data masking in SQL Server is all about. I have also explained how this feature works in SQL Server and the various functions that can be used to mask a column in a table. Finally, I have demonstrated with live examples where I have modified the underlying columns one at a time by applying the different functions – default, partial and email and obtain the masking. It is quite a useful feature to restrict users’ access to sensitive data, however, it doesn’t encrypt any data.

在本文中，我们已经了解了SQL Server中的动态数据屏蔽是什么。 我还解释了此功能在SQL Server中的工作方式以及可用于屏蔽表中列的各种功能。 最后，我通过现场示例进行了演示，其中我通过应用不同的功能（默认，部分和电子邮件）一次修改了基础列并获得了屏蔽。 限制用户访问敏感数据是一项非常有用的功能，但是，它不会对任何数据进行加密。

翻译自: https://www.sqlshack.com/understanding-dynamic-data-masking-in-sql-server/