Along with Get Noticed contest I’m a bit preoccupied with other activities. One of them is preparing to take 70-473 Exam (cloud and data related stuff). Since I’m reading and using cert related stuff I could as well write about it – I’ll probably remember everything I’ll write about for rest of my life (writing about things works magic with memory) and maybe someone will have any use for that. Let’s start with simple things – dynamic data masking.
What is dynamic data masking?
In case you haven’t realized by name it’s dynamic mask for hiding parts of data from unauthorized eyes. There are few predefined data masks in Azure and you can specify you custom ones. You can hide things like credit card number, social security number, emails or address data from people who are not allowed to see them (ie. database administrators or developers). You probably seen masked data hundreds of time in your life. Any billing related to credic card payments should present your credic card number looking like that xxxx-xxxx-xxxx-1234. And it’s exactly what dynamic data masking does.
How can I mask my data?
You can add one of predefined or custom mask to any column in your database and provide set of SQL and/or AAD Users that will see full data. You need to remember that users with administor permissions will always be excluded from masking and WILL see unmasked values, so if user that will manage your database shouldn’t see everything you should ensure that he or she have only necessary permissions (you should do that regardless TBH).
For this post I’ll use very simple table.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE [dbo].[Person] ( [Name] nvarchar(100) NOT NULL, [LastName] nvarchar(100) NOT NULL, [DateOfBirth] date MASKED WITH (FUNCTION = 'default()') NOT NULL, [Rating] bigint NOT NULL ) GO INSERT INTO [dbo].[Person] VALUES ('Rafal', 'Hryniewski', '1988-05-16', 100) GO |
Maybe you’ve noticed that in 4th line I’ve used “MASKED WITH” statement, we’ll get to it for now let’s focus on simpler, non T-SQL things. Most simple way to create dynamic masks is of course Azure portal that provides rather simple interface to do so. Dynamic Data Masking is accessible from SQL Database blade.
By clicking in Add mask we will be able to choose one of predefined masks from select menu. Let’s say we don’t want unathorized people to see my rating and I want to replace it with random numer.
Masks names are rather self explanatory with short description that really tells you everything you want to know, one exception is default mask which I’ll describe in a while. Predefined masks are:
- email – addres@domain.com will be masked to aXXXXX@XXXXXX.com
- credit card – 1234-1234-1234-1234 will be masked to XXXX-XXXX-XXXX-1234
- number – will be masked to random number in given range
- default – will be 0 for numeric fields, 4 X characters for text data types (or fewer if text is shorter) or 01-01-1900 for date types
There is also custom mask which we cover at the end of this post.
If we go back a while, to T-SQL used to create this table, you can see that one column was created with MASKED WITH statement. This is another way of covering our columns with masks during table creation. Let’s see how our data looks for non-admin user with those two masks already.
|
1 2 |
Name LastName DateOfBirth Rating Rafal Hryniewski 1900-01-01 12 |
And number in rating column will randomize and change during each query.
So we now know how to mask our data during table creation or by clicking stuff in azure. Now left use last, custom mask in ALTER TABLE statements. Please take a note, that inside T-SQL it’s called partial.
|
1 2 3 4 5 6 7 |
ALTER TABLE [dbo].[Person] ALTER COLUMN [LastName] ADD MASKED WITH (FUNCTION = 'partial(1, "", 0)') GO ALTER TABLE [dbo].[Person] ALTER COLUMN [Name] ADD MASKED WITH (FUNCTION = 'partial(1, "***", 1)') GO |
What exactly we did? We’ve masked two remaining columns in two different ways. First let me show you, how does SELECT results look like for simple user.
|
1 2 |
Name LastName DateOfBirth Rating R***l H 1900-01-01 20 |
As you can see with first T-SQL statement we hidden every LastName character except first. We’ve used custom (partial) mask which parameter syntax is (prefix, padding, suffix) where prefix is number of starting characters to remain unchanged, padding is what we will paste in place of masked data and suffix are ending characters that will remain intact.
But all of our users can’t see data now? What should you do to grant them access? There are two ways first is providing users as excluded during creatin mask, second one is granting UNMASK permission to user with statement (testrh is my test user name with some basic permissions in scope of database):
|
1 2 |
GRANT UNMASK TO testrh GO |
Or you could just drop mask with this code if you think masking first name is kind of overkill.
|
1 2 3 |
ALTER TABLE [dbo].[Person] ALTER COLUMN [Name] DROP MASKED GO |
Summary
I hope you liked this post and learned something from it. I haven’t blogged about database topics yet so if you have any suggestions and/or questions feel free to reach me in comments or in social media. I hope to write more about Azure SQL and related technologies because while my latest love Akka.NET is fascinating, I don’t want to write just about actors and… I need some training for 70-473 and nothing is better that practice and/or blogging about something.
PS. If you have some good materials related to 70-473 that you could recommend, please leave me link in comments. Thanks!
PS2. Along with many other “new” features introduced in Azure SQL, Dynamic Data Masking is also available on SQL Server 2016. Thanks for reminding me about that – Michał Wilczyński.