July 20, 2015 1 Comment
In today’s IT landscape, data security is a big deal. Just ask people like Troy Hunt (Blog | @troyhunt), who is a security expert, how losing data can destroy your company. Companies all over the world lose data every single day, and you might not even be aware of this. I’m still seeing companies who give all IT employees admin permission on the whole IT infrastructure: from webserver to database servers, and from customer relations system to financial system. But why is it still a struggle for IT professionals to protect their data? In some companies they don’t even hash passwords, let alone protect them with a certificate or even heavier protection.
In this blog I’m not going to tell you how to secure your data, because honestly I’m not an expert at that. I do want to introduce you to a new way to obfuscate your data, that was introduced in SQL Server 2016. According to Wikipedia obfuscation means: “(…)the obscuring of intended meaning in communication, making the message confusing, willfully ambiguous, or harder to understand”. Or in easy terms: make it harder to read for your users and/or employees. Let’s take a look at an example.
If I would give you an email address like “JohnDoe@domain.com”, it’s really easy to guess the name of the person. Let’s be honest, it’s not rocket science right?! But what if I would give you the email address “Jo*****@d*****.com”, would you still guess the name correctly? Yes I agree with you there’s a small chance you might guess the name right, but those chances are slim. And instead of writing your own code to obfuscate data this way, with SQL Server 2016 you’ll get it out of the box.
The following is tested on SQL Server CTP 2.1. If you’re not running that version or higher, this might mean the scripts or test cases might not work the same as in my case.
In order to test this new feature, let’s create a new database first:
USE master GO CREATE DATABASE DynamicDataMasking ON PRIMARY (NAME = N'DynamicDataMasking', FILENAME = N'C:\Database\DynamicDataMasking.mdf') LOG ON (NAME = N'DynamicDataMasking_log', FILENAME = N'C:\Database\DynamicDataMasking_log.ldf') GO
Now let’s create a table called Users:
CREATE TABLE dbo.Users (UserID INT IDENTITY(1,1), FirstName VARCHAR(100), LastName VARCHAR(100), Address VARCHAR(100), DateOfBirth DATE, SocialSecurityNumber VARCHAR(15), Email VARCHAR(100))
This table contains some basic information about the users of our application. And of course we need some user data in the table. I’ve used Redgate’s SQL Data Generator to generate data for my table. Because I’ve generated a full table, I’ve made the script available here.
Now that we have a table with sensitive data, let’s look at the ways of obfuscating this data. From SQL Server 2016 onwards you get to use the Dynamic Data Masking feature. This means you can add a “mask” on top of your data. This is done by altering the columns in your table:
ALTER TABLE <TableName> ALTER COLUMN <ColumnName> ADD MASKED WITH (FUNCTION = '<FunctionName>')
This takes care of the data obfuscation for you, and you can determine the level of obfuscation by using specific functions:
Default: Completely replaces the value in the column with “xxxx”
Email: Replaces the email address with X’s, except for the first character, and the Top-level domain
Custom: The most dynamic of the 3 functions. This allows you to configure a prefix and suffix, and replaces the rest of the values with X’s
Let’s add masks to the columns in our table:
ALTER TABLE dbo.Users ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = 'default()') ALTER TABLE dbo.Users ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXXXX",1)') ALTER TABLE dbo.Users ALTER COLUMN Address ADD MASKED WITH (FUNCTION = 'partial(10, "XXXXX", 0)') ALTER TABLE dbo.Users ALTER COLUMN City ADD MASKED WITH (FUNCTION = 'partial(3, "XXXXX", 0)') ALTER TABLE dbo.Users ALTER COLUMN State ADD MASKED WITH (FUNCTION = 'partial(4, "XXXXX", 0)') --Country no masking ALTER TABLE dbo.Users ALTER COLUMN DateOfBirth ADD MASKED WITH (FUNCTION = 'default()') ALTER TABLE dbo.Users ALTER COLUMN SocialSecurityNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)') ALTER TABLE dbo.Users ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
Testing data masking
Now that we have a table with masked data, how does that look from different perspectives? Let’s create 2 users: an application login with just read permissions, and an admin login with db_owner permissions:
USE master GO CREATE LOGIN [AppLogin] WITH PASSWORD = 'TestPass', --SID = 0xDB9ED49EF06F3A40B5024B36F5CDD658, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF GO CREATE LOGIN [AdminLogin] WITH PASSWORD = 'TestPass', --SID = 0xDB9ED49EF06F3A40B5024B36F5CDD658, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF GO USE DynamicDataMasking GO CREATE USER AppLogin FOR LOGIN AppLogin WITH DEFAULT_SCHEMA = dbo GO ALTER ROLE db_datareader ADD MEMBER AppLogin GO CREATE USER AdminLogin FOR LOGIN AdminLogin WITH DEFAULT_SCHEMA = dbo GO ALTER ROLE db_owner ADD MEMBER AdminLogin GO
Now open up 2 new SSMS tabs, and log in with the 2 accounts. In both sessions run the query below:
USE DynamicDataMasking GO SELECT * FROM dbo.Users
The difference is clearly visible when you select the top 10 records in both sessions:
In the admin connection you get to see the full dataset, and in the application login the masked dataset is returned.
The retrieval and querying of data isn’t changed when you have data masks applied. If you run a query where you search for users with a specific first name, the correct results is returned:
SELECT * FROM dbo.Users WHERE FirstName = 'Jeffrey'
Now we get to the interesting part: the security issues. Even though this is not a form of data encryption, I did expect this could be a part of data security. But unfortunately this was a false sense of security…
Let’s try a normal query on the masked data, with the AppLogin session:
USE DynamicDataMasking GO SELECT * FROM dbo.Users
This returns the data as expected: masked. But is there any way around this? Let’s make the AppLogin a db_owner on another database (run this statement with a user that has permissions to do this):
USE Sandbox GO CREATE USER AppLogin FOR LOGIN AppLogin WITH DEFAULT_SCHEMA = dbo GO ALTER ROLE db_owner ADD MEMBER AppLogin GO
So the AppLogin is still db_datareader on the DynamicDataMasking database, and db_owner on the Sandbox database. Now let’s run the query below:
USE Sandbox GO SELECT * FROM DynamicDataMasking.dbo.Users
Well, that’s awkward! If your application login has db_owner permissions on another database, you can query the data from the users table without the data masks!
But I know what you’re thinking. My application logins aren’t db_owner anywhere so I’m safe. WRONG! Let me show you another example:
USE DynamicDataMasking GO SELECT U1.* FROM dbo.Users U1 INNER JOIN dbo.Users U2 ON U2.USerID = U1.UserID
Well hello precious data! If you just join a table to ANY other table (or join it to itself), all data is returned without masking.
This must be all right? There’s one last thing…
Let’s try to insert the masked data into a temp table, and select that result:
USE DynamicDataMasking GO SELECT * INTO #UsersTemp FROM dbo.Users SELECT * FROM #UsersTemp DROP TABLE #UsersTemp
I’m not sure what the idea behind of this feature was, or how the SQL Server designed it, but this sounds like a real security issue to me. I expect my data to be obfuscated for my application users, but it’s the complete opposite. If you have a direct connection to the database (and not through an application only) it’s really easy to get a hold of the unmasked data.
Even though I really like this feature (and yes, I’m serious!) there are some things the SQL Server team needs to look at. If you’re expecting your data to be masked for all non-admin users, you’re in for a nasty surprise.
Hoping this will be fixed soon, I’ve created a Connect item for this. If you agree with me on this, and you think it’s a problem as well, please up-vote it or leave a comment in the connect item. There is also a specific item for the join issue. Let’s make sure the SQL Server team is aware of these issues.