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

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