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.