Select Page

Basics of Using Dynamic Data Masking

Eric Russo | | September 10, 2015

Basics of using Dynamic Data Masking with SQL Server 2016When SQL Server 2016 was released, I was interested in finding some of the cool capabilities as part of the release. In this blog, we will look at a simple example. Data Masking is a security feature which hides or obfuscates sensitive data from certain users. Adopting a data masking solution can enable database owners/administrators to raise the level of security and privacy assurance of their database – in addition to meeting compliance requirements with various security and privacy standards.

Dynamic Data Masking or Real Time Masking

This refers to a scenario meant to protect a production database, by returning obscured data on queries performed by certain users, as per defined by the masking policy. The actual data on the database is not changed. This means that ‘privileged’ users still get results with the actual data, while other users only get access to data to which they are entitled – again, based on the defined policies. Naturally, this implies that the determination of what data to return is done on the fly, in real time.

Data Masking in Action

I have started to experiment this feature on a CTP2.2 and I have not used the trace flags that you see on the internet. We will do the following:

1. Create a demo database for use
2. Create a table with a number of columns
3. Create the masking with different options
4. Create a login/user which will have low privileges
5. Check the effect of data masking

SET NOCOUNT ON
GO
-- drop database DataMaskingDB - if already exists
USE [master]
GO
IF DB_ID('DataMaskingDB') IS NOT NULL
BEGIN
 DROP DATABASE [DataMaskingDB]
END
-- create new database called DataMaskingDB
CREATE DATABASE DataMaskingDB
GO
USE DataMaskingDB
GO

CREATE TABLE Employees (
ID INT IDENTITY(1, 1) PRIMARY KEY
,FirstName NVARCHAR(30) NOT NULL
,LastName NVARCHAR(30) NOT NULL
,Credit_Card VARCHAR(20) NULL
,Salary INT NULL
,OffEmail NVARCHAR(60) NULL
,PerEmail NVARCHAR(60) NULL
,DOB DATETIME NULL
)
-- insert a row
INSERT INTO [dbo].[Employees]
(FirstName, LastName, Credit_Card, Salary, OffEmail, PerEmail, DOB)
VALUES('Pinal','Dave','1234-8765-1234-8765',987654,'pinaldave@SQLAuthority.com','pinal@dave.com', '30-September-1980')
GO

-- Apply Data Masking techniques
ALTER TABLE Employees
ALTER COLUMN Credit_Card ADD MASKED
WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-",4)')

ALTER TABLE Employees
ALTER COLUMN Salary ADD MASKED
WITH (FUNCTION = 'default()')      -- default on int

ALTER TABLE Employees
ALTER COLUMN DOB ADD MASKED
WITH (FUNCTION = 'default()')      -- default on date

ALTER TABLE Employees
ALTER COLUMN FirstName ADD MASKED
WITH (FUNCTION = 'default()')      -- default on varchar

ALTER TABLE Employees
ALTER COLUMN OffEmail ADD MASKED
WITH (FUNCTION = 'email()')
GO

Now that the basic building blocks have been done. We will next create the user for our experimentation and see the effect of data masking on them.

USE DataMaskingDB
GO
CREATE USER StandardUser WITHOUT LOGIN;
GRANT SELECT ON Employees TO StandardUser;
GO

USE DataMaskingDB
GO
SELECT * FROM Employees; -- this would show clear data
GO
EXECUTE AS USER = 'StandardUser';
SELECT * FROM Employees;     -- this should show masked data
REVERT;
GO

The output for above will look like this:

dynamic_output

As you can see, SQL Server 2016 introduces a powerful feature where privacy and data leakage of sensitive data for organizations can be taken care. I am sure there are compelling reasons where I see developers use this capability inside their application that it is worth a try.

Stay tuned for upcoming blogs on some of the new features of SQL Server 2016 and my impressions of the sneak peek from Microsoft SQL Server 2016.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS