Data security is always one of the important points which can not 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 a dynamic mask encryption.
This is one of the best features of SQL SERVER 2016 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 following features:-
1) It masked the Sensitive data.
2) There will be no impact on functions & Stored Procedures and other SQL statement after applying this.
3) Applying the Data Masking is super easy.
4) You can allow any database user/role to see unmasked data by just simple Grant & Revoke Statement .
5) Data is not physically changed.
6) It is just on the fly obfuscation of data query result .
7) 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.
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 2016 has 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 you 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 mask 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 then with 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 a masking to a column and later on you don’t want that masking. So , don’t worry it very easy to remove masking from a column. below is the syntax for 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 below figure.
Now, in this table, we are inserting 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
2) Email Masking :- In the table, we are going to apply Email masking to email column below is the syntax for it.
3) Partial Masking:- For SSN we are going to apply custom masking. below is the syntax for same. Here as we aware that SSN is 11 characters long in our database. we applied the partial masking to show first two & last two characters in original value and rest other in the mask.
4) Random Number Masking :- In our table, we are going to apply Random number masking to 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.
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 below screen.
After our successful log in, we will run the select statement on same database’s table as we did earlier. If you see below snap you will find that we got masked data for LastName, Email, SSN, and securePin.
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 mask on the LastName then below is the syntax for same.
Now, let me run the same select statement seeMask_user. You will find the Last Name is unmasked now.
From 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 may like this feature. Please, share your input for same.
Enjoy !!
RJ
Data Masking is one of the best ways to help you to secure your sensitive data by a dynamic mask encryption.
This is one of the best features of SQL SERVER 2016 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 following features:-
1) It masked the Sensitive data.
2) There will be no impact on functions & Stored Procedures and other SQL statement after applying this.
3) Applying the Data Masking is super easy.
4) You can allow any database user/role to see unmasked data by just simple Grant & Revoke Statement .
5) Data is not physically changed.
6) It is just on the fly obfuscation of data query result .
7) 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.
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 2016 has 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 you 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 mask 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 then with 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 a masking to a column and later on you don’t want that masking. So , don’t worry it very easy to remove masking from a column. below is the syntax for 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 below figure.
Now, in this table, we are inserting 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
2) Email Masking :- In the table, we are going to apply Email masking to email column below is the syntax for it.
3) Partial Masking:- For SSN we are going to apply custom masking. below is the syntax for same. Here as we aware that SSN is 11 characters long in our database. we applied the partial masking to show first two & last two characters in original value and rest other in the mask.
4) Random Number Masking :- In our table, we are going to apply Random number masking to 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.
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 below screen.
After our successful log in, we will run the select statement on same database’s table as we did earlier. If you see below snap you will find that we got masked data for LastName, Email, SSN, and securePin.
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 mask on the LastName then below is the syntax for same.
Now, let me run the same select statement seeMask_user. You will find the Last Name is unmasked now.
From 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 may like this feature. Please, share your input for same.
Enjoy !!
RJ
