How to configure Dynamic Data Masking?
Microsoft introduced with SQL Server 2016 new feature - Dynamic Data Masking. This feature can be useful if you don't want someone to see sensitive data. Let's say that your company hires DBA contractor for 3 months, but IT Security guy doesn't want to he couldn't see a number of credit cards of our customer. For this purpose, we can use this masking function, be aware that DDM don't use encryption method but masking method.
Dynamic Data Masking supports four types of mask:
1 ) Default - The masking depends on data types.
Binary data types - binary, varbinary and image
Numeric data types - int, money, numeric, float, real and etc...
Time and date data types - date, datetime2, datetimeoffset, , time, etc..
String data types - char/nchar, varchar/nvarchar, text/ntext
2) Custom string - This type show you only first and last letter and add random string in the middle.
3) Random - This method masks random value within specified range of numeric type.
4) Email - It allows display for example first letter, at(@) and .com, such as hXXXX@XXXX.com
DDM doesn't support following:
Always Encrypted columns
Filestream data
Computed columns
You cannot FULLTEXT index where DDM is implemented.
Etc...
Implementation of Dynamic Data Masking via T-SQL looks like this.
Now look at Email column before and after configure DDM.
As you can see for non-privileged user named Pigeon are data masked in Email column!
For more info visit official documentation on MSDN.
See you later Alligator.