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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.