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] [n var char]( 70 )NULL, [CreditCard] [ var char]( 16 )NULL, [Salary] [ int ] NULL, [Email] [n var char]( 60 )NULL )ON [PRIMARY] -- Insert some rows INSERT INTO [dbo].[Confidential] VALUES ( '1' , 'Stephane' , '3546748598467584' , 113459 , '[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 ( '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:
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:
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:
But, it cannot view in clear text data that it has inserted.
Let’s add my user to the db_owner database role:
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 😉