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.
Смотрите видео SQL Server 2016 (New feature): How to use Dynamic Data Masking онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь HandyAndy Tech Tips 29 Июнь 2016, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 8,187 раз и оно понравилось 97 людям.