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:
-- 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,'firstname.lastname@example.org') INSERT INTO [dbo].[Confidential] VALUES ('2','David','3546746598450989',143576,'email@example.com') INSERT INTO [dbo].[Confidential] VALUES ('3','Nathan','3890098321457893',118900,'firstname.lastname@example.org') INSERT INTO [dbo].[Confidential] VALUES ('4','Olivier','3564890234785612',98000,'email@example.com') INSERT INTO [dbo].[Confidential] VALUES ('5','Alain','9897436900989342',85900,'firstname.lastname@example.org') INSERT INTO [dbo].[Confidential] VALUES ('6','Fabrice','9083234689021340',102345,'email@example.com')
I will create some masks for confidential columns with the following script:
-- 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:
-- 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 Condifendial table like this for example:
But, it cannot view in clear text data that it has inserted.
The only way to visualize data without masks is to be a member of the db_owner role:
Conclusion is clear, in order to be able to view masked columns user has to be member of the db_owner role in the database containing the table.