Dynamic data masking in SQL SERVER

In our day to day life we are more concern about our data security and sensitive information’s when we fill out some purchase form and other billing application which requires your Credit card and other personal information. This data can be viewed easily in the backed users if it’s not secure controlled.

SQL Server 2016 provides a feature known as Dynamic Data Masking which is a security feature that limits the access of unauthorized users to sensitive data at the database layer.

As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.

Dynamic Data Masking, also known as DDM, is a simple security data protection method allows you to determine your “sensitive” data, by field in order to configure the suitable masking function to hide it from queries. This feature requires no coding effort from the application side or encrypting or applying any change to the real data stored in the disk.

Features:

• Provides configurable masking policy using simple T-SQL command.
• Database is not changed physically and database operations are not affected.
• Flexibility to define a set of privileged SQL users or roles for unmasked access.

Types of Masks:

There are 4 types of masks are offered by SQL Server 2016.

1. Default Mask
The default mask masks the full data according to the assigned data type.

• For string types (char, nchar, varchar, nvarchar, text, ntext), it replaces the data with XXXX or fewer Xs, in case size of field is less than 4.
• For numeric datatypes (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real), it uses 0 value.
• For binary datatypes (binary, varbinary, image), it uses a single byte of binary value 0.

2. Email Mask
The email mask especially for the fields that stores emails. It exposes only the first letter of email followed by XXX, followed by @ sign, followed by XXXX and a constant suffix “.com” to form an email. E.g. [email protected]

3. Custom Mask
SQL Server 2016 allows you to define your own mask for a specific field. In this method, you can define the prefix and suffix characters to be exposed, and the padding will be shown as it is. “prefix,[padding], suffix”.

4. Random Mask
Random mask can be defined over any of the numeric fields. It exposes a random number within the defined range.

Example

1. Create a table with different mask functions

CREATE TABLE [dbo].[Contacts]
(
	[ID] [int] IDENTITY(1,1) NOT NULL Primary key,
	[FName] [nvarchar](30) MASKED WITH (FUNCTION = 'default()') NOT NULL,
	[LName] [nvarchar](30) NOT NULL,
	[CreditCard] [varchar](20) MASKED WITH (FUNCTION = 'partial(2, "XX-XXXX-XXXX-XX", 2)') NULL,
	[SalaryINR] [int] MASKED WITH (FUNCTION = 'default()') NULL,
	[OfficeEmail] [nvarchar](60) MASKED WITH (FUNCTION = 'email()') NULL,
	[PersonalEmail] [nvarchar](60) NULL,
	[SomeDate] [datetime] MASKED WITH (FUNCTION = 'default()') NULL
)

Insert Some data in the table

2.  Create a user with only select permission.

CREATE USER [ReadOnlyUser] WITHOUT LOGIN;
GRANT SELECT ON Contacts TO ReadOnlyUser;

3.  Now select the data with the Existing using and the new User that is created

The data will be masked for the ReadOnlyUser.

Conclusion:

This technique allows a developer to debug the Production environment without breaching security or confidentiality for a user.