As most of you know that latest update from Microsoft about SQL Server 2016 was released of CTP2.1 version. There is a long list of new features getting introduced in SQL Server 2016. In this blog, I would talk about one of the security feature called Dynamic Data Masking.
Whenever you access your account on your bank site, would you be comfortable in seeing your credit card or bank account number in clear text on the web page? There are multiple ways to do this at the application level, but as a human nature, it leaves room for error. One small mistake from the developer can leak sensitive data and can cost a huge loss. Wouldn’t it be great if a credit card number would be returned with only its last 4 digits visible – XXXX-XXXX-XXXX-1234 with no additional coding? Sounds interesting, read on!
Before experimenting this feature please remember that if you are using CTP2.0 then you need to turn on trace flags using below command.
DBCC TRACEON(209,219,-1)
If you don’t enable, then here is the error which you would receive while trying my sample script given later.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘masked’.
Not to forget that this is SQL Server 2016 feature. Running the script on earlier versions of SQL would cause below
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘MASKED’.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Here is the script to create objects (database, table, masked column).
Dynamic Data Masking
SET NOCOUNT ON GO -- drop database MaskingDemo - if already exists USE [master] GO IF DB_ID('MaskingDemo') IS NOT NULL BEGIN ALTER DATABASE [MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [MaskingDemo] END -- create new database called MaskingDemo CREATE DATABASE MaskingDemo GO USE MaskingDemo GO -- Create table with different data type columns CREATE TABLE MyContacts ( ID INT IDENTITY(1, 1) PRIMARY KEY ,fName NVARCHAR(30) NOT NULL ,lName NVARCHAR(30) NOT NULL ,CreditCard VARCHAR(20) NULL ,SalaryINR INT NULL ,OfficeEmail NVARCHAR(60) NULL ,PersonalEmail NVARCHAR(60) NULL ,SomeDate DATETIME NULL ) -- insert a row INSERT INTO [dbo].[MyContacts] ([fName],[lName] ,[CreditCard],[SalaryINR],[OfficeEmail],[PersonalEmail], SomeDate) VALUES('Pinal','Dave','1234-5678-1234-5678',999999,'pinal@SQLAuthority.com','pinal@foobar.in', '31-March-2013') GO -- apply masking ALTER TABLE MyContacts ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-XX",2)') ALTER TABLE MyContacts ALTER COLUMN SalaryINR ADD MASKED WITH (FUNCTION = 'default()')-- default on int ALTER TABLE MyContacts ALTER COLUMN SomeDate ADD MASKED WITH (FUNCTION = 'default()')-- default on date ALTER TABLE MyContacts ALTER COLUMN fname ADD MASKED WITH (FUNCTION = 'default()')-- default on varchar ALTER TABLE MyContacts ALTER COLUMN OfficeEmail ADD MASKED WITH (FUNCTION = 'email()') GO -- create a new user and grant select permissions USE MaskingDemo GO CREATE USER WhoAmI WITHOUT LOGIN; GRANT SELECT ON MyContacts TO WhoAmI;
I have added additional comments in script so that it’s easy to understand. Below script would show the power of masking.
USE MaskingDemo GO SELECT * FROM MyContacts; -- this would show clear data GO EXECUTE AS USER = 'WhoAmI'; SELECT * FROM MyContacts -- this should show masked data REVERT; GO
As we can see above, those fields which are masked are showing obfuscated data based on masking rule.
Note: Versions after CTP2 release, the trace flag will not be needed. If you add trace flag, you would start getting “Incorrect syntax” error.
Reference: Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Very good feature i was excepting from long time…thanks for sharing such new featuer….you always rocking….
Welcome Veeresh.
A small, but crucial n fantastic feature indeed!!!
I agree Viral.
What determines who can see masked vs unmasked values?
There is a permission called UNMASK. Would write more on a blog.
What determines who can see masked vs unmasked data?
How does this affect aggregate functions against masked columns?
I have not tested it, would have another blog post for this.
Hi…
Nice feature…which will most of the people where dev team’s job reduced. Thanks Pinal.
I don’t think the same SShankar. It would avoid human errors.
This feature should not be used for security!!!
Not as it is on CTP 2.1 !!!
You can expose the data simply by CONVERTing it.
More information and demo can be seen in the TechNet article, last comment: https://social.technet.microsoft.com/wiki/contents/articles/31419.database-engine-dynamic-data-masking.aspx
Thanks for enlightening Ronen.
Seems the provided link specifies that as of CTP 2.1 converting the data no longer exposes it, but simply returns to the default making of XXXX. So no problem.
Eran K, The linked article was written by me (I know what it said).
Now check this blog for production version issues:
>> SQL Server 2016 – Crack the masking
Great new feature. Thanks Pinal.
why Microsoft have too many release 2012, 2014 and now 2016….. with out using ……. I hope it is confusing customers it shold be stable enough . sometimes it may good to learn from other…like ORACLE
What all Edition supports this feature in SQL Server 2016? Is this only for Enterprise Edition?
How do you set unmask permissions on a user or role so that the user sees masks on certain columns and no mask on others. It seems all fields are masked or non of them are.
GRANT unmask TO proxyUser;
This ain’t gona cut it. Need something like
GRANT unmask OBJECT::dbo.Sometable.Somefieldd TO proxyUser;
I must be missing something.
Answered my own question. No i don’t think i’m missing anything. All fields for a particular user, database are masked or unmasked. You can’t grant / deny unmask rights at the column or even the table level.
See note on page 17 in microsoft 2016 ebook.
http://download.microsoft.com/download/C/F/1/CF1B4F65-2F24-48E8-8111-5D46FC89BB56/Microsoft_Press_eBook_Intro_SQL%20Server_2016_Preview_Ed_PDF.pdf