未加星标

Dynamic Data Masking Feature in SQL Server 2016

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Microsoft has introduced an impressive new feature in SQL Server 2016 called Dynamic Data Masking (DDM). Dynamic Data Masking allows a developer or administrator to decide how much of the sensitive data to reveal with minimal impact on the application layer. This feature also helps to simplify the design and coding of security in your application by making the data at the database level.

Dynamic Data Masking does not modify or change the actual data stored in a table; it appliesthe masking functions on the table’s column at the time of returning a data as the result of a query. Dynamic Data Masking supports four data masking functions, as listed below, using which you can mask the data at the database level:

Default Random Custom String Email

Note: There are two ways using which you can apply the DDM functions. You can apply this at the time you create the table or youcan apply this function in the existing table that contains data using an ALTER statement.

Default

The default function of Dynamic Data Masking masksdata on the basis of the column's data type.

If the data type is date and time, then it shows the data in 1900-01-01 00:00:00.000 formats. If the data type is numeric then it shows a 0. If data type is string, then it displays databy adding Xs in the string. This function can add maximum 4 X’s in string data, if string contains less than 4 characters, then it will show X for fewer characters only.

An example of the Default Dynamic Data Masking function is shown below.

In this whole article, we will use the sametable, so let's create this table. Thebelow script will create a table named DDM_Student_Sample. While creating thetable, we will apply the default DDM function on the Student_DOB column. The actual data of the Student_DOB column will not be visible to the user who has read permission. Instead of the actual data, SQL Server will return data in the 1900-01-01 00.00.00.000 format

--Create a table DDM_Student_Sample & apply default function on Student_DOB column
Create table DDM_Student_Sample
(
Student_ID int,
Student_DOB datetime masked with (function = 'default()'),
Student_Name varchar(100),
Student_Email_Id nvarchar(100)
)

After table creation, we need to insert some data into table to check how the Default DDM function works. So we will use below query to insert four rows into the table.

-- Insert some values into DDM_Student_Sample table
insert into DDM_Student_Sample values (1234,'05/17/1989','Stuart Little Joe',[email protected]')
insert into DDM_Student_Sample values (2134,'03/01/1990','Alexa sentmov',[email protected]')
insert into DDM_Student_Sample values (1324,'06/21/1992','SentLaw Rents',[email protected]')
insert into DDM_Student_Sample values (1254,'10/29/1987','pitterpie Laafte',[email protected]')

After inserting the data we will use the below script to check an actual data stored in the table- DDM_Student_Sample. ( here we are using the user credentials who is having full access or adequate permission which require to check an actual data of the table and those users only will be able to see the sensitive information like as shown in above figure.)

-- Check the actual data in the table DDM_Student_Sample using the below query
Select * from [dbo].[DDM_Student_Sample]
Dynamic Data Masking Feature in SQL Server 2016

Now we will create a user and grant read permission on DDM_Student_Sample table using below script:

--Create DDM_Read user & Grant select (read) permision on table DDM_Student_Sample
CREATE USER DDM_Read WITHOUT LOGIN

As we have applied Default DDM function on column Student_DOB, so lets check how the data will appear when user having read permission on a table using below script.

-- Check the data using user DDM_Read who is having select permission on DDM_Student_Sample table
EXECUTE AS USER = 'DDM_Read'
SELECT * FROM [dbo].[DDM_Student_Sample]
REVERT
Dynamic Data Masking Feature in SQL Server 2016

On above output we can see that user DDM_Read is not able to see the actual data for the Student_DOB column because we have applied the Default Dynamic Data Masking function on this column. Hence, data of column Student_DOB showing in the 1900-01-01 00.00.00.000 format.

If you want to allow a few users who have less privileges, like the user, DDM_Read, then grant the UNMASK permission for this set of users:

-- Grant UNMASK permission to user
Grant UNMASK to DDM_Read

After granting UNMASK permission to the user, DDM_Read, they will be able to see the actual data, like shown in the below figure.


Dynamic Data Masking Feature in SQL Server 2016

Use the below script to revoke the UNMASK permission of user, DDM_Read.

-- Revoke the Unmask permission of user
Revoke UNMASK to DDM_Read Random

This DDM function is applied on numeric data types only. It displays a random value for the specified range. In the below example we will apply the Random function on the Student_ID column.

-- apply Random DDM function on Student_ID column
Alter Table[dbo].[DDM_Student_Sample] Alter Column Student_ID Add masked with (function='Random(1,4)')

After applying the Random function, when we try to check the data of table using the DDM_Read user (user with read permission only), the data of the table will look like shown in below figure:


Dynamic Data Masking Feature in SQL Server 2016

In the above figure, we can see that actual values for Student_ID are replaced with some random numeric values. Again, if you want to allow less privileged user to check the actual data of the table, then grant the UNMASK permission.

Custom String

This DDM function uses the below syntax to mask the data:

Syntax :  Partial(prefix,[padding],suffix) Prefix Starting numbers of character to be displayed. Suffix Last number of characters to be displayed from specified column value Padding Custom padding string for masking.

We will apply the Custom String DDM function on Student_Name column with the below values :

Prefix = 3 -- It will displayed first three characters of Student_Name column values. Suffix= 9 -- It will display last 9 characters of Student_Name column values. Padding = &&**& -- It will start masking from 4th character and display this Padding string.

Use the below script to apply Custom String function on a Student_Name column of table DDM_Student_Sample.

-- apply Partial DDM function on Student_Name column
Alter Table[dbo].[DDM_Student_Sample] Alter Column Student_Name Add masked with (function='Partial(3,"&&**&",9)')

And then check the data using DDM_Read user.


Dynamic Data Masking Feature in SQL Server 2016

The data in the column, Student_Name, will look like it does above for the user, DDM_Read, due to the Custom String DDM function.

The Email Function

This DDM function will displays the first character of an email address, masking the rest of the characters with [email protected] until the suffix “.com”.For example, if we apply the email DDM function for an email address like [email protected], then this email address will appear as "[email protected]".

Using the below script, we will apply the email DDM function on the Student_Email_Id column of the table, DDM_Student_Sample, and check how the da

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLUNMSQL ServerUTCU
分页:12
转载请注明
本文标题:Dynamic Data Masking Feature in SQL Server 2016
本站链接:http://www.codesec.net/view/521401.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(19)