Azure SQL Database (masking) Azure SQL database Dynamic Data mask

Source: Internet
Author: User
Tags management studio sql server management sql server management studio

Windows Azure Platform Family of articles Catalog

  When we use relational data, we sometimes want to:

-Admin admin, can view all the data

-Regular users, some sensitive fields, such as credit cards, e-mails and other fields are masked

In this case, you can use Dynamic Data masking to mask some fields of the data table.

For example, we have the user's credit card information, email address masking, can play a role in protecting the privacy of users.

Dynamic Data Masking policy:

1. SQL User not screened: a set of SQL users or AAD identities that can get unshielded data in SQL query results. Users with administrator privileges are never masked, and these users can see the raw data without any masking.

2. Masking rules: A set of rules that define the specified field to be masked, and the mask function used. You can define a specified field by using the database schema name, table name, and column name.

3. Shielding function: A set of methods for controlling data disclosure in different situations.

Shielding function Shielding logic
Default

Complete masking based on the data type of the specified field

For string data types (nchar, ntext, nvarchar), XXXX is used, and fewer X is used if the field size is less than 4 characters.
• For numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real), a value of 0 is used.
For date/Time data types (date, DateTime2, DateTime, DateTimeOffset, smalldatetime, time), 1900-01-01 is used.
• For SQL variables, the default value for the current type is used.
• For XML, the document will be used.
• For special data types (timestamp, table, HierarchyID, GUID, binary, image, varbinary space type), a null value is used.

Credit card

This masking method exposes the last four digits of the specified field and adds a constant string as a prefix for the credit card format.

xxxx-xxxx-xxxx-1234

Email

This masking method exposes the first letter and replaces the field with xxx.com, and uses a constant string in the format of an e-mail address as the prefix.

[Email protected]

Random number This masking method generates random numbers based on the selected boundary and the actual data type. If the specified bounds are equal, the mask function will be constant.
Custom text

This masking method exposes the first and last characters and adds a custom fill string in the middle. If the original string is shorter than the exposed prefix and suffix, only the padding string is used.
prefix [fill] suffix

Preparatory work:

1. Preparing Your Azure China account

2. Download Azure SQL Server Management Studio: https://docs.microsoft.com/en-us/sql/ssms/ Download-sql-server-management-studio-ssms

1. First we sign in to the Azure ARM Portal: https://portal.azure.cn

2. Create an azure SQL Database named Leisqldbsh.

Create a new Azure SQL Database Server at the same time, create a login user name: Leizhang

  

3. After creation, set the IP whitelist for SQL Server.

  

4. Use SSMs to link to this database. Under the LEISQLDBSH,

  

Execute the following T-SQL statement

CREATE TABLE [dbo].[UserInfo](    [UniKey] int  not NULL IDENTITY(1,1),    [FirstName] [nvarchar]( -) not NULL,    [LastName] [nvarchar]( -) not NULL,    [CreditCard] [nvarchar]( -) not NULL,    [Mail] [nvarchar]( -) not NULL,    [CellPhone] [nvarchar]( -) not NULL, CONSTRAINT [Pk_userinfo] PRIMARY KEY CLUSTERED (    [UniKey] ASC) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on))GOINSERT  intodbo. UserInfo (Firstname,lastname,creditcard,mail,cellphone)VALUES('San','Zhang','4300-0000-0000-0001','[email protected]','13900000001'),('Er','Wang','4300-0000-0000-0002','[email protected]','13900000002'),('Xiao','Zhang','4300-0000-0000-0003','[email protected]','13900000003'),('Xiao','Li','4300-0000-0000-0004','[email protected]','13900000004'),('Xiao','Ming','4300-0000-0000-0005','[email protected]','13900000005')

5. If we query the Userinfor table with the user Leizhang created in step 2, all the data can be queried. Such as:

6. Then we need to create another user Untrusteddev.

Under master database, execute the following T-SQL command:

Createwith='n0tl0lc4t5^'GOCREATE USER Untrusteddev  for LOGIN Untrusteddev  with = [dbo] GO

7. Under LEISQLDBSH database, execute the following T-SQL

CREATE USER Untrusteddev  for LOGIN Untrusteddev  with = [dbo] GO EXEC sp_addrolemember n'db_datareader', n'  Untrusteddev'GO

8. Then view the dynamic Data masking, such as:

  

Click on the Add Mask. For CreditCard field, add mask

  

For mail fields, add mask

  

9. So we've set up the data masking for the 2 fields of the datasheet.

Let's review the database's link information:

Server Name leisqlserversh.database.chinacloudapi.cn leisqlserversh.database.secure.chinacloudapi.cn
Login Name Leizhang Untrusteddev
Password Slightly Slightly
Description Sysadmin Db_datareader

10. We log in to the server as Untrusteddev:leisqlserversh.database.secure.chinacloudapi.cn (note that there is secure information in this DNS)

  Query table UserInfo, information as follows:

You can see that both the CreditCard field and the Mail field are masked.

  

This will protect the privacy of your customers.

Azure SQL Database (masking) Azure SQL database Dynamic Data mask

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.