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.
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','firstname.lastname@example.org', '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)