Security has been one of the main concerns of database developers since the creation of database management systems. Various data protection regulations have been introduced to ensure safe access to sensitive data. read more about To Protect Sensitive Data Dynamic Data Masking In SQL Server.
One of the security features introduced by Microsoft is an impressive new feature in SQL Server 2016 called Dynamic Data Masking (DDM).
Dynamic data masking allows a developer or administrator to decide how much sensitive data to display with minimal impact on the application layer. This feature also simplifies security design and encryption in your application by generating data at the database level.
It is important to note that data masking is not the same as data encryption and should not be used as a primary layer of security.
Only used to cover sensitive information such as social security numbers, email addresses, phone numbers, credit card numbers, etc. The following is an example of dynamic data masking:
- Phone number: xxxx-xxx-xx-3526
- Email: xxxxx@xxxx.com
Data is not physically closed in the database. On the other hand, the data in the query results is obscured. By default, all users will see masked data in the output.
Unmasked data is visible in the actual database. For information on how to view dynamic data masking, go through this article.
Dynamic Data Masking
Dynamic Data Masking (DDM) restricts the disclosure of sensitive data by covering it from unauthorized users. It can be used to make designing and coding security in your application a lot easier.
Dynamic data masking does not alter the actual data stored in the table. It implements a masking function on table columns during return of data as a result of the query. Dynamic data panning supports four data panning functions that you can use to mask data at the database level:
- Default
- Random
- Custom String
Dynamic data masking can be configured in specific database fields to hide sensitive data in the query result set. With DDM, the data in the database does not change.
Dynamic data masking is easy to use with existing applications because the masking rules are applied in the query results. Many applications can mask sensitive data without changing existing queries.
- Central data hiding policies act directly on secret fields in the database.
- Identify special users or roles who have access to sensitive data.
- DDM has full and partial masking functions as well as masking random numeric data.
- Simple Transact-SQL commands define and manage masks.
For example, call center support personnel can identify callers by multiple digits based on their social security number or credit card. Social security numbers or credit card numbers do not have to be fully accessible to supporters.
An incognito rule can be defined that covers all but the last four digits of the social security or credit card number in the result pool of each query. In another example, a developer could use a Personal Data Protection (PII) appropriate data mask to require the production environment to troubleshoot without breaking compliance regulations.
Purpose of DDM
The purpose of DDM is to limit the exposure of sensitive data by preventing users who shouldn’t have access to the data from viewing it. DDM is not intended to prevent database users from directly connecting to the database and performing large-scale queries that reveal portions of sensitive data.
DDM complements other SQL Server security functions (monitoring, encryption, line level protection). It is highly recommended that you use this feature in addition to protecting sensitive data in a database.
DDM Masking function
Four main types of masking functions in Dynamic Data Masking that works by adding the following function with column metadata. These are briefly presented here and used later in demonstrations.
Default Function: It Covers the data according to the field data type. If the field’s data type is binary, varbinary, or image, a byte with a binary value of 0 is used to mask this field.
If the data type of a masked field is one of the numeric data types, a null value is used to mask that field. For string data types, the value XXXX is used to mask this field. If the field is less than 4 characters long, the smaller X number is used to mask the value.
Example for column definitions Syntax:
The phone number varchar (12) MASKED WITH (FUNCTION = ‘default ()’) NULL Example alter syntax: ALTER COLUMN AGE ADD MASKED WITH (FUNCTION = ‘default ()’)
Email Function: Email feature, which is used to mask the fields that store the email addresses. The email function only displays the first characters of the email address and masks other emails as well as aXXX@XXXX.com. Example definition syntax EMail varcharMASKED WITH (FUNCTION = ’email ()’) NULL
Example of alternative syntax:
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ’email ()’)
Random Function: Used to mask any numeric data type, replacing the original value with any value within the range specified in this function.
Example definition syntax: Account_Number bigint MASKED WITH (FUNCTION = ‘random ([end area], [end area])’)
Example of alternative syntax: ALTER COLUMN [day] ADD MASKED WITH (FUNCTION = ‘random (1, 12)’)
Custom String Function: A masking method that displays the first and last letters and adds a custom string for the layer in the middle. Prefix, [padding], suffix
Note: If the original values are too short to complete the entire mask, part of the prefix or suffix will not be displayed.
Example definition syntax: First name varchar (100) MASKED WITH (FUNCTION = ‘partial (prefix, [padding], suffix)’) NULL
Example for alter syntax:
How it works
This is how data masking works:
- Incorrect data masking in query results
- Managed according to table and column guidelines
- Many masking functions are available for various categories of sensitive data
- Flexibility in defining a set of privileged inputs for unmasked data access
- By default, the database owner is not masked
Advantages of dynamic data masking
Dynamic data masking offers the following advantages over conventional approaches:
- Dynamic data masking implements a centralized policy to hide or modify sensitive data in a database that is inherited by any application that wants to access data.
- Dynamic data masking in SQL Server can help privileged users view sensitive data and users who are not allowed to view it.
- There is a simple implementation in the form of a T-SQL script.
Conclusion:
The dynamic data masking feature in SQL Server 2016 allows users to mask data at the database level without changing or obscuring the actual data stored in the table.
We can say that this feature gives DBAs the advantage of allowing them to hide sensitive data from a group of less privileged users. This feature avoids the added hassle of blurring or masking data when a vendor visits your company to fix database-related problems.
In this article we have looked at some examples of how dynamic data masking is performed. A DDM feature was introduced in SQL Server 2016 to improve customer data security.
There are four types of masks available in SQL Server: default, Partial, Random, and Email. We have seen all about them.