SQL Server 2016的新功能–动态数据屏蔽

There are many new features in SQL Server 2016, but the one we will focus on in this post is:

SQL Server 2016中有许多新功能，但本文中我们将重点介绍的功能是：

• Dynamic Data Masking
  动态数据屏蔽

Have you ever been on a website, where your personal information, ie. Social Security number or Credit Card number shown in clear text, ready for everyone to have a look at. Would it not be cool if your information was somehow masked by default, and not needed to rely on the application to mask the data before displaying it on the screen?

您是否曾经浏览过您的个人信息的网站，即 以清楚的文字显示的社会保险号或信用卡号，方便所有人查看。 如果默认情况下以某种方式屏蔽了您的信息，并且不需要在屏幕上显示数据之前依赖于应用程序来屏蔽数据，这会很酷吗？

数据屏蔽的示例 (Examples of Datamasking)

Type	Value	Masked
Social Security number	320474-2345	320474-****
Credit Card	4566-6546-6546-7897	4566-****-****-****
Telephone	+45 45454545	+45 45******
Email	kenneth@mydomain.com	K*******@*********.com

类型	值	蒙面的
社会安全号码	320474-2345	320474-****
信用卡	4566-6546-6546-7897	4566-****-****-****
电话	+45 45454545	+45 45 ******
电子邮件	kenneth@mydomain.com	K*******@*********.com

If you look at the above, then it is clear that it would be really nice to have your sensitive data somewhat masked when displayed on a monitor, that other people can look at the same time.

如果您看上面的内容，那么很明显地，当您的敏感数据在监视器上显示时，将您的敏感数据掩盖起来会非常好，其他人可以同时查看。

Now with the introduction of SQL Server 2016 we have the possibility to mask data in the database, and the data is masked when queried as well – this means that the application developers should not worry about this little security issue any more.

现在，随着SQL Server 2016的引入，我们可以屏蔽数据库中的数据，并且在查询时也将屏蔽数据–这意味着应用程序开发人员不必再担心这个小安全问题。

如何使用它 (How to use it)

While it is a feature in CTP 2.1 and enabled by default in this build, you should enable a few trace flags to use it, of you are using CTP 2.0 build. The trace flags is as follows.

虽然它是CTP 2.1中的一项功能，并且在此内部版本中默认启用，但在使用CTP 2.0内部版本时，应启用一些跟踪标志以使用它。 跟踪标志如下。

 
-- To enable Dynamic Data Masking in CTP 2.0
 
DBCC TRACEON(209,219,-1)
 

When this is done, you are ready to work with Dynamic Data Masking.

完成此操作后，就可以使用动态数据屏蔽了。

There are three types of masks available from SQL server as default.

默认情况下，SQL Server提供三种掩码类型。

Default	Can be used on Nchar and Nvarchar, remember that MAX size is not supported Use XXXX for char fields and a zero value for numeric datatypes Can also mask datetypes, date, datetime2, datetime, smalldatetime, time and datetimeoffset
Email	When using email mask the first letter in the email will be shown, and the domain
String	Allows you to mask strings with you own masking string in the middle

默认	可以在Nchar和Nvarchar上使用，请记住不支持MAX大小 将XXXX用于char字段，并将零值用于数字数据类型 也可以屏蔽日期类型，日期，datetime2，datetime，smalldatetime，time和datetimeoffset
电子邮件	使用电子邮件掩码时，将显示电子邮件中的第一个字母，并且域
串	允许您在中间使用自己的屏蔽字符串屏蔽字符串

我们创建一个数据屏蔽表 (Let’s create a table for data masking)

The following table will contain information that we would like to mask later.

下表将包含我们稍后要掩盖的信息。

 
-- Create a table that we need masking on
 
CREATE TABLE [dbo].[Client]
(
	[ClientID] int identity(1,1) not null,
	[Firstname] nvarchar(50) null,
	[Lastname] nvarchar(50) null,
	[Birthdate] datetime null,
	[Email] nvarchar(128) null,
	[PhoneNumber] nvarchar(50) null,
	[Birthplace] nvarchar(50) null,
	[SocialSecurityNumber] nvarchar(12) null
)
 

At the moment, the table is not enabled for masking, and every column in the table will be showing all the data it is storing. Let us also insert some data that we can work with.

目前，该表尚未启用屏蔽功能，并且该表中的每一列都将显示其存储的所有数据。 让我们还插入一些可以使用的数据。

 
-- Insert test data
 
INSERT INTO [dbo].[Client] 
(Firstname, Lastname, Birthdate, Email, Phonenumber, SocialSecurityNumber, Birthplace) 
VALUES
  ('Kenneth', 'Nielsen', '1974-05-19', 'Kenneth@funkylab.com', '+45-12345678', 1905741234, 'Copenhagen')
, ('Peter', 'Nielsen', '1953-05-19', 'Peter@funkylab.com', '+45-23456789', 1901531234, 'Stockholm')
, ('Lotte', 'Nielsen', '1965-01-06', 'Lotte@funkylab.com', '+45-87654321', 01061234, 'Aalborg')
 

我可以屏蔽表中已经存在的数据吗？ (Can I mask data already in a table?)

Unlike the other security feature “Always Encrypted”, we have the possibility to mask data that is already in a table without the hassle of creating a new table and importing data into that one. We simply have to alter the table and columns that we need masked data in.

与其他安全功能“始终加密”不同，我们可以屏蔽表中已有的数据，而无需创建新表并将数据导入到该表中。 我们只需要更改需要屏蔽数据的表和列即可。

To do so, we will issue the following statement.

为此，我们将发布以下声明。

 
-- To alter a table already in the database
 
ALTER TABLE <TableName>
ALTER COLUMN <ColumnName> ADD MASKED WITH (FUNCTION = '<FunctionName>')
 

The functions that can be used is the ones stated above, so if the column was holding ie. Email addresses, the alter function could be this. The following script will enable data masking on the data in the dbo.client table.

可以使用的功能是上述功能，因此，如果该列正在保存，即。 电子邮件地址，更改功能可能是这样。 以下脚本将对dbo.client表中的数据启用数据屏蔽。

 
-- To alter a table already in the database
 
ALTER TABLE [dbo].[Client] ALTER COLUMN [EMAIL] ADD MASKED WITH (FUNCTION = 'EMAIL()')
ALTER TABLE [dbo].[Client] ALTER COLUMN [BIRTHDATE] ADD MASKED WITH (FUNCTION = 'DEFAULT()')
ALTER TABLE [dbo].[Client] ALTER COLUMN [Firstname] ADD MASKED WITH (FUNCTION = 'PARTIAL(1, "XXXXXXXXXX", 0)')
ALTER TABLE [dbo].[Client] ALTER COLUMN [Lastname] ADD MASKED WITH (FUNCTION = 'DEFAULT()')
ALTER TABLE [dbo].[Client] ALTER COLUMN [PhoneNumber] ADD MASKED WITH (FUNCTION = 'PARTIAL(4, "XXXXXX", 2)')
ALTER TABLE [dbo].[Client] ALTER COLUMN [SocialSecurityNumber] ADD MASKED WITH (FUNCTION = 'PARTIAL(6, "XXXX", 0)')
ALTER TABLE [dbo].[Client] ALTER COLUMN [BirthPlace] ADD MASKED WITH (FUNCTION = 'PARTIAL(1, "XXXXXXXXXX", 0)')
 

Now data should be masked when we query it, but if we just make a SELECT * FROM [dbo].[Client] we will still be able to see the data – this is because we are logged into the database as DBO, and therefore have all the rights needed to see and manipulate data.

现在，在查询数据时应该屏蔽数据，但是如果仅执行SELECT * FROM [dbo]。[Client]，我们仍然可以看到数据–这是因为我们以DBO身份登录数据库，因此拥有查看和操作数据所需的所有权利。

To test if our datamask is working, we have to create a new login and user, that is only member of the Data_Reader role, this role have rad permissions, and data will be masked dynamically.

要测试我们的数据掩码是否正常工作，我们必须创建一个新的登录名和用户，该用户和用户仅是Data_Reader角色的成员，该角色具有rad权限，并且数据将被动态屏蔽。

创建登录名 (Create Logins)

 
-- Create login and user with only Read Permissions
 
USE master
GO
 
CREATE LOGIN [DynamicMaskReader]
    WITH PASSWORD = 'DynamicMask',
    DEFAULT_DATABASE = [master],
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF
GO
 
USE DynamicDataMasking
GO
 
CREATE USER [DynamicMaskReader] FOR LOGIN [DynamicMaskReader] WITH DEFAULT_SCHEMA = dbo
GO
 
ALTER ROLE db_datareader ADD MEMBER [DynamicMaskReader]
GO
 

以读者身份连接 (Connect as Reader)

When this user is created, you have to open a new Query and connect to the database using the new user.

创建该用户后，您必须打开一个新的查询并使用该新用户连接到数据库。

1. CTRL+N (opens a new query window)
   CTRL + N（打开新的查询窗口）
2. Right click on the query window
   右键单击查询窗口

3. Select Connection ➜ Disconnect

   选择连接➜断开连接

   

4. Right click on the query window
   右键单击查询窗口

5. Select Connection ➜ Connect

   选择连接➜连接

   

6. Select SQL Server Authentication
   选择SQL Server身份验证
7. Type in the name “DynamicMaskReader” in login
   在登录名中输入名称“ DynamicMaskReader”

8. Type in the password “DynamicMask” in password

   在密码中输入密码“ DynamicMask”

   

9. And click Connect
   然后点击连接

Now we are ready to query the data that we have put a dynamic datamask on.

现在，我们可以查询放置了动态数据掩码的数据了。

查询数据 (Query the Data)

In the query window, you can now issue statements where you select data from the table [dbo].[client] and you will see that the data is masked as by the function we defined earlier.

现在，在查询窗口中，您可以发出语句，从表[dbo]。[client]中选择数据，然后您将看到数据被我们前面定义的函数掩盖了。

• Firstname is masked so that the first letter is visible and all remaining letters is replaced by X
  屏蔽了名字，以便第一个字母可见，而所有剩余字母均被X替换
• Lastname is masked by default, leaving all traces of a name masked and replaced by 4 X
  默认情况下，姓氏会被屏蔽，而所有名称的痕迹都会被屏蔽并替换为4 X
• Birthdate is masked to be 2000-01-01 for all records
  所有记录的生日都被屏蔽为2000-01-01
• Email is masked by function Email and therefore only showing first letter and top-level domain.
  电子邮件被功能电子邮件掩盖，因此仅显示首字母和顶级域。
• Phonenumber is masked to only show country code and last number in this number format.
  电话号码被屏蔽，仅以该号码格式显示国家代码和最后一个号码。
• Birthplace is masked to show only first letter and all remaining letters is replaces by X
  出生地被遮罩以仅显示第一个字母，而所有剩余字母被X替换
• 
  
  

ClientID	Firstname	Lastname	Birthdate	Email	PhoneNumber	Birthplace	SocialSecurity Number
1	KXXXXXXXXXX	xxxx	2000-01-01 00:00:00.000	KXXX@XXXX.com	+45-XXXXXX78	CXXXXXXXXXX	190574XXXX
2	PXXXXXXXXXX	xxxx	2000-01-01 00:00:00.000	PXXX@XXXX.com	+45-XXXXXX89	SXXXXXXXXXX	190153XXXX
3	LXXXXXXXXXX	xxxx	2000-01-01 00:00:00.000	LXXX@XXXX.com	+45-XXXXXX21	AXXXXXXXXXX	106123XXXX

客户编号	名字	姓	生日	电子邮件	电话号码	出生地	社会保障 数
1个	KXXXXXXXXXX	xxx	2000-01-01 00：00：00.000	KXXX@XXXX.com	+ 45-XXXXXX78	CXXXXXXXXXX	190574XXXX
2	PXXXXXXXXXX	xxx	2000-01-01 00：00：00.000	PXXX@XXXX.com	+ 45-XXXXXX89	SXXXXXXXXXX	190153XXXX
3	LXXXXXXXXXX	xxx	2000-01-01 00：00：00.000	LXXX@XXXX.com	+ 45-XXXXXX21	AXXXXXXXXXX	106123XXXX

结论 (Conclusion)

This post should have given you a basic understanding of the concept of Dynamic Data Masking, and should enable you to use it on your own data/tables when you make the shift to SQL Server 2016.

这篇文章应该使您对动态数据屏蔽的概念有了基本的了解，并且应该使您在转向SQL Server 2016时可以在自己的数据/表上使用它。

I hope you have enjoyed reading and testing, and remember it is very easy to set up a test SQL Server 2016 in AZURE. Perhaps I should make a post on that ;o)

我希望您喜欢阅读和测试，并记住在AZURE中设置测试SQL Server 2016非常容易。 也许我应该就此发表一个帖子; o）

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