azure云数据库
Azure SQL supports in building and managing wide range of SQL databases, tools, frameworks etc. on the cloud. Organizations are drifting towards Azure SQL because of its obvious advantages over on-premises SQL Server and are generating, exchanging and storing data at an exponential rate on Azure. It becomes essential to safeguard, monitor our sensitive data and get alerts on anomalous activities else companies may come under fire for letting data leak and hacked.
Azure SQL支持在云上构建和管理各种SQL数据库,工具,框架等。 由于其比本地SQL Server明显的优势,组织正在向Azure SQL迁移,并且正在Azure上以指数速率生成,交换和存储数据。 保护,监视我们的敏感数据并获得异常活动的警报至关重要,否则公司可能因数据泄漏和被黑客攻击而受到抨击。
介绍 (Introduction)
Microsoft SQL Azure has recently introduced Advanced Data Security capability as a single pane to help monitor and store our confidential data in a more secure way. It is an integrated security package that offers a single go-to-place for classifying, assessing, tracking, remediating, detecting malicious activities and much more. Previously, features like ‘Threat Detection & Auditing and ‘Vulnerability Assessment’ were managed separately under Advanced Threat Protection heading and now they are updated and unified under one umbrella – Advanced Data Security in Azure SQL.
Microsoft SQL Azure最近将高级数据安全性功能作为一个窗格引入,以帮助以更安全的方式监视和存储我们的机密数据。 它是一个集成的安全软件包,它提供了一个分类,评估,跟踪,修复,检测恶意活动等的唯一途径。 以前,“威胁检测和审核”和“漏洞评估”等功能是在“高级威胁防护”标题下单独管理的,而现在,它们是在一个伞下-Azure SQL中的“ 高级数据安全”下进行更新和统一的。
In this short article series, we will gain an in-depth understanding of the following topics that come under Advanced Data Security in Azure SQL.
在这个简短的系列文章中,我们将深入了解Azure SQL的高级数据安全性下的以下主题。
- Data Discovery & Classification (Preview feature) 数据发现和分类(预览功能)
- Vulnerability Assessment 漏洞评估
- Advanced Threat Protection 先进的威胁防护
前提条件 (Pre-requisite )
This article assumes that the reader is accustomed to create a basic Azure SQL Database with minimal configurations through the Azure portal. In case, you are unaware of how to create it, I would recommend going through this great article, Microsoft Azure SQL Database – Step by Step Creation tutorial.
本文假定读者习惯于通过Azure门户以最少的配置创建基本的Azure SQL数据库。 如果您不知道如何创建它,建议您阅读这篇很棒的文章《 Microsoft Azure SQL数据库-逐步创建教程》 。
I have deployed database AdventureWorksDW2017 from on-premise to Azure SQL database and will be using it to demonstrate and simulate Advanced Data Security features. If you want to go over steps to restore any database in Azure SQL, you can refer to this article, Microsoft Azure, our first steps to migrate data.
我已将数据库AdventureWorksDW2017从本地部署到Azure SQL数据库,并将用于演示和模拟高级数据安全性功能。 如果要执行在Azure SQL中还原任何数据库的步骤,可以参考本文Microsoft Azure,这是我们迁移数据的第一步。
Before we go ahead and talk more on the security capabilities, let’s quickly enable the Auditing settings to have complete investigation experience while handling security. Go to the Auditing blade and toggle Auditing switch from OFF to ON. Select Storage and configure basic Storage details for audit logs to get saved and select the save button as shown below. Whenever a vulnerability assessment or threat protection scan happens, logs are stored in this storage account and are subsequently used for alerts. We will talk about these scans in detail in the subsequent part.
在继续讨论安全功能之前,让我们快速启用“ 审核”设置,以在处理安全性时获得完整的调查经验。 转到审核刀片并将审核开关从OFF切换到ON。 选择存储并配置基本的存储详细信息以供审核日志保存,然后选择保存按钮,如下所示。 每当进行漏洞评估或威胁防护扫描时,日志就会存储在此存储帐户中,并随后用于警报。 我们将在下一部分中详细讨论这些扫描。
Small note – This Azure SQL DB (AdventureWorksDW2017) is restored in the server named sqlshackdemoserver and resource group sqlshackdemo_rg as shown below in the Overview blade in the Azure portal.
小笔记–此Azure SQL DB(AdventureWorksDW2017)在名为sqlshackdemoserver和资源组sqlshackdemo_rg的服务器中还原,如下面Azure门户的“概述”刀片中所示。
Once it is enabled, all three features supported by Advanced Data Security become accessible as shown in the screenshot below. Let’s begin with the first feature in this capability.
启用后,可以访问Advanced Data Security支持的所有三个功能,如下面的屏幕快照所示。 让我们从此功能的第一个功能开始。
数据发现与分类 (Data Discovery & Classification)
Data Discovery and Classification is a preview feature of Advanced Data Security. As the name suggests, it offers several services for discovering and classifying sensitive data to protect sensitive data by using sensitivity labels and Information Type in the database. We will demonstrate this definition shortly. This is implemented at the column level and also known as SQL Information Protection.
数据发现和分类是Advanced Data Security的预览功能。 顾名思义,它通过使用数据库中的敏感度标签和信息类型,提供了几种发现和分类敏感数据的服务,以保护敏感数据。 我们将在短期内演示此定义。 这是在列级别实现的,也称为SQL信息保护。
With General Data Protection Regulation (GDPR) imposed to protect the private information of individuals, there is a high need for the business to be GDPR compliant, otherwise, any breaches in these guidelines result in harsh fines. This feature helps to meet GDPR regulations by identifying and classifying the columns that potentially contain sensitive data in the database.
为了保护个人私人信息而实施的通用数据保护条例( GDPR ),非常需要使企业符合GDPR的要求,否则,违反这些准则将导致严厉的罚款。 通过识别和分类数据库中可能包含敏感数据的列,此功能有助于满足GDPR法规。
Click on the Data Discovery & Classification (preview) pane to open it and let’s move ahead to see this in action on the Azure portal. By default, nothing is identified and classified on this page. We will click on the notification in the blue bar that specifies classification recommendations as shown below. These classifications are scanned by the classification engine in the database to list all the columns that contain potential confidential data.
单击“数据发现和分类(预览)”窗格将其打开,然后继续进行操作,以在Azure门户上看到正在执行的操作。 默认情况下,此页面上没有任何标识和分类。 我们将单击蓝色栏中的通知,该通知指定分类建议,如下所示。 这些分类由数据库中的分类引擎扫描,以列出包含潜在机密数据的所有列。
You can see the classification report generated with a lot of details like schema name, table names, column names, information type and Sensitivity label for each column that this tool considers as sensitive data. Recommendations on Information Type like name, financial, contact info, credit card, etc. and Sensitivity labels like Confidential, Confidential – GDPR are listed in the dropdown as shown in the screenshot below.
您会看到生成的分类报告,其中包含许多详细信息,例如该工具视为敏感数据的每一列的架构名称,表名称,列名称,信息类型和灵敏度标签。 下拉列表中列出了有关信息类型的建议,例如姓名,财务,联系方式,信用卡等,以及敏感性标签(例如机密,机密– GDPR),如下面的屏幕快照所示。
You can select one or more recommended columns and click the ‘Accept selected recommendations’ button to apply these columns as classified.
您可以选择一个或多个推荐的列,然后单击“接受选定的推荐”按钮以将这些列应用为已分类。
We can make changes to the default recommendations of Information Type and Sensitivity label. I have made a couple of changes to the below three columns and also edited type and label accordingly. Additionally, this tool gives the liberty to make classification per the business needs and we are not obliged to go just by recommendations. Hit the Save button to save these classification changes.
我们可以更改“信息类型和灵敏度”标签的默认建议。 我对以下三列进行了几处更改,并相应地编辑了类型和标签。 此外,该工具还可以根据业务需求进行分类,因此我们不必仅仅依靠建议。 点击保存按钮以保存这些分类更改。
Once these changes are saved, we can see the updated report in the Overview pane of Data Discovery & Classification feature. This gives the summary of the no. of classified columns and also the no. of tables that contain sensitive data.
保存这些更改后,我们可以在“数据发现和分类”功能的“概述”窗格中看到更新的报告。 这给出了编号的摘要。 分类列的数量以及 包含敏感数据的表。
手动添加自定义分类 (Adding custom classifications manually)
Apart from the recommendation made by this tool, you can also manually add classifications by using the ‘Add classification’ button on the top menu bar. Select schema, table, column, information type and label in the context window and hit Add classification at the bottom as shown below.
除了此工具的建议之外,您还可以使用顶部菜单栏上的“添加分类”按钮来手动添加分类。 在上下文窗口中选择架构,表,列,信息类型和标签,然后单击底部的“添加分类”,如下所示。
You can see the custom classification being added in the bottom row, and click save button to save this newly classified column.
您可以在底部行中看到自定义分类,然后单击“保存”按钮以保存此新分类的列。
使用T-SQL添加自定义分类 (Adding custom classifications using T-SQL)
With this, we come to the last leg of this article, this feature provides one more option to manage column data classification using T-SQL. We can add and remove these classifications and also check all the classifications available in the entire Azure SQL database using T-SQL queries.
有了这个,我们进入了本文的最后一步,此功能提供了另一个选项,可以使用T-SQL管理列数据分类。 我们可以添加和删除这些分类,还可以使用T-SQL查询检查整个Azure SQL数据库中所有可用的分类。
添加灵敏度分类 (Add sensitivity classification)
We can add the sensitivity classification to one or more columns in a database with information type and label. Identifying and discovering such sensitive columns and classifying them helps to achieve better data protection. Let’s say we want to categorize columns YearlyIncome and BaseRate in dimcustomer table as Highly Confidential and EmailAddress column in dimemployee table as Confidential. Execute the below code in the new query window to add below classifications.
我们可以将灵敏度分类添加到具有信息类型和标签的数据库中的一列或多列中。 识别和发现此类敏感列并对其进行分类有助于实现更好的数据保护。 假设我们要将dimcustomer表中的YearlyIncome和BaseRate列归为“高度机密”,而dimemployee表中的EmailAddress列归为“机密”。 在新的查询窗口中执行以下代码以添加以下分类。
ADD SENSITIVITY CLASSIFICATION TO
dbo.dimcustomer.YearlyIncome, dbo.dimemployee.BaseRate
WITH (LABEL='Highly Confidential', INFORMATION_TYPE='Financial')
ADD SENSITIVITY CLASSIFICATION TO
dbo.dimemployee.EmailAddress
WITH (LABEL='Confidential', INFORMATION_TYPE='Contact Info')
Once this is executed, these columns are added and listed in the Overview pane in Data Discovery & Classification.
执行此操作后,将添加这些列并在“数据发现和分类”的“概述”窗格中列出。
Also, one more feature to acknowledge in the above screenshot is the Export button. This tool supports downloading a report in an excel format using the Export button on the top menu bar.
另外,在上面的屏幕快照中要确认的另一个功能是“ 导出”按钮。 该工具支持使用顶部菜单栏上的“导出”按钮以excel格式下载报告。
跌落敏感度分类 (Drop sensitivity classification)
‘DROP Sensitivity Classification’ helps to delete classified data from one or more columns in the Azure SQL database, one such example to drop column classification is shown below.
“ DROP敏感度分类”有助于从Azure SQL数据库的一个或多个列中删除分类数据,下面是删除列分类的示例之一。
DROP SENSITIVITY CLASSIFICATION FROM
dbo.dimemployee.EmailAddress, dbo.dimcustomer.YearlyIncome
检索数据库中的分类 (Retrieve classifications in the database)
You can retrieve all the classifications made to the database using view, sys.sensitivity_classifications. This view lists all the classified columns with their corresponding details like class, id, label, type etc.
您可以使用sys.sensitivity_classifications视图检索对数据库所做的所有分类。 该视图列出了所有分类列及其相应的详细信息,例如类,id,标签,类型等。
SELECT * FROM sys.sensitivity_classifications
结论 (Conclusion )
We explored one of the new components of Advanced Data Security available with Azure SQL Database in this article. This feature is a potential way to exhibit private data security. Since this feature – Data Discovery & classification is in preview mode, we can expect a lot more new advancements in the near future from the SQL Azure team.
本文探讨了Azure SQL数据库提供的高级数据安全性的新组件之一。 此功能是展现私有数据安全性的一种潜在方法。 由于此功能–数据发现和分类处于预览模式,因此我们可以期望SQL Azure团队在不久的将来会有更多的新进展。
敬请关注 (Stay tuned)
Keep a tab on the next block to continue your learning on two other important aspects of data security in Azure SQL – Vulnerability Assessment and Advanced Threat Protection.
保持选中状态,继续学习Azure SQL数据安全的另外两个重要方面- 漏洞评估和高级威胁防护 。
目录 (Table of contents)
Advanced Data Security in Azure SQL Database – Data Discovery & Classification |
Vulnerability Assessment and Advanced Threat Protection in Azure SQL Database |
Azure SQL数据库中的高级数据安全性–数据发现和分类 |
Azure SQL数据库中的漏洞评估和高级威胁防护 |
翻译自: https://www.sqlshack.com/advanced-data-security-in-azure-sql-database-data-discovery-classification/
azure云数据库