SQL Security Tips - Dynamic Data Masking To Secure Data Access

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 to secure your sensitive data by dynamic mask encryption.

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

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

Dynamic Data Masking has the following features,

  • It masks the Sensitive data.
  • There will be no impact on functions & Stored Procedures and other SQL statements after applying this.
  • Applying the Data Masking is super easy.
  • You can allow any database user/role to see unmasked data by just a simple Grant & Revoke Statement.
  • Data is not physically changed.
  • It is just on 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 very easy and below is the syntax for it.

SQL Security Tips: Dynamic Data Masking to secure data access

Here, if you see the syntax is very simple, the only new thing is MASKED and with (function=function name) only.

The function is nothing but the 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 RXXXX@XXXX.com
     
  3. Partial () function
    With the help of this function you can mask specific data length and exclude some part 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

This is also possible that you applied mask to a column and 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 are using a new database “SecureDataMask”. In this database we are creating a tblSecureEmployee as shown in the below figure.

Now, in this table, we are inserting a couple of data for testing as shown below

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

1) Default Masking

In the table, we are applying default masking on LastName

SQL Security Tips: Dynamic Data Masking to secure data access

2) Email Masking

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

SQL Security Tips: Dynamic Data Masking to secure data access

3) Partial Masking

For SSN we are going to apply custom masking. Below is the syntax for the same. Here as we are aware 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.

SQL Security Tips: Dynamic Data Masking to secure data access

4) Random Number Masking

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

SQL Security Tips: Dynamic Data Masking to secure data access

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

If you see the data is still in the original state because I logged in using privilege account “SA”. Now, to test the masking let me create a new user account.

After creating the account we are trying to log in with a new account as shown in the below screen.

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.

SQL Security Tips: Dynamic Data Masking to secure data access

Now, it might be a rare case but suppose you want to remove the mask from any column on which you applied masking then 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 now.

From the above few changes, you can secure your data via Dynamic masking and as mentioned above there will be no impact on your existing function, stored procedure because data is not physically changed.

I hope you like this feature. Please, share your input for the same.

Enjoy!