11/13/2018

Getting Started with Dynamic Data Masking in Azure SQL Database

Azure SQL Database provides Dynamic Data Masking out of the box. 



This blog reviews the commands required to set up a Dynamic Data Masking, and bundles in Row Level Security as well.

First, we logged into Azure Portal with valid credentials and subscription  Then selected Azure SQL Database:


Next, we created a new database.  The wizard prompted for information:





Next, we created a new Resource Group for this demo and created a new Server and Database:



Our newly created Azure SQL Database was provisioned:





We already had the new Azure Data Studio downloaded and installed on laptop.  It's a new tool similar to SQL Server Management Studio (SSMS).  

We connected to our newly created Azure SQL Database.   Azure SQL Database requires a setting in Azure SQL Database Firewall, so we need to add current IP Address to allow access from remote laptop:




Using traditional SQL, we ran some  scripts to test the Data Masking functionality, as follows:


------------------------------ -- DROP USERS IF THEY EXIST   DROP USER IF EXISTS TestUser;   DROP USER IF EXISTS TestUser2;   DROP USER IF exists TestUser3; -- DROP TABLE IF EXISTS   DROP table IF EXISTS dbo.Membership; ------------------------------
-- CREATE DATABASE TABLE FOR TESTING CREATE TABLE dbo.Membership    (MemberID int IDENTITY PRIMARY KEY   UserName varchar(100) NULL,    FirstName varchar(100)   NULL   LastName varchar(100)    NULL   Phone varchar(12)        NULL   Email varchar(100)       NULL,    SSNbr varchar(11)        NULL);  -------------------------------- -- INSERT 3 ROWS 1 FOR EACH USERNAME INSERT dbo.Membership (UserName,FirstName, LastName, Phone, Email, SSNbr) VALUES   ('TestUser2','Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com','123-45-6789'),  ('TestUser3','Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co','9876-54-321'),  ('TestUser', 'Juan','Mu', '555.123.4569', 'ZMu@contoso.net','555-44-3211');  ------------------------ -- CREATE A SCHEMA FOR SECURITY CREATE SCHEMA Security; ------------------------ -- CREATE A FUNCTION TO TEST ROW LEVEL SECURITY CREATE FUNCTION Security.fn_securitypredicate(@Username AS sysname    RETURNS TABLE  WITH SCHEMABINDING  AS      RETURN SELECT 1 AS fn_securitypredicate_result   WHERE @Username = USER_NAME() OR USER_NAME() = 'TestUser2'; --------------------- -- CREATE A SECURITY POLICY THAT REFERENCES ABOVE FUNCTION CREATE SECURITY POLICY FilterDemo ADD FILTER PREDICATE Security.fn_securitypredicate(UserName)   ON dbo.Membership  WITH (STATE = ON);  --------------------- --CREATE TEST USER 1 CREATE USER TestUser WITHOUT LOGINGRANT SELECT ON dbo.Membership TO TestUser; ----- --CREATE TEST USER 2 CREATE USER TestUser2 WITHOUT LOGINGRANT SELECT ON dbo.Membership TO TestUser2; ----- --CREATE TEST USER 3 CREATE USER TestUser3 WITHOUT LOGINGRANT SELECT ON dbo.Membership TO TestUser3; ------------------------ -- RUN SELECT STATEMENT AS USER 'TestUser' RETURNS 1 ROW -- AS ROW LEVEL SECURITY IS IMPLEMENTED EXECUTE AS USER = 'TestUser'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------ -- RUN SELECT STATEMENT AS USER 'TestUser2' RETURNS 3 (ALL) ROW -- AS ROW LEVEL SECURITY IS IMPLEMENTED EXECUTE AS USER = 'TestUser2'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser2' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------ -- RUN SELECT STATEMENT AS USER 'TestUser3' RETURNS 1 ROW -- AS ROW LEVEL SECURITY IS IMPLEMENTED EXECUTE AS USER = 'TestUser3'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser3' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------
The first query returns 1 row, run as "TestUser".

The second query returns 3 rows, run as "TestUser2"
.

The third query returns 1 row, run as "TestUser3".

Reason being, we implemented a function, to handle Row Level Security.  Row Level Security is another Security feature out of the box in Azure SQL Database.  You can see the LastName and SSNbr fields are not masked, yet.

To get Dynamic Data Masking, we add the following code snippets:
------------------------ -- APPLY DATA MASKING TO LASTNAME FIELD OF MEMBERSHIP TABLE
ALTER TABLE dbo.Membership  ALTER COLUMN LastName varchar(100) MASKED WITH (FUNCTION = 'default()'); --- -- APPLY DATA MASKING TO SSNBR FIELD OF MEMBERSHIP TABLE ALTER TABLE dbo.Membership  ALTER COLUMN SSNbr varchar(100) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)'); ------------------------ -- RE-RUN SELECT STATEMENT AS USER 'TestUser' RETURNS 1 ROW -- 2 FIELDS ARE MASKED AS EXPTECTEDEXECUTE AS USER = 'TestUser'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------ -- RE-RUN SELECT STATEMENT AS USER 'TestUser2' RETURNS 3 (ALL) ROW -- 2 FIELDS ARE MASKED AS EXPTECTED EXECUTE AS USER = 'TestUser2'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser2' as QueryType,User,* FROM dbo.Membership; 
REVERT; ------------------------ -- RE-RUN SELECT STATEMENT AS USER 'TestUser3' RETURNS 1 ROW -- 2 FIELDS ARE MASKED AS EXPTECTED EXECUTE AS USER = 'TestUser3'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser3' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------
Here's the output from all 3 queries. You can see the Dynamic Data Masking. Fields are LastName & SSNbr.

------------------------ -- TO GET A LIST OF FIELDS THAT HAVE DYNAMIC DATA MASKING, RUN THIS SCRIPT SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  FROM sys.masked_columns ASJOIN sys.tables AS tbl       ON c.[object_id] = tbl.[object_id]  WHERE is_masked = 1; ------------------------
Source code for this demo was found here. Thanks for reading~!