I’m not sure about you guys, but in different jobs I’ve had, there’s always people querying the production database, accessing to sensitive information that they should not be allowed to see like phone numbers, social security numbers, accounts and more.
As DBAs and developers, we have to understand that security is the most important regarding the data. However, there’s going to be people that *need* to be always looking at some data. They could be Data Analysts, people in Finance department, somebody from HR, and others… sometimes even end users and customers have access to that data (ugh!).
SECURITY MECHANISMS
There are different ways how to deal with this and avoid everybody to be looking at things they shouldn’t.
One thing we can do is, to create roles and give permissions to see just the tables or views they need. Views are another mechanism we can use. Here, we just create the view with the data from the different tables they need and grant their user, or role the permission to see the view.
Stored procedures can also be used for this the same way we do with views. The catch is that, we need to GRANT EXECUTE to the user, role or group, otherwise it will fail and you’ll be getting lots of emails of people complaining.
There is a different way to do this in case you don’t want to restrict people from seeing all the objects in the database (of course, you grant SELECT permission only). SQL Server 2016 introduced some new security features, and one of this is what I’ll show you next.
DYNAMIC DATA MASKING
By implementing Dynamic Data Masking (DDM), the DBA defines column level data masking preventing users from reading sensitive data. This allows the people to query the database themselves without seeing the sensitive content of columns. DDM works at query runtime, it doesn’t affect the stored data of the table.
DDM works by defining a fixed set of functions to the columns we want to hide the data from. So, every time we query a table with a DDM function defined it’ll check if we have permission to see the contents of the columns or if it will be displayed as we defined in the masking function.
These are the four masking functions that we can use:| Function | Description |
|---|---|
default | Implements full masking. |
email | Masks email addresses as bXXXX@XXXX.com |
random | Masks the original numeric value with a random value from a range. |
partial | Uses a custom string to mask characters in the data. |
CREATE USER DataAnalyst WITHOUT LOGIN; CREATE USER FinanceGuy WITHOUT LOGIN;Now, we’re going to create a table and define the functions we want to use to mask the data.
CREATE TABLE dbo.SuperSensitiveCustomerData ( ID INT IDENTITY(1,1) PRIMARY KEY , FirstName VARCHAR(100) , LastName VARCHAR(100) MASKED WITH(FUNCTION = 'partial(1,"xxxx", 0)') , HomeAddress VARCHAR(250) MASKED WITH(FUNCTION = 'default()') , SSN VARCHAR(15) MASKED WITH(FUNCTION = 'partial(6, "xxxx", 0)') , Email VARCHAR(50) MASKED WITH(FUNCTION = 'email()') , CreditCard VARCHAR(40) MASKED WITH(FUNCTION = 'partial(12,"xxxx", 0)') , DateOfBirth DATE MASKED WITH(FUNCTION = 'default()') )
See how I’m using different functions like partial(), default() and email(). In this case I didn’t use the random() function.
OK, now that we have our table, we need to populate it with some data. I went to Mockaroo which is a service that generates random test data with different formats like SSNs, Dates, Credit Card numbers and so on. I’ll include the data I used on my github along with the code in this post.
We already have our table and data, we need to grant SELECT permission to our users. And then, execute the query as if we are the users.
GRANT SELECT ON dbo.SuperSensitiveCustomerData TO DataAnalyst, FinanceGuy; EXECUTE(N'SELECT * FROM dbo.SuperSensitiveCustomerData') AS USER = N'DataAnalyst' EXECUTE(N'SELECT * FROM dbo.SuperSensitiveCustomerData') AS USER = N'FinanceGuy'And this is how they will see the data:
As you can see, none of them have the UNMASK permission, so they are not able to see the whole contents of the columns. They see just part of the information there. I’m going give DataAnalyst user permission to see the data.
GRANT UNMASK TO DataAnalyst; EXECUTE(N'SELECT * FROM dbo.SuperSensitiveCustomerData') AS USER = N'DataAnalyst' EXECUTE(N'SELECT * FROM dbo.SuperSensitiveCustomerData') AS USER = N'FinanceGuy'Now, this is what each of them see:
DataAnalyst user is the one on top, see the user is now able to see everything while FinanceGuy doesn’t.
BUT MY TABLE ALREADY EXISTS… WHAT ABOUT THAT?
If you already have a table, and want to implement DDM so unauthorized users won’t see sensitive data, you can alter the table and alter each column adding the function of your choice.
I created a copy of the table we were using but now with no masking functions defined.-- Create a new table as a copy of the first, but with no masking functions.
CREATE TABLE dbo.MoreSuperSensitiveCustomerData
(
ID INT IDENTITY(1,1) PRIMARY KEY
, FirstName VARCHAR(100)
, LastName VARCHAR(100)
, HomeAddress VARCHAR(250)
, SSN VARCHAR(15)
, Email VARCHAR(50)
, CreditCard VARCHAR(40)
, DateOfBirth DATE
);
-- Copy the data from our first table
INSERT INTO MoreSuperSensitiveCustomerData(FirstName, LastName, HomeAddress, SSN, Email, CreditCard, DateOfBirth)
SELECT FirstName, LastName, HomeAddress, SSN, Email, CreditCard, DateOfBirth FROM SuperSensitiveCustomerData;
-- Grant select to the new table
GRANT SELECT ON dbo.MoreSuperSensitiveCustomerData TO DataAnalyst, FinanceGuy;
-- Query as the users
EXECUTE(N'SELECT * FROM dbo.MoreSuperSensitiveCustomerData') AS USER = N'DataAnalyst';
EXECUTE(N'SELECT * FROM dbo.MoreSuperSensitiveCustomerData') AS USER = N'FinanceGuy';
And, they both can see the data without masking. 
Now, I need to do an ALTER TABLE and ALTER COLUMN command to define the masking function to the columns I want to hide.
ALTER TABLE dbo.MoreSuperSensitiveCustomerData ALTER COLUMN LastName ADD MASKED WITH(FUNCTION = 'partial(1,"xxxx", 0)'); ALTER TABLE dbo.MoreSuperSensitiveCustomerData ALTER COLUMN HomeAddress ADD MASKED WITH(FUNCTION = 'default()'); ALTER TABLE dbo.MoreSuperSensitiveCustomerData ALTER COLUMN SSN ADD MASKED WITH(FUNCTION = 'partial(6, "xxxx", 0)'); ALTER TABLE dbo.MoreSuperSensitiveCustomerData ALTER COLUMN Email ADD MASKED WITH(FUNCTION = 'email()'); ALTER TABLE dbo.MoreSuperSensitiveCustomerData ALTER COLUMN CreditCard ADD MASKED WITH(FUNCTION = 'partial(12,"xxxx", 0)'); ALTER TABLE dbo.MoreSuperSensitiveCustomerData ALTER COLUMN DateOfBirth ADD MASKED WITH(FUNCTION = 'default()');Test one more time with the users.
EXECUTE(N'SELECT * FROM dbo.MoreSuperSensitiveCustomerData') AS USER = N'DataAnalyst'; EXECUTE(N'SELECT * FROM dbo.MoreSuperSensitiveCustomerData') AS USER = N'FinanceGuy';If you remember, the DataAnalyst user already had the UNMASK permission so she is able to see the content of the column.
There you have it.
Before I forget, there are some limitations. I guess you already figured out the first one. Yeah, the UNMASK permission has to be on database level. Another limitation is that you can’t mask the columns encrypted with the Always Encrypted feature. There are a few more, but these two are the ones I can remember now being 11:43 p.m.
I hope you liked the post and that you’ve learned something new or refreshed some old knowledge. Also, remember that you can find the code for this and other posts in the following link: https://github.com/learningsql/sql-code
If you have any questions or comments, shoot me an email, leave a message or tweet me here. Don’t forget to like and share. See you soon. Cheers! If you want to receive updates and new blog posts to your inbox, subscribe to my LearningSQL with Beno email list down here. [mc4wp_form id=”331″]