When 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','firstname.lastname@example.org', '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:
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.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.