SQL Dynamic Data Masking

sensitive information is often stored in the database, and it should only be available to specific people on a need-to-know basis. Beyond that need, the sensitive data should not be exposed via the application, or to developers or IT personnel who access the production database directly.

By considering this factor MS SQL server has implemented a couple of new features in SQL Server 2016. Out of those, I would like to talk about Dynamic Data Masking.

What is Dynamic Data Masking (DDM).

Dynamic Data Masking rules can be defined in particular columns, indicating how the data in those columns will appear when queried. There are no physical changes to the data in the database itself; the data remains intact and is fully available to authorized users or applications. Database operations remain unaffected, and the masked data has the same data type as the original data, so DDM can often be applied without making any changes to database procedures or application code.

How To implement DDM on SQL Server.

To add a data mask on a certain column in your database, all you need to do is alter that column by adding a mask and specifying the required masking type.

There are 3 types of masking methods

default
email
partial

Let me Explain above four functions using and practical example. Consider you have a user table and it contains names and emails which is sensitive. Table as below.

The above table can be created using the following code.

1). Default Masking

This masking method will replace all the data into XXX value and obstruct all the data.

Rules Behind obfuscation of default masking

For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text, ntext).
For numeric data types use a zero value (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).
For date and time data types use 01.01.1900 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time).
For binary data types use a single byte of ASCII value 0 (binary, varbinary, image).

2). Email

This masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address.

Ex:

aXXX@XXXX.com

3). Partial

This method will allow to masking where you can specify part of the data to expose.

Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix

Demo

Alter the above created table using the following code

and after that have to create a user to view the data. To create the user using the following code. after that login to the user after that, you can see the output like this for that user.

Note

If you use this user to pull the data from a programming language or querying the data will show only as masked data,

SQL Dynamic Data Masking