SQL 2016 Dynamic Data Masking

One of the features I am excited about in SQL 2016 is Dynamic Data Masking, but after a few minutes using it I have to say proceed with caution here. ¬†As Rodney Landrum points out, all you have to work around the masking is cast the column to nchar. That means on top of masking you would also want to control access to your data through a proc or view. If I need to do that, couldn’t I just do the masking on my own with a proc or view? Anyway, here are my own noodlings with this new capability:


CREATE DATABASE Andrew
USE Andrew

CREATE TABLE MaskingDemo
(SecretString varchar(50),
NotSoSecretString varchar(50)
)

INSERT INTO MaskingDemo VALUES ('Secret','Not a secret')

DBCC TRACEON(209,219,-1)

ALTER TABLE MaskingDemo ALTER COLUMN SecretString ADD MASKED WITH (FUNCTION='default()')

SELECT * FROM MaskingDemo

CREATE USER Andrew WITHOUT LOGIN
GRANT SELECT ON MaskingDemo To Andrew

SELECT * FROM MaskingDemo; --sa can see the secret

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo; --Andrew can not see the secret
REVERT;

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo WHERE SecretString='secret'; --But Andrew can go fishing for secrets
REVERT;

EXECUTE AS USER = 'Andrew';
SELECT *, CAST(SecretString AS nchar(50)) FROM MaskingDemo;
REVERT; --oh crap, Andrew can see the secret

--what if i want Andrew to see the secret
GRANT UNMASK TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo;
REVERT; --now Andrew can see everything

DENY SELECT ON MaskingDemo TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo;
REVERT; --Andrew sees nothing

CREATE VIEW vMaskingDemo AS
SELECT 'xxxxxx' AS Secret, NotSoSecretString FROM MaskingDemo

GRANT SELECT ON vMaskingDemo TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM vMaskingDemo;
REVERT; --Column is masked

EXECUTE AS USER = 'Andrew';
SELECT CAST(Secret AS nchar(50)), NotSoSecretString FROM vMaskingDemo;
REVERT; --Casting would do nothing

USE master
DROP DATABASE Andrew

CTP 2.3 Updates (9/13/15)

The trace flag is no longer required as Masking is turned on by default. The trace flag will now actual disable it if you run it. Also, the issue where the CAST will displaying the masked data has been fixed.