Masking has been
implemented as a lightweight way to hide information from prying eyes for
decades now, when you type password you got *, when you view your credit card numbers
you see partially masked data. And as security
leaks increases on all small and big companies, Microsoft has implemented a new
security tool for SQL Server called Dynamic Data Masking.
What is Dynamic Data Masking?
From
MSDN
Dynamic data masking limits sensitive data
exposure by masking it to non-privileged users. Dynamic data masking helps
prevent unauthorized access to sensitive data by enabling customers to
designate how much of the sensitive data to reveal with minimal impact on the
application layer. It’s a data protection feature that hides the sensitive data
in the result set of a query over designated database fields, while the data in
the database is not changed. Dynamic data masking is easy to use with existing
applications, since masking rules are applied in the query results. Many
applications can mask sensitive data without modifying existing queries.
Start using DDM
1.
Create Database and Tables
Use Master
IF EXISTS(SELECT 1 FROM sys.databases WHERE database_id = DB_ID('SQLWandererDEMO'))
BEGIN;
DROP DATABASE [SQLWandererDEMO];
END;
GO
Create Database [SQLWandererDEMO]
GO
USE
[SQLWandererDEMO];
IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('CustomerInfo'))
BEGIN;
DROP TABLE [CustomerInfo];
END;
GO
CREATE TABLE [CustomerInfo] (
[CustomerInfoID] INTEGER
NOT NULL IDENTITY(1, 1),
[Id] INTEGER
NULL,
[Name] VARCHAR(255) NULL,
[CreditCard] VARCHAR(50) NULL,
[Payments] money
NULL,
[Email] VARCHAR(255) NULL,
PRIMARY KEY ([CustomerInfoID])
);
GO
2.
Insert Records
INSERT INTO CustomerInfo([Id],[Name],[CreditCard],[Payments],[Email])
VALUES(1,'Potts, Eaton B.','4916489637887','5.45','Suspendisse.tristique@dolor.org')
,(2,'Britt, Beverly M.','453268 114117 7739','7.00','sodales.elit.erat@Donecconsectetuer.ca')
,(3,'Lester, Tanner I.','491662 555248 1862','3.39','felis.purus.ac@pretiumet.net')
,(4,'Snider, Jolie A.','4929628560963454','1.20','pellentesque.massa.lobortis@Maecenasiaculis.ca')
,(5,'Kelly, Xaviera U.','4556 0660 6234 1301','5.74','Integer.urna.Vivamus@fringillaporttitorvulputate.co.uk')
,(6,'Montoya, Melinda P.','4556203207531120','5.19','Aliquam.erat@Cras.co.uk')
,(7,'Woods, Hedley R.','4916 328 64 1504','0.74','eu.elit.Nulla@Mauris.net')
,(8,'Figueroa, Abra M.','4532 732 29 3780','1.58','magnis.dis@nasceturridiculus.net')
,(9,'Woodward, Imani D.','4051 942 36 8597','9.48','quis.arcu@cursus.net')
,(10,'Tucker, Armando R.','4556416695002','7.28','vel@parturient.com');
3.
Create a Spy User
USE
[master]
GO
CREATE LOGIN [Spy] WITH PASSWORD=N'password', DEFAULT_DATABASE=[SQLWandererDEMO], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE
[SQLWandererDEMO]
GO
CREATE USER [Spy] FOR LOGIN [Spy]
GO
USE
[SQLWandererDEMO]
GO
ALTER ROLE [db_datareader] ADD
MEMBER [Spy]
GO
USE
[SQLWandererDEMO]
GO
ALTER ROLE [db_datawriter] ADD
MEMBER [Spy]
GO
4.
Do Test before implementing DDM
--select as admin/dbowner
SELECT * FROM [CustomerInfo]
--select as datareader/datawriter
EXECUTE AS USER='spy';
SELECT * FROM [CustomerInfo];
REVERT;
OUTPUT : 
5.
Add DDM to existing tables
ALTER Table [CustomerInfo]
ALTER COLUMN [Payments] ADD
MASKED WITH (FUNCTION='random(1,100)')
ALTER Table [CustomerInfo]
ALTER COLUMN [CreditCard] ADD
MASKED WITH (FUNCTION='partial(2,"XXXX",2)')
ALTER Table [CustomerInfo]
ALTER COLUMN [Email] ADD MASKED WITH (FUNCTION='email()')
6.
Test after implementing DDM
OUTPUT: 
7.
Try to Copy content of a masked table using Select
Into
GRANT CREATE TABLE TO spy
GRANT ALTER ON SCHEMA::dbo TO spy
EXECUTE AS USER='spy';
SELECT * into
[CustomerInfoCopy] FROM [CustomerInfo];
SELECT * FROM
[CustomerInfoCopy]
REVERT;
OUTPUT:
8.
Try using Cast
EXECUTE AS USER='spy';
SELECT cast(Email as nvarchar(50)) as Email FROM
[CustomerInfo];
REVERT;
OUTPUT:
Conclusion
DDM is not a one time solution for your security needs, but DDM you can at least Help you implement data masking without too much effort compared to doing a Backup, Restore then Obfuscate method for creating Copies of your Production Database.