Dynamic Data Masking in SQL Server 2016

Overview

Microsoft has added new security technologies in the SQL Server 2016 platform that provides the administrator the ability to protect sensitive data. One of them (DDM), helps the administrator basically making the message difficult to understand, usually with confusing and ambiguous language for certain selected columns

Attributes of DDM
  • Dynamic data masking obscures sensitive data by masking it for unauthorized users 
  • It has minimal impact on the application layer and implemented in the database layer 
  • Uses of DDM include preventing developers to work with production tables without exposing sensitive data. 
  • DDM can be configured when creating or after creation of a table by using the ALTER command 
  • Users can still update, insert and delete the data that has DDM configured Although the data is not masked when it is stored in the database if the user executes the SELECT INTO or INSERT INTO to copy data from a masked column into another table the results in the target table will be masked.

Dynamic Data Masking

Dynamic data masking rules can be defined on particular columns, indicating how the data in those columns will appear when queried. There are no physical changes to the data in the database itself. The data remains intact and is fully available to authorized users or applications. Database operations remain unaffected, and masked data has the same data type as the original data, so DDM can often be applied without making any changes to database procedures or application code.














Dynamic Data Masking Functions

Dynamic Data Masking supports four data masking functions, as listed below, using which you can mask the data at the database level:
  • Default Masking - Full masking according to the data types of the designated fields· 
  • Email Masking - The email masking method exposes the first letter of an email address and the constant suffix ".com" in the form of an email address (aXXX@XXXX.com). 
  • Custom String Masking - Exposes the first and last letters and adds a custom padding string in the middle. 
  • Random Masking - Use on any numeric type to mask the original value with a random value within a specified range 
Note: There are two ways to apply this DDM functions. Apply this at the time of table creation or apply this function in the existing table using an ALTER statement.

Demo on how to configure dynamic data masking


--Create a user database
Use master
GO
Create database DynamicMasking
GO

--Create a user table
Use DynamicMasking
GO
Create table SensitiveData
(
  ID int,
  FirstName varchar (20),
  LastName varchar (20),
  CodeNo varchar (20),
  TelNo varchar (20),
  EmailID varchar(20),
  Gender char (1),
  Age int
 )

--Insert data into table
Insert into SensitiveData values
(1,'john','mathews','000-22-2222','212 555 2522','john@gmail.com','M',34),
(2,'harry','jones','121-22-3333','818 555 4545','harry@gmail.com','M',19),
(3,'jimmy','cage','000-22-5555','303 555 7852','jimmy@gmail.com','M',28),
(4,'alex','hales','111-22-6666','605 555 8568','alex@gmail.com','F',41),
(5,'steve','smith','222-22-7777','310 555 1212','steve@gmail.com','M',23)

--Log in as an admin to view all columns in a table
Select * from SensitiveData












--Query to find schema of table
Use [master]
GO
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM DynamicMasking.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'SensitiveData';
GO


--Create a user without a SQL login in a database
Use [DynamicMasking]
GO
CREATE USER Sam
WITHOUT LOGIN
GO

--Grant select and insert permission to sam
Use [DynamicMasking]
GO
GRANT SELECT ON [dbo].[SensitiveData] TO [Sam]
GO
GRANT INSERT ON [dbo].[SensitiveData] TO [Sam]
GO

--Sam can view the data on SensitiveData table
EXECUTE AS USER = 'Sam'
SELECT * FROM dbo.SensitiveData -- columns are NOT masked
REVERT
GO



--Let’s mask the columns with four functions
--1: FirstName column with default function
--2: EmailID column with email function
--3: TelNo column with partial function
--4: Age column with random function

--1: Default Function - DEFAULT() masks all the data in the column
Use [DynamicMasking]
GO
ALTER TABLE dbo.SensitiveData
ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = 'Default()')
GO

--2: Email Function:- EMAIL() masks the EmailID column
Use [DynamicMasking]
GO
ALTER TABLE dbo.SensitiveData
ALTER COLUMN EmailID ADD MASKED WITH (FUNCTION = 'email()')
GO

--3: Partial Function:- PARTIAL() masks the TelNo with partial changes
Use [DynamicMasking]
GO
ALTER TABLE dbo.SensitiveData
ALTER COLUMN TelNo ADD MASKED WITH (FUNCTION = 'partial(0, "xxx xxx xx", 2)')
GO

--4: Random Function : RANDOM() uses a range of numbers to mask data in a column
Use [DynamicMasking]
GO
ALTER TABLE SensitiveData
ALTER COLUMN age ADD MASKED WITH (FUNCTION = 'random(1111,2222)')
GO

--Sam cannot see the any or all masked columns
EXECUTE AS USER = 'Sam'
SELECT * FROM dbo.SensitiveData -- all columns are masked
REVERT
GO












--Granting Sam the UNMASK permission will enable him to see unmasked data:
Use [DynamicMasking]
GO
GRANT UNMASK TO Sam

Use [DynamicMasking]
GO
EXECUTE AS USER = 'Sam'
SELECT * FROM dbo.SensitiveData -- columns are not masked
Revert
GO













--Reset and enable masking to sam
REVOKE UNMASK TO Sam
GO
EXECUTE AS USER = 'Sam'
SELECT * FROM dbo.SensitiveData -- colums are masked once again
REVERT
GO


-- Try doing a SELECT INTO from the table with a mask into a temp table (with a user that doesn't have the UNMASK permission), and you'll find that the temp table contains masked data:
EXECUTE AS USER = 'Sam'
SELECT ID,FirstName,LastName,CodeNo,TelNo,EmailID,Age INTO #SensitiveData052217
FROM dbo.SensitiveData
Revert
GO

SELECT * FROM #SensitiveData052217  -- SensitiveData052217 has masked data
GO

Use [DynamicMasking]
GO
EXECUTE AS USER = 'Sam'
SELECT * FROM dbo.#SensitiveData052217
REVERT
GO












DROP TABLE #SensitiveData052217
REVERT
GO
Select name,is_masked, masking_function
from sys.masked_columns


--To completely remove the masked permission
ALTER TABLE dbo.SensitiveData ALTER COLUMN EmailID DROP MASKED
GO
ALTER TABLE dbo.SensitiveData ALTER COLUMN TelNo DROP MASKED
GO
ALTER TABLE dbo.SensitiveData ALTER COLUMN AGE DROP MASKED
GO
ALTER TABLE dbo.SensitiveData ALTER COLUMN FirstName DROP MASKED
GO
Select name,is_masked, masking_function from sys.masked_columns
GO

Permissions

GRANT UNMASK TO User
REVOKE UNMASK TO User

Viewing DDM data with DMV

Select * from sys.masked_columns

No comments:

Post a Comment