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