How To Add Data Sensitivity Classification Command in SQL Server 2019

For a database administrator, the common everyday practice involves running multiple operations targeted at ensuring database security and integrity. Thus, we shouldn’t overlook the importance of sensitive data stored in the database under any circumstances. In light of this, we are excited to demonstrate the new ADD SENSITIVITY CLASSIFICATION command introduced in SQL Server 2019, which allows adding the sensitivity classification metadata to database columns.

What’s the big deal about data protection?

There are numerous types of applications that store sensitive information both for users, such as credit card numbers, passwords, health care information, IDs, SSN, and other applications like credential data, trade secrets, certificates.

The leak or breach of such information can lead to horrific consequences as companies might be forced to pay millions of dollars in damage compensation to customers and financial institutions.

To be compliant with regulations for personal data such as GDPR or healthcare data (HIPAA), the company needs to acquire the best practices of data security and protection.

What the command offers

The most sensitive data in your database mainly refers to the business, financial, healthcare, or personal information. To establish a high level of your organization data protection, the key steps you should undertake are to discover the sensitive data and then to classify it. This is where the new command will show to its best advantage.

Above all, it will help you meet the standards for data privacy and requirements for regulatory compliance. Additionally, with its help, you can implement several scenarios to monitor (audit) and alert on anomalous access to sensitive data. Finally, you will be able to toughen the security of databases containing highly sensitive data and manage access to them.

Summarizing the above, one of the pivoting points in compliance practice is to know which data has to be secured, classify this data, give access to only a limited number of people allowed to view or modify it, and continuously monitor access to your sensitive data to know all access patterns.

How the ADD SENSITIVITY CLASSIFICATION command works

For starters, let me remind you that a similar feature – Data Discovery and Classification was introduced into SSMS v17.5. As well as the ADD SENSITIVITY CLASSIFICATION command, the SSMS wizard allows classifying data and labeling it with sensitivity tags. To learn the details and the differences between these two, refer to our article about SQL Data Discovery and Classification in SSMS.

Let’s now talk about the ADD SENSITIVITY CLASSIFICATION command in greater detail. Hence, this section will deal with the most important issues related to discovering, classifying, and labeling columns that contain sensitive data in your database, along with viewing the current classification state of your database.

Below are the two metadata attributes used in the classification of sensitive data:

Labels are the main classification attributes. Their task is to define the sensitivity level of the data stored in the column. You can indicate your data as being Public, General, Confidential, Highly Confidential, etc.

Information Types give additional description of the type of data stored in the database column. They indicate the field your sensitive data refers to, whether it is Banking, Contact Info, Credentials, Financial, or else.

Rank defines the sensitivity rank and ranges from none to critical, as shown below:

SQL Syntax

To add sensitivity classification to a database object, simply apply the following syntax:

ADD SENSITIVITY CLASSIFICATION TO
    <object_name> [, ...n ]
    WITH ( <sensitivity_option> [, ...n ] )

<object_name> ::=
{
    [schema_name.]table_name.column_name
}

<sensitivity_option> ::=  
{
    LABEL = string |
    LABEL_ID = guidOrString |
    INFORMATION_TYPE = string |
    INFORMATION_TYPE_ID = guidOrString |
    RANK = NONE | LOW | MEDIUM | HIGH | CRITICAL
}

Let’s consider the following example:

As well as that, SQL Server 2019 introduced the sys.sensitivity_classifications system catalog view, which returns information types and sensitivity labels. You can use it to manage the database classifications, as well as to generate reports. With the limitation that the classification is supported only for columns.

Use the following query to review all classified columns with the corresponding classifications:

SELECT
    SCHEMA_NAME(sys.all_objects.schema_id) as SchemaName,
    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

See the example output below:

Database Audit

Database audit involves analyzing and tracking the activity of database users related to database security, access and usage, data creation, change or deletion. Auditing is an essential part of database security because oftentimes database administrators and consultants have to make sure the permission to access data is only given to those who need it and not otherwise.

There’s no denying that the most critical part of any organization is its data. There can be many users who might have permission to manipulate data, and it is extremely important that all confidential and restricted data not be edited by unauthorized users.

Applying the ADD SENSITIVITY CLASSIFICATION command, you can quickly and easily detect the most vulnerable data and classify it. After that, the classification state is added to the audit log, which helps monitor access to sensitive data for compliance and auditing purposes.

ADD SENSITIVITY CLASSIFICATION in SQL Complete

If you want to keep up with the latest changes introduced in SQL Server 2019 and find an appropriate tool for that, take a look at SQL Complete v6.6. The tool offers auto-completion functionality and is intended for SQL database development, management, and administration. Also, SQL Complete 6.6 supports the ADD SENSITIVITY CLASSIFICATION command. 

SQL Complete 6.6 enables you to easily classify database columns by prompting sensitivity labels that show the vulnerability of the data in the database column. With the tool’s suggestions, you can quickly and effortlessly tag columns according to the data sensitivity level.

Aside from the sensitivity label, a column may have another attribute – Information Type, which provides additional granularity to the type of data stored in the database column. Again, quick and comprehensive prompts by SQL Complete 6.6 significantly facilitate data classification.

In the suggestion window, SQL Complete 6.6 marks columns containing personal or confidential information according to GDPR with black or red circles depending on the sensitivity degree.

Summary

On that note, let’s outline the main capabilities and advantages the new feature has to offer. Add sensitivity classification is a powerful enhancement introduced into SQL Server 2019 bound to improve database security and compliance with data protection rules. With its help, you can easily discover the columns that contain potentially sensitive data, create reports as well as add the classification metadata. By using the command, you are sure to facilitate database audit and bring access to your sensitive data under control.

Previously published at https://blog.devart.com/add-sensitivity-classification-command-in-sql-server-2019.html