Dynamic Data masking in SQL Server

Sometimes ah, when we store the data in the database, need to be clear, but for the front desk query out, but also do not want to clear the text out of the time (for example, customer's cell phone Ah, mailbox AH) Such a bit sensitive information, before usually save a clear text, and then in the foreground to show the time of special treatment, And then it's OK.

And now, in 2016, it provides the function of data mask, which can obscure some basic information of the corresponding column.

Without further ado, do a sample first

1CreateTable T2 (IDIntIdentity,namenvarchar), PWDvarchar), Phonevarchar), Emailvarchar200))23Go4InsertIntoT2 (Name, PWD, Phone, Email)5Select‘Tom‘,‘Zhangsan‘,‘12433281924‘,‘[Email protected]‘6UnionAll7Select‘John doe‘,‘Wojiaolisi‘,‘18734612309‘,‘[Email protected]‘8UnionAll9Select‘Harry‘,‘Wangwu‘,‘15378198234‘,‘[Email protected]‘10UnionAll11Select‘Sixgod‘,‘Godsixzhao‘,‘00834123642‘,‘[Email protected]‘1213Select*FromT21415ID Name PWD Phone Email16----------- -------------------------------------------------- -------------------- -------------------------------- ------------------ --------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------17 @qq .com 18 2 John Doe Wojiaolisi 18734612309 lisi @sohu .com 19 3 Harry Wangwu 15378198234 wangwu@21cn.com 20 4 sixgod Godsixzhao 00834123642 sixgod @qq. com        

Test Table Setup

Then we do a mask on a column of the table. Let's say we have a mask for the password to use the following statement

Alter TableT2Alter columnPwdAddMasked with(function='default ()'then I create a user Test1 and then grant only one query permission to him. Run as followsExecute  asLogin='Test1'Select *  fromt2id Name PWD Phone Email----------- -------------------------------------------------- -------------------- ---------------------------- ---------------------- ----------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- 1Zhang San xxxx12433281924Zhangsan@qq. com2John Doe xxxx18734612309Lisi@sohu. com3Harry xxxx15378198234Wangwu@21cn. com4Sixgod xxxx00834123642Sixgod@qq. com

The default method is to replace the entire column with XXXX to display. (This happens only when the query is made, and the data is not actually modified to XXXX). This is a method of mask.

2016 provided to 4 kinds of masks, the above has been shown to one, there are 3 kinds of email (), ramdom (), Custom masking these 3 kinds, the following to demonstrate

Alter TableT2Alter columnEmailAddMasked with(function='Email ()')Alter TableT2Alter columnIdAddMasked with(function='Random (1000,9999)')Alter TableT2Alter columnPhoneAddMasked with(function='Partial (4, "* * *", 4)')Select *  fromt2id Name PWD Phone Email----------- -------------------------------------------------- -------------------- ---------------------------- ---------------------- ----------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- 7952Zhang San xxxx1243****1924Zxxx@XXXX. com7601John Doe xxxx1873****2309LXXX@XXXX. com5583Harry xxxx1537****8234Wxxx@XXXX. com3190Sixgod xxxx0083****3642Sxxx@XXXX. com

2, e-mail () This function only shows the first e-mail and @, all other use X to mask
3, Random (1000,9999) displays the value from the 1000~9999 generated from the stochastic integer mask

4, partial equivalent to the custom mask mode, the format (prefix reserved digits, mask string, suffix number of reserved digits) This format to create

Does that mean you can't see the original data after adding a mask? Not, it is necessary to use permissions to control

Grant  to User Revoke  from User

As long as you have unmask this permission, then you can see the original data ~ ~ ~
PS: You can see those columns with mask.

Select object_name (object_id), name,masking_function      from sys.masked_columns (no column name)    name    masking_functiont2    ID    random (9999  ) T2    PWD    default() T2    Phone    Partial (4, " **** 4 ) T2    Email    ()

Dynamic Data masking in SQL Server