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,'[email protected]','[email protected]', '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 Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.
Not everybody knows what a Database Administrator does. Learn here what DBA job responsibilities are so you can see the depth and breadth of their tasks.
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.