Last week, dbi services organized an event named “SQL Server 2016: what’s new?” in Lausanne, Basel and Zurich. I would take the opportunity to say again a big thank you to everyone which joined us.
During my session some questions concerning the new functionality Dynamic Data Masking were asked. In fact data are masked for some roles and not for some others.
Let’s try to clarify that.

I will use the same script I used during the event to initialize the database, create the table and fill-in my table with some rows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Create database
USE MASTER
GO
CREATE DATABASE DDM_TEST
GO
-- Create the Confidential table
USE [DDM_TEST]
GO
CREATE TABLE [dbo].[Confidential](
[ID] [int] NULL,
[Name] [nvarchar](70)NULL,
[CreditCard] [varchar](16)NULL,
[Salary] [int] NULL,
[Email] [nvarchar](60)NULL
)ON [PRIMARY]
-- Insert some rows
INSERT INTO [dbo].[Confidential] VALUES ('1','Stephane','3546748598467584',113459,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('2','David','3546746598450989',143576,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('3','Nathan','3890098321457893',118900,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('4','Olivier','3564890234785612',98000,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('5','Alain','9897436900989342',85900,'[email protected]')
INSERT INTO [dbo].[Confidential] VALUES ('6','Fabrice','9083234689021340',102345,'[email protected]')

I will create some masks for confidential columns with the following script:

1
2
3
4
5
6
7
8
9
10
-- create data masking for confidential columns with masking functions
USE DDM_TEST
GO
ALTER Table Confidential
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'random(1, 150000)')
ALTER Table Confidential
ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION='partial(1,"XXXX",2)')
ALTER Table Confidential
ALTER COLUMN Email ADD MASKED WITH (FUNCTION='email()')

I create now a user and add him to db_datareader role:

1
2
3
4
5
6
7
8
-- Create a user named TestDemo and add this user to the db_datareader role
USE DDM_TEST
go
CREATE USER TestDemo WITHOUT LOGIN
USE [DDM_TEST]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestDemo]
GO

Let’s try to visualize the Confidential table with my user:

DDM_res1

Member of the db_datareader role are able to query the Confidential table but cannot see masked columns without masks.
Let’s try to add my user to the db_datawriter role and rerun my query:

DDM_res2

Same, and it’s normal as db_datawriter cannot change read possibility, but the user is now able to insert data in the Confidential table like this for example:

DDM_res3

But, it cannot view in clear text data that it has inserted.
Let’s add my user to the db_owner database role:

DDM_res4

He can now view masked columns.
In term of permissions for data masking we have:

  • to create table with dynamic data mask: CREATE TABLE and ALTER on schema permissions are needed
  • to add, replace, remove a mask on a column:  ALTER ANY MASK permission and ALTER permission on table are needed
  • to view unmasked data on a table where a user has SELECT permission: requires UNMASK permission for the user
  • CONTROL permission on a database: includes ALTER ANY MASK and UNMASK permission (admin users or roles as SYSADMIN, SERVER_ADMIN, db_owner have automatically CONTROL permission on the database)

Happy masking 😉

Post Views: 1,684
Thumbnail [60x60]
by
Stéphane Savorgnano