How To Implement SQL Server Dynamic Data Masking?
SQL Server 2016 added a new data protection feature known as Data Masking in SQL Server. It allows user to limit the sensitive data by masking it to unauthorized users. User can hide the sensitive data without changing the data of the database, since masking is applied to the query result. It allows users to mask the sensitive column of a table present in SQL Server 2016 Database. Dynamic Data Masking in SQL Server Database is useful for regulatory compliance, as protection of sensitive data may be demanded by the government or by any other industrial body.
For example, a support person may identify their customers by their credit card number or by social security number. However, those numbers are exposed partially to the support person in case of Data Masking.MS SQL Server Dynamic data masking can be applied here to mask all the numbers except the last 4 digits. For example an executive can view the credit card number as XXXX-XXXX-XXXX-1234.
How to define Dynamic Data Masking in SQL Server?
You can define the Dynamic data masking rule on a column to protect your sensitive data. There are mainly four types of mask that are available for SQL Server 2016. They are; Default, Custom String, Email and Random.
You can apply full mask according to the data type of fields.
All the letters of emails are masked except the first letter,@ and the suffix .com. For example email@example.com
Expose first and last letters and masking is performed on middle part. For example Prefix, MASK, Suffix
Random masking can be used for any numeric type, to mask the real value with random values.
Limitations of MS SQL Server Dynamic Data Masking
Dynamic data masking rule cannot be applied on following column types:
- Encrypted columns
Setting Up Dynamic Data Masking in MS SQL Server Database
Step 1: Create a new database Masking Demo. If masking Demo database already exists then drop the database and create it again.
Create new database
Step 2: Create Table with different data types of columns.
Step 3: Insert data into row for testing
Step 4: Apply masking
Step 5: Create a test user and test permissions to the table
How to Change or Remove a Mask?
To change the Mask definition of an existing column, run the alter column command.
Alter Table MyContacts Alter Column Credit Card
Add Mask With (Function =’partial(1,XXX-XXXX-XXXX-XXX,1)’);
To remove the applied mask, use Drop Masked
Alter Table MyContacts Alter Column Credit Card Drop Masked;
Hence, Dynamic Data Masking in SQL Server allows users to protect their sensitive data according to their requirement by concealing the confidential data present in table of SQL Server. However, you should also keep in mind that data masking is not the true encryption and it will not protect your data in all scenarios.