SQL Server->> new features of SQL Server 2016--Dynamic Data masking

Dynamic data masking is designed to prevent sensitive data from being exposed to unauthorized users in the form of minimal overhead and maintenance costs. The Dynamic data masking field used for the table is equivalent to the part of the field data being covered. For example, some sensitive data, such as email or payroll, involving PII, we can use this function. To use this function, first define a group of people (database role) to have permission to look at the data, and grant unmask permissions to the role group. Microsoft offers 4 types of masking for us to choose from: Default,email,random and custom String. Default is the character type is replaced with XXXX, the number is replaced by 0, and the time becomes 1990-01-01. Email is for the email type, except for the first character, the @ symbol, and the other of the. com to replace the X.

Note the place:

1) The Import/export function of SELECT into, INSERT into and database will bring mask into the newly generated table or target table.

2) The data added to the mask is masked in the backup file.

3) The Add Mask field does not prevent updates by users with Update permissions

4) Compatibility mode must be (SQL SERVER 2016)

CREATE TABLEdbo. Dmmtest (Strcol1nvarchar( -) MASKED with(FUNCTION = 'default ()')NULL, Intcol1intMASKED with(FUNCTION = 'default ()')NULL, Dttmcol1datetimeMASKED with(FUNCTION = 'default ()')NULL, Bincol1varbinary( -) MASKED with(FUNCTION = 'default ()')NULL, Emailcol1nvarchar( $) MASKED with(FUNCTION = 'Email ()')NULL, Randomcol1intMASKED with(FUNCTION = 'random (3,10)')NULL)Insertdbo. Dmmtest (Strcol1, Intcol1, Dttmcol1, Bincol1, Emailcol1, randomcol1)Values('ABCDEFGHIJKLMN',123456789,getdate(),0x123456789,'[email protected]',123456789)

This is true if you see data with a user who is not a grant unmask

See which fields have mask added

SELECT  as table_name, c.is_masked, c.masking_function    from  as C   JOIN  as tbl         on C.[object_id]= tbl. [object_id]  WHERE = 1;  

GROUP by actually occurs before mask, but also with real data

The results are as follows

Reference:

Dynamic Data Masking

SQL Server->> new features of SQL Server 2016--Dynamic Data masking