SQL数据分类–在SQL Server 2019中添加敏感度分类

SQL Server 2019 offers powerful new features to help in safeguarding your data and complying with various privacy regulations, which we’ll be covering in this article

SQL Server 2019提供了强大的新功能来帮助保护您的数据并遵守各种隐私法规，我们将在本文中进行介绍。

SQL Server Management Studio 17.5 introduced a new feature for data discovery and data classification into different categories. This helps to discover sensitive data such as SSN number, credit card number, bank account details, personal records etc. It is very critical for the data to be secure and compliant under SOX, PCI, and GDPR for each organization. The Classify data features adds extended properties to the columns to specify the label and the information type.

SQL Server Management Studio 17.5引入了一项新功能，用于将数据发现和数据分类到不同类别中。 这有助于发现敏感数据，例如SSN号，信用卡号，银行帐户详细信息，个人记录等。对于每个组织来说，对于SOX，PCI和GDPR，确保数据的安全性和合规性至关重要。 “分类”数据功能将扩展属性添加到列中以指定标签和信息类型。

This option is available under (Right click on Database) Tasks->Data Discovery and Classification ->Classify Data in SSMS version 17.5 and above.

在SSMS 17.5及更高版本中，（在数据库上单击鼠标右键）“任务”->“数据发现和分类”->“分类数据”下提供此选项。

If we run the ‘Classify Data’ on WideWorldImporters database, we get the ‘Data Classification’ report.

如果在WideWorldImporters数据库上运行“分类数据”，则将获得“数据分类”报告。

We can see that we have the option to classify the information type. We can decide whether this column contains contact info, banking, credit card, date of birth, financial, National ID etc.

我们可以看到我们可以选择对信息类型进行分类。 我们可以决定此列是否包含联系方式，银行业务，信用卡，出生日期，财务状况，身份证等。

Similarly, we can choose the sensitive level as shown below:

同样，我们可以选择敏感级别，如下所示：

Therefore, we can choose the appropriate information type and sensitivity level and apply the recommendation accordingly. Once we apply and save the recommendation, we can view the SQL Data Classification Report that shows the information such as Classified columns, tables containing sensitive data.

因此，我们可以选择适当的信息类型和敏感度级别并相应地应用建议。 应用并保存建议后，我们可以查看SQL数据分类报告，该报告显示诸如分类列，包含敏感数据的表之类的信息。

Once we have defined the information type and sensitivity level, we can go to particular column properties and can see that this information is added as part of extended properties as shown below:

定义信息类型和敏感度级别后，我们可以转到特定的列属性，并且可以看到此信息是作为扩展属性的一部分添加的，如下所示：

We can see all the columns in a database where we defined the data classification.

我们可以看到定义了数据分类的数据库中的所有列。

SELECT Schema_name(objects.schema_id) AS schema_name, 
objects.NAME                   AS table_name, 
columns.NAME                   AS column_name, 
ISNULL(EP.information_type_name,'') AS  information_type_name,
ISNULL(EP.sensitivity_label_name,'') AS  sensitivity_label_name
FROM (SELECT ISNULL(EC1.major_id,EC2.major_id) AS major_id, 
      ISNULL(EC1.minor_id,EC2.minor_id) AS minor_id, 
      EC1.information_type_name, 
      EC2.sensitivity_label_name 
      FROM (SELECT major_id, 
             minor_id,
              NULLIF(value,'') AS information_type_name
              FROM sys.extended_properties 
              WHERE NAME = 'sys_information_type_name') EC1
              FULL OUTER JOIN (SELECT major_id, 
                 minor_id, 
                                NULLIF(value,'') AS sensitivity_label_name
                                FROM sys.extended_properties 
                                WHERE  NAME = 'sys_sensitivity_label_name') EC2 
                                ON ( EC2.major_id = EC1.major_id AND EC2.minor_id =              EC1.minor_id )) EP 
 JOIN sys.objects objects 
 ON EP.major_id = objects.object_id 
 JOIN sys.columns columns 
 ON ( EP.major_id = columns.object_id AND EP.minor_id = columns.column_id )

So far, we have explored the way Classify Data in SQL Server Management Studio using the extended properties in the columns. Now let us view the enhancements in the SQL Server 2019.

到目前为止，我们已经探索了使用列中的扩展属性在SQL Server Management Studio中对数据进行分类的方法。 现在让我们查看SQL Server 2019中的增强功能。

SQL Server 2019和数据分类 (SQL Server 2019 and Data Classification)

Before we move further, you can go through below articles to get familiar with SQL Server 2019.

在我们继续之前，您可以阅读下面的文章以熟悉SQL Server 2019。

• SQL Server 2019 overview and installation SQL Server 2019概述和安装
• SQL Server 2019 – New DMF sys.dm_db_page_info SQL Server 2019 –新的DMF sys.dm_db_page_info
• SQL Server 2019 Enhanced PolyBase – Part 1 SQL Server 2019增强的PolyBase-第1部分
• SQL Server 2019 Enhanced PolyBase – Part 2 SQL Server 2019增强的PolyBase-第2部分

SQL Server 2019 provides enhancements to Data classification feature. SQL Server 2019 provides new command ‘ADD SENSITIVITY CLASSIFICATION’. We can define the information type and label for our sensitive data similar to SQL Server Management Studio 17.5 and above Classify data. We will see the difference in the later section of the article.

SQL Server 2019提供了对数据分类功能的增强。 SQL Server 2019提供了新命令``ADD SENSITIVITY CLASSIFICATION''。 我们可以为敏感数据定义信息类型和标签，类似于SQL Server Management Studio 17.5及以上对数据进行分类。 我们将在本文的后面部分看到差异。

The syntax for command ‘ADD SENSITIVITY CLASSIFICATION’ is as shown below.

命令“ ADD SENSITIVITY CLASSIFICATION”的语法如下所示。

ADD SENSITIVITY CLASSIFICATION TO [Object names]
WITH (LABEL = [Label type], INFORMATION_TYPE = [Information Type]);

[Object names]	[schema_name].[table_name].[column_name]
Label	Public General Confidential – GDPR Confidential Highly Confidential Highly Confidential – GDPR
Information Type	Banking Contact Info Credentials Credit Card Date of Birth Financial Health Name National ID SSN Other

[对象名称]	[schema_name]。[table_name]。[column_name]
标签	上市 一般 机密– GDPR 机密 高度机密 高度机密– GDPR
信息类型	银行业 联络资料 证书 信用卡 出生日期 金融 健康 名称 国民身份证 社交网络 其他

Now we will define the data classification using SQL Server 2019 ADD SENSITIVITY CLASSIFICATION.

现在，我们将使用SQL Server 2019 ADD SENSITIVITY CLASSIFICATION定义数据分类。

Run the below query to define the data classification for the above table and columns.

运行以下查询以定义上述表和列的数据分类。

ADD SENSITIVITY CLASSIFICATION TO APPLICATION.PaymentMethods_Archive.PaymentMethodName
WITH (LABEL = 'Confidential', INFORMATION_TYPE = 'Financial');
 
ADD SENSITIVITY CLASSIFICATION TO APPLICATION.PEOPLE.EMailAddress
WITH (LABEL = 'Highly Confidential', INFORMATION_TYPE = 'Contact Info');
 
ADD SENSITIVITY CLASSIFICATION TO APPLICATION.PEOPLE.HashedPassword
WITH (LABEL = 'Highly Confidential- GDPR', INFORMATION_TYPE = 'Credentials');

In this example, we defined data classification individually for each table. Let us assume that we want to define the same label and information type for two columns from different tables. We can either run the separate statement or club them into a single statement.

在此示例中，我们为每个表分别定义了数据分类。 让我们假设我们要为来自不同表的两列定义相同的标签和信息类型。 我们可以运行单独的语句，也可以将它们合并为一个语句。

In the below example, we will define the same data label (Confidential) and information type (Financial) for the SpecialDeals and StockItems table.

在下面的示例中，我们将为SpecialDeals和StockItems表定义相同的数据标签（机密）和信息类型（财务）。

Therefore, we can run the below query. In this query, we have added both the tables in a single statement of ‘ADD SENSITIVITY CLASSIFICATION’.

因此，我们可以运行以下查询。 在此查询中，我们将两个表添加到了“ ADD SENSITIVITY CLASSIFICATION”的单个语句中。

ADD SENSITIVITY CLASSIFICATION TO 
Sales.Invoices.InvoiceID,
Sales.SpecialDeals.DiscountAmount
WITH (LABEL = 'Confidential', INFORMATION_TYPE = 'Financial');

Now, let us look at the column property similar way we checked above after specifying data classification using SQL Server Management Studio 17.5. We do not find any extended property in SQL Server 2019 way of classifying data.

现在，让我们看一下使用SQL Server Management Studio 17.5指定数据分类之后，我们与上面检查过的方式类似的列属性。 我们无法在SQL Server 2019中对数据进行分类的方式中找到任何扩展属性。

SQL Server 2019 does not store information as the extended properties. Instead, it adds metadata about the sensitivity classification information as per defined columns. We can view the information about classified columns from the system view sys.sensitivity_classifications.

SQL Server 2019不会将信息存储为扩展属性。 相反，它按定义的列添加有关灵敏度分类信息的元数据。 我们可以从系统视图sys.sensitivity_classifications中查看有关分类列的信息。

Select class_desc,label,OBJECT_NAME(major_id) as [Object Name],
label,information_type from sys.sensitivity_classifications

SQL Server 2019中分类数据的审核行为 (Audit behaviour for classified data in SQL Server 2019)

Suppose we have defined auditing on the database table. Later we have defined the data classification using ADD SENSITIVITY CLASSIFICATION. Do we need to make changes in the data auditing in order to capture the related information about the data classification?

假设我们已经在数据库表上定义了审计。 后来我们使用ADD SENSITIVITY CLASSIFICATION定义了数据分类。 我们是否需要在数据审核中进行更改以捕获有关数据分类的相关信息？

Firstly, we can see that the on below columns we have not defined any data classification. We will define it later.

首先，我们可以看到在下面的列中我们没有定义任何数据分类。 我们将在以后定义它。

Now let us create the SERVER AUDIT first using below query:

现在，让我们首先使用以下查询创建SERVER AUDIT：

USE master;
GO
 
CREATE SERVER AUDIT DataClassficationSQL2019 TO FILE (FILEPATH = 'C:\sqlshack\Audit\');
GO  
 
ALTER SERVER AUDIT DataClassficationSQL2019 WITH (STATE = ON);
GO

This query creates the audit file in C:\sqlshack\Audit folder. We can see a server audit file in this path.

该查询在C：\ sqlshack \ Audit文件夹中创建审核文件。 我们可以在此路径中看到服务器审核文件。

• Define Database Audit on object level and specify the operation we want to capture for example select, insert, update etc.
  在对象级别定义数据库审核，并指定我们要捕获的操作，例如选择，插入，更新等。

USE [WideWorldImporters]
GO
 
Create DATABASE AUDIT SPECIFICATION [PurchasingSuppliersTransactions]
FOR SERVER AUDIT DataClassficationSQL2019
ADD (SELECT ON [Purchasing].[SupplierTransactions] BY dbo) WITH (STATE = ON);

This database audit will capture the event for select statements on the Application. People Table.

此数据库审核将为应用程序上的select语句捕获事件。 人表。

• Now let us classify data using ‘ADD SENSITIVITY CLASSIFICATION’ in SQL Server 2019 for the columns
  现在让我们使用SQL Server 2019中的``ADD SENSITIVITY CLASSIFICATION''对列进行数据分类

• Perform some select activity on the [Purchasing].[SupplierTransactions] the table in the WideWorldImporters database. These select events will be captured by the database audit created in the above step
  在WideWorldImporters数据库中的[Purchasing]。[SupplierTransactions]表上执行一些选择活动。 这些选择事件将由上一步中创建的数据库审核捕获
• sys.fn_get_audit_file function sys.fn_get_audit_file函数分析数据库审核

We observed so far that we have not made any changes in the database audit. SQL Server 2019 adds a new column data_sensitivity_infomation in the audit file to analyze the database audit.

到目前为止，我们已经观察到我们在数据库审核中没有进行任何更改。 SQL Server 2019在审核文件中添加新列data_sensitiveivity_infomation以分析数据库审核。

In the below query, we can see that we provided input to the function as audit file created by the database audit.

在下面的查询中，我们可以看到我们将输入作为数据库审核创建的审核文件提供给函数。

sys.fn_get_audit_file( ‘C:\sqlshack\Audit\DataClassficationSQL2019_*.sqlaudit’

sys.fn_get_audit_file（'C：\ sqlshack \ Audit \ DataClassficationSQL2019 _ *。sqlaudit'

In addition, in the output, we will analyse the information from data_sensitivity_information column.

另外，在输出中，我们将分析data_sensitiveivity_information列中的信息。

data_sensitivity_information = CONVERT(xml, data_sensitivity_information)

data_sensitivity_information = CONVERT（xml，data_sensitivity_information）

SELECT event_time,action_id as [Activity],
statement as [Query],
[object] = [database_name] + '.' + [schema_name] + '.' + [object_name],
data_sensitivity_information = CONVERT(xml, data_sensitivity_information)
FROM sys.fn_get_audit_file( 'C:\sqlshack\Audit\DataClassficationSQL2019_*.sqlaudit'  DEFAULT ,  DEFAULT)
WHERE action_id = 'SL';  --SL filtered out events for select statements.

We can see that the select statement is captured in the database audit. Let us click on the XML link in the data_sensitivity_column. It returns the below information for the label and information_type.

我们可以看到select语句已在数据库审计中捕获。 让我们单击data_sensitiveivity_column中的XML链接。 它为label和information_type返回以下信息。

<sensitivity_attributes>
<sensitivity_attribute label=“Confidential” information_type=“Financial” />
</sensitivity_attributes>

< 敏感性属性 >
< 敏感性 标签 = “ 机密 ” 信息类型 = “ 财务 ” />
</ sensitivity_attributes >

Now let us verify the same using the sys .sensitivity_classification. we can see the database audit collected the same information without modifying anything in the audit.

现在，让我们使用sys .sensitiveivity_classification验证相同的结果。 我们可以看到数据库审核收集了相同的信息，而无需在审核中进行任何修改。

.

。

• Now let us add data classification to one more column in this [Purchasing].[SupplierTransactions] table.
  现在，让我们在此[Purchasing]。[SupplierTransactions]表中的另一列中添加数据分类。

LABEL = ‘Highly Confidential’, INFORMATION_TYPE = ‘Banking’

LABEL =“高度机密”，INFORMATION_TYPE =“银行”

ADD SENSITIVITY CLASSIFICATION TO [Purchasing].[SupplierTransactions].[SupplierInvoiceNumber]
WITH (LABEL = 'Highly Confidential', INFORMATION_TYPE = 'Banking');

• Let us view the database audit result again:
  让我们再次查看数据库审核结果：

Click on the XML link and we can view both the label and information type for the table.

单击XML链接，我们可以查看表的标签和信息类型。

<sensitivity_attributes>
<sensitivity_attribute label=“Highly Confidential” information_type=“Banking” />
<sensitivity_attribute label=“Confidential” information_type=“Financial” />
</sensitivity_attributes>

< 敏感性属性 >
< 敏感性 标签 = “ 高度机密 ” information_type = “ 银行 ” />
< 敏感性 标签 = “ 机密 ” 信息类型 = “ 财务 ” />
</ sensitivity_attributes >

We can verify the same information in below image as well.

我们也可以在下图中验证相同的信息。

结论 (Conclusion)

Add sensitivity classification is a nice enhancement in SQL Server 2019. We can identify the columns from the SQL Server Management Studio Classify Data report and add the metadata without the extended properties. You can work on this to take a step closer to achieving full data privacy regulation compliance.

添加敏感度分类是SQL Server 2019中的一项很好的增强。我们可以从SQL Server Management Studio的“分类数据”报告中识别列，并添加不具有扩展属性的元数据。 您可以在此基础上更进一步，以实现对数据隐私法规的完全合规性。

翻译自: https://www.sqlshack.com/sql-data-classification-add-sensitivity-classification-in-sql-server-2019/