Here I am talking about SQL Data Discovery & Classification feature that is built into Azure SQL Database. With this feature you have the ability to classify your database, which is what I will do today. There are 2 attributes to classification which are important components. These are labels and information types. Labels are used to define the sensitivity level of the data stored in the column and information types being the type of data stored in the column.
To start this process, you need to navigate to the security section of your SQL Database, it is actually within Advanced Threat Protection.
This needs to be enabled, it does cost but after the trial period expires.
As you can see below it will return a list of recommended columns to classify with the type and label.
While the classification was running I executed the famous sp_WhoIsActive (http://whoisactive.com/) command where you can clearly see the DcService running, well I guess that is the Data Classification Service.
Whilst connected to the database via SSMS you can issue the below query to get a really good summary of classification, basically the outcome of the process above.
SELECT sys.all_objects.name AS TableName, sys.all_columns.name As ColumnName, Label, Information_Type FROM sys.sensitivity_classifications left join sys.all_objects on sys.sensitivity_classifications.major_id = sys.all_objects.object_id left join sys.all_columns on sys.sensitivity_classifications.major_id = sys.all_columns.object_id and sys.sensitivity_classifications.minor_id = sys.all_columns.column_id
You can use TSQL to add new labels and information types for your classification purposes. For example, via SSMS I execute the following:
ADD SENSITIVITY CLASSIFICATION TO [dbo].[ErrorLog].[UserName] WITH ( LABEL='Highly Secret', INFORMATION_TYPE='CIA' )
Checking this via the Azure portal confirms the change.
Pingback: Dew Drop – February 4, 2019 (#2891) | Morning Dew