SQL Server 2016 (New feature): How to use Dynamic Data Masking
Want to selectively hide or scramble sensitive data? In this video, I'll show you how to use a new feature/tool in SQL Server 2016, called Dynamic Data Masking, that allows you to partly obfuscate certain columns of data in a table or view.
--
Hey, if you enjoyed this video, please SUBSCRIBE to HandyAndy Tech Tips!
--
My notes on this topic:
Now, to add the masking, you COULD define the masking in the table definition statement, but I'll use the ALTER TABLE statement. Anyway, the basic syntax is the same: MASKED WITH (FUNCTION = '').
The string can basically be one of four functions.
The easiest is default() - everything is automatic. It:
---- on a TEXT FIELD, replaces the original value of the field with 4 X's.
---- on a DATE FIELD, set s the date to 1900-01-01 00:00:00.00
---- on a NUMERIC FIELD, sets the value to 0.
email() only shows the first letter, and replaces the remainder of the field with X's, an @ symbol, and a standard .com suffix. It, of course, also works on fields that don't include actual email addresses - so it therefore works on any VARCHAR or CHAR.
partial() - When used as partial(no-of-chars-to-reveal-before, padding, chars-to-reveal-after), it reveals the first character and the last two, separated by a string of X's. This also only works on strings - not on numeric types or dates.
random(lower-bound, upper-bound) replaces the field's original value with a randomly-generated number between 1 and 10. This, of course, only works with numeric data types like INT.
Now, it's worth noting that if you want to give a particular user - or role - the ability to see all of the data in its unmasked form, then you can simply grant them the UNMASK privilege. Annoyingly, this doesn't seem to work on a table level, only for the entire database.
Watch video SQL Server 2016 (New feature): How to use Dynamic Data Masking online without registration, duration hours minute second in high quality. This video was added by user HandyAndy Tech Tips 29 June 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 8,187 once and liked it 97 people.