Posts
22
Comments
75
Trackbacks
0
SQL Server 2016 Dynamic Data Masking (DDM)

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.







posted on Friday, January 29, 2016 11:38 PM Print
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Comment *  
Verification
Tag Cloud