SQL Server 2016 – Dynamic Data Masking

Friends!

So I’ve reached to my final blog about SQL 2016 Advance Security features (couple of discussed in my previous blogs 1 & 2). In this blog I’m going to put forward “Dynamic Data Masking” (DDM) feature with a great interest.

As I’ve a practice of putting technical things through live examples, here is the one where Dynamic Data Masking suits very well.

Let’s go back to our same DB “Finacle” where a table contains bank’s prestigious customers email ID, Credit Card No, Phone No and so one. Such personal information is PII (Personally Identifiable Information). Bank’s compliance team wants to safeguard this information at lower environment (than production) or wherever requested. Application Support Team, Development Team or Testing Team requests to refresh lower environment DBs with actual PROD Data. Definitely this kind of need isn’t unusual for any application to do real time troubleshooting, load testing etc.

Before SQL Server 2016 to address this compliance item, development/DBA team has to come up with some kind of data masking scripts which immediately runs after non-production DB refresh (with PRODUCTION data). Let’s me list down some challenges evolves with this kind of traditional data masking solution.

• Additional downtime for teams involved. In other words $ impact.
• Lot of additional disk space required for LOG files drive, so it doesn’t cry during script execution.
• Data masking Script failure handling.
• Timely update & maintenance of Data masking Script (additional overhead for team, no dedicated team(s))

Dynamic Data Masking (DDM) feature of SQL Server 2016 addresses aforesaid challenges with ease. DDM helps in preventing abuse of sensitive data by masking it. Here are some feature/benefits of DDM.

• Masks the data on the fly as your queries run.
• Policy-driven at the table and column level, for a defined set of users.
• Set at the login level, unlike row-level security.
• Multiple masking functions available (e.g. full, partial).

Dynamic Data Masking (DDM) comes with certain types of pre-defined features listed below.

• Default – fully mask values by returning a value of XXXX
• Email – partially mask email addresses like this: aXXX@XXXX.com.
• Partial – partially mask values by using a custom definition, 3 parameters (Prefix, Padding, Suffix).
• Random – fully mask numeric values by using a random value between a lower & upper boundary).

So let’s jump on code to which gives some hands on Dynamic Data Masking (DDM).

1. Create a table in database, contains PII columns.

   USE Finacle
   Go
   --DROP TABLE [CustomersPII_Next]
   GO
   CREATE TABLE [dbo].[CustomersPII_Next](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [PhoneNo] [nvarchar](10) NOT NULL,
    [EmailId] [nvarchar](50) NOT NULL,
    [Organisation] [nvarchar](50) NOT NULL,
    [Location] [nvarchar](50) NULL
    PRIMARY KEY CLUSTERED 
    (
    [CustomerId] ASC
    ) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] 
   GO
   

2. Insert some PII data to newly created table.

   INSERT INTO [CustomersPII_Next] ([PhoneNo],[EmailId],[Organisation],[Location]) VALUES ('999999999','avanish@sqlservergeeks.com','SQLMaestros','India')
   INSERT INTO [CustomersPII_Next] ([PhoneNo],[EmailId],[Organisation],[Location]) VALUES ('989898989','ab@sqlservergeeks.com','SQLMaestros','India')
   INSERT INTO [CustomersPII_Next] ([PhoneNo],[EmailId],[Organisation],[Location]) VALUES ('909090909','ahmad@sqlservergeeks.com','SQLMaestros','India')
   GO
   --SELECT * FROM [CustomersPII_Next]
   --GO

3. Use Dynamic Data Masking (DDM) pre-defined functions to mask PII information at table definition level.
   

   ALTER TABLE [CustomersPII_Next]
   ALTER COLUMN [Organisation] ADD MASKED WITH (FUNCTION ='default()')
   GO
   ALTER TABLE [CustomersPII_Next]
   ALTER COLUMN [PhoneNo] ADD MASKED WITH (FUNCTION ='partial(2,"xxxx",2)')
   GO
   ALTER TABLE [CustomersPII_Next]
   ALTER COLUMN [EmailId] ADD MASKED WITH (FUNCTION ='email()')
   GO

4. Create a user inside database and grant permission.

   CREATE USER  [LowProfile] WITHOUT LOGIN;
   GO
   GRANT SELECT ON [CustomersPII_Next] TO [LowProfile]

5. Select data from PII table with newly created user.

   SELECT * FROM [CustomersPII_Next]
   GO 
   EXECUTE AS USER = 'LowProfile'
   SELECT * FROM [CustomersPII_Next]
   REVERT;

Wow! Data masking in SQL wasn’t ever that much easy. All you’ve create different user(s) and assign SELECT permission in lower environment to safeguard PII information which is an organizational asset and maintains integrity of organization without compromising organization’s continuous development and improvement activities.

Stay tuned.

Happy Learning!

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook