SQL Security Tips - Dynamic Data Masking To Secure Data Access

Introduction

Data security is always one of the most important points which cannot be ignored. Nowadays, if you are working for any specific domain like Banking or Healthcare, then there are a lot of compliance rules which you have to follow. Data Masking is one of the best ways to help you secure sensitive data through dynamic mask encryption.

This is one of the best features of SQL SERVER, which I like most.

With the help of Dynamic Data Masking, you are just applying a mask to your sensitive data. For example, if your system stores SSN data, it should be visible to privileged or, we can say, authorized users only.

Features of Dynamic Data Masking

• It masks Sensitive data.
• After applying this, there will be no impact on functions & Stored Procedures, and other SQL statements.
• Applying the Data Masking is super easy.
• You can allow any database user/role to see unmasked data with just a simple Grant & Revoke Statement.
• Data is not physically changed.
• It is just the fly obfuscation of data query results.
• It is just a T-SQL command with basic syntax.

Now, let us understand how to implement it.

Data masking implementation is straightforward; below is its syntax.

The syntax is very simple; the only new thing is MASKED and with (function=function name) only.

The function is nothing but a way to mask the data. SQL SERVER has the following different functions to mask the data

1. Default() function
   This is basic masking. With the help of this function, you can easily mask any field. For example, your first name or last name field can be masked, like XXXX, etc. 
2. Email() function
   If your column is email type, or we can say if you store Email in your column, then you should use the Email() function for masking. For example, your email can be masked like [email protected]. 
3. Partial () function
   With the help of this function, you can mask specific data lengths and exclude some parts of data from masking logic. For example, 123-4567-789 is your phone number with a partial masking feature you can mask like 12X-XXXX-7XX. 
4. Random() function
   By the name, it is clear that you can mask the data with any random number range. We will see more below in the hands-on.

Remove Masking

You could also apply the mask to a column; later on, you don’t want that masking. So, don’t worry. It is very easy to remove masking from a column. Below is the syntax for the same.

Now, let’s understand this by an example.

In the example, we use a new database, “SecureDataMask.” In this database, we are creating a tblSecureEmployee, as shown in the below figure.

In this table, we insert a couple of data for testing, as shown below.

Now we are applying different masking on this table’s column,

Default Masking

In the table, we are using default masking on LastName

Email Masking

In the table, we will apply Email masking to the email column. Below is the syntax for it.

Partial Masking

For SSN, we are going to apply custom masking. As we know that SSN is 11 characters long in our database, we applied partial masking to show the first two & last two characters in the original value and the rest in the mask. Below is the syntax for the same.

Random Number Masking

In our table, we will apply Random number masking to the Securepin column, as shown below.

Here, so far, we are done with all the masking now. Let me run the select statement to test it.

The data is still in the original state because I logged in using the privileged account “SA.” To test the masking, let me create a new user account.

After creating the account, we try to log in with a new one, as shown in the screen below.

After our successful login, we will run the select statement on the same database table as we did earlier. If you see the below snap, you will find that we got masked data for LastName, Email, SSN, and securePin.

It might be a rare case, but suppose you want to remove the mask from any column you applied masking, so don’t worry; it is super easy.

Suppose, from the same table, we don’t want a mask on the LastName then below is the syntax for the same.

Now, let me run the same select statement seeMask_user. You will find the Last Name is unmasked nowYouanges; you can secure your data via Dynam from the above few changes masking. As mentioned above, there will be no impact on your existing function or stored procedure because data is not physically changed.

Summary

This article taught us about Dynamic Data Masking To Secure Data Access. I hope you like this feature. Please, share your input for the same.

Enjoy!