In this article we will continue with the demo examples of the types of Random Data Hide and Partial Data Concealment.

1. Apply random concealment

Random concealment is to replace numeric values ​​with a random value in a specified range.

Apply shading to the Age column. In this case the designation range is 1 to 99.

Run the SELECT statement with the account privileged.

Re-run the SELECT statement with the account Linhdemo2. Now the return value is between 1 and 7.

2. Apply partial concealment

Partial hiding allows us to specify exactly what value is to be hidden. This function works best for consistently formatted column data, such as credit card numbers or country IDs. To implement the partial cloaking rule, we must specify three arguments: how many characters are first to reveal, how many characters are in between and how to mask them, how many last to reveal.

Apply shielding to the phonenumber column.

In this case, we reveal the first 1 character ( 1 ), hide the first four characters with the value x ( xxxxxx ) and reveal the last character ( 1 ).

Run the SELECT statement with the account privileged.

Re-run the SELECT statement with the account Linhdemo2.

Now that we have all the cloaking rules in place, let’s query the sys.masked_columns system view to see the hidden columns in our database and how to implement that cloaking.

3. REFERENCES

1. https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017
2. https://www.slideshare.net/antoniosch/dynamic-data-masking-sql-server-2016
3. https://www.imperva.com/blog/static-vs-dynamic-data-masking-why-are-we-still-comparing-the-two/
4. https://www.sentryone.com/blog/johnmartin/dynamic-data-masking-my-thoughts
5. https://www.sqlhelps.com/blog/sql-server-2016-data-masking-and-always-encrypted/