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), companies need 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. 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 three metadata attributes used in the classification of sensitive data:
Label is the main classification attribute. Its 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 Type gives additional description of the type of data stored in the database column. It indicates 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_idSee 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’s 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 dbForge SQL Complete
The Devart team is committed to keeping up with the latest changes in SQL Server, so the support for the new command was added to SQL Complete with the v6.6 release. The tool is a superior solution for SQL database development, management, and administration with great auto-completion functionality. The support for the ADD SENSITIVITY CLASSIFICATION command is one of its beneficial updates. Let’s overview how the new feature works in SQL Server Management Studio (SSMS) with SQL Complete.
SQL Complete 6.6 (and later versions) enables you to easily classify database columns according to the data sensitivity level by prompting sensitivity labels that show the vulnerability of data in the database column.
Aside from the sensitivity label, a column can 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 significantly facilitate data classification.
In the suggestion window, SQL Complete marks columns containing personal or confidential information according to GDPR with black or red circles depending on the sensitivity degree.
ADD SENSITIVITY CLASSIFICATION in dbForge Studio for SQL Server
dbForge Studio for SQL Server—a comprehensive integrated development environment (IDE) designed for SQL Server database management and development—also obtained support for the ADD SENSITIVITY CLASSIFICATION command with the release of v7.0, bringing a new level of efficiency to data classification processes. This feature seamlessly integrates sensitivity label prompts into the completion list and quick info, enabling swift and accurate data classification directly within the SQL Server environment.
By supporting the ADD SENSITIVITY CLASSIFICATION command, dbForge Studio for SQL Server v7.0 (and later versions) simplifies the process of SQL Server data classification. The tool prompts sensitivity labels and information types, allowing users to efficiently tag columns based on data sensitivity levels and information types. This enhances data security, compliance, and auditing capabilities, enabling organizations to better protect sensitive data and meet regulatory requirements.
Summary
On that note, let’s outline the main capabilities and advantages the new feature has to offer. The ADD SENSITIVITY CLASSIFICATION command is a powerful enhancement introduced in SQL Server 2019 that will 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.
