{"id":88221,"date":"2020-09-15T14:34:46","date_gmt":"2020-09-15T14:34:46","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=88221"},"modified":"2022-04-24T21:00:42","modified_gmt":"2022-04-24T21:00:42","slug":"dynamic-data-unmasking","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/dynamic-data-unmasking\/","title":{"rendered":"Dynamic Data Unmasking"},"content":{"rendered":"

Dynamic data masking is a SQL Server 2016 feature to mask sensitive data at the column level from non-privileged users. Hiding SSNs is a common example in the documentation. However, the\u00a0documentation<\/a>\u00a0also gives the following warning:<\/p>\n

\n

The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who should not have access to the data from viewing it. Dynamic data masking does not aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data.<\/em><\/p>\n<\/blockquote>\n

How bad can it be? This post explores how quickly a table of SSNs can be unmasked by a non-privileged user.<\/p>\n

Simple Demo<\/h2>\n

Let\u2019s use a table structure very similar to the example in the documentation:<\/p>\n

DROP TABLE IF EXISTS dbo.People;\r\nCREATE TABLE dbo.People\r\n(\r\nPersonID BIGINT PRIMARY KEY,\r\nFirstName VARCHAR(100) NOT NULL,\r\nLastName VARCHAR(100) NOT NULL,\r\nSSN VARCHAR(11) MASKED WITH (FUNCTION = 'default()') NULL\r\n);\r\nINSERT INTO dbo.People\r\nVALUES\r\n(1, 'Pablo', 'Blanco', '123-45-6789');<\/pre>\n
Here\u2019s what the data looks like for a privileged user, such as a user with sa:<\/p>\n
\"a12_sa_results\"<\/p>\n
However, if I login with my lowly\u00a0erik<\/code>\u00a0SQL Server login I can no longer see Pablo Blanco\u2019s SSN:<\/p>\n
\"a12_erik_results\"<\/p>\n
Test Data<\/h2>\n
To make things more interesting let\u2019s load a million rows into the table. SSNs will be randomized but I didn\u2019t bother randomizing the first and last names.<\/p>\n
DROP TABLE IF EXISTS dbo.People;\r\nCREATE TABLE dbo.People\r\n(\r\nPersonID BIGINT PRIMARY KEY,\r\nFirstName VARCHAR(100) NOT NULL,\r\nLastName VARCHAR(100) NOT NULL,\r\nSSN VARCHAR(11) MASKED WITH (FUNCTION = 'default()') NULL\r\n);\r\nINSERT INTO dbo.People WITH (TABLOCK)\r\nSELECT TOP (1000000)\r\nROW_NUMBER() OVER (ORDER BY (SELECT NULL)),\r\nREPLICATE('A', 10),\r\nREPLICATE('Z', 12),\r\nRIGHT('000' + CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(11)), 3) + '-'\r\n+ RIGHT('00' + CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(11)), 2) + '-'\r\n+ RIGHT('0000' + CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(11)), 4)\r\nFROM master..spt_values AS t1\r\nCROSS JOIN master..spt_values AS t2;<\/pre>\n
How quickly can the malicious end user\u00a0erik<\/code><\/a>\u00a0decode all of the data? Does he really require a set of exhaustive queries? To make things somewhat realistic, setting trace flags and creating objects is off limits. Only temp tables can be created, since all users can do that.<\/p>\n
Decoding the SSN Format<\/h2>\n
The\u00a0WHERE\u00a0clause of queries can be used to infer information about the data. For example, the following query is protected by data masking because all of the action is in the\u00a0SELECT<\/code>\u00a0clause:<\/p>\n
SELECT PersonId,\r\nFirstName,\r\nLastName,\r\nCASE LEFT(SSN, 1)\r\nWHEN '0' THEN\r\n'0'\r\nWHEN '1' THEN\r\n'1'\r\nWHEN '2' THEN\r\n'2'\r\nWHEN '3' THEN\r\n'3'\r\nWHEN '4' THEN\r\n'4'\r\nWHEN '5' THEN\r\n'5'\r\nWHEN '6' THEN\r\n'6'\r\nWHEN '7' THEN\r\n'7'\r\nWHEN '8' THEN\r\n'8'\r\nWHEN '9' THEN\r\n'9'\r\nELSE\r\nNULL\r\nEND AS D1\r\nFROM dbo.People;<\/pre>\n
However, the following query will only return the subset of rows with 1 as the first digit in their SSNs:<\/p>\n
SELECT PersonId,\r\nFirstName,\r\nLastName\r\nFROM dbo.People\r\nWHERE LEFT(SSN, 1) = 1;<\/pre>\n
With 90 queries we could get all of the information that we need, but that\u2019s too much work. First we need to verify the format of the SSN in the column. Perhaps it has dashes and perhaps it doesn\u2019t. Let\u2019s say that our malicious end user gets lucky and both of the following queries return a count of one million rows:<\/p>\n
SELECT COUNT(*)\r\nFROM dbo.People\r\nWHERE LEN(SSN) = 11;\r\nSELECT COUNT(*)\r\nFROM dbo.People\r\nWHERE LEN(REPLACE(SSN, '-', '')) = 9;<\/pre>\n
It\u2019s a reasonable assumption that the SSN is in a XXX-XX-XXXX format, even though the data mask doesn\u2019t tell us that directly.<\/p>\n
Looping to Victory<\/h2>\n
Armed with our new knowledge, we can create a single SQL query that decodes all of the SSNs. The strategy is to define a single CTE with all ten digits and to use one\u00a0CROSS APPLY<\/code>\u00a0for each digit in the SSN. Each\u00a0CROSS APPLY<\/code>\u00a0only references the SSN column in the\u00a0WHERE<\/code>\u00a0clause and returns the matching prefix of the SSN that we\u2019ve found so far. Here\u2019s a snippet of the code:<\/p>\n
SELECT p.PersonID,\r\nd9.real_ssn\r\nFROM dbo.People AS p\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d0.DIGIT + '%'\r\n) AS d1(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd1.prefix + d0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d1.prefix + d0.DIGIT + '%'\r\n) AS d2(prefix);<\/pre>\n
In the\u00a0d1\u00a0derived table the first digit is found. That digit is passed to the\u00a0d2<\/code>\u00a0derived table and the first two digits are returned from\u00a0d2<\/code>. This continues all the way to\u00a0d9<\/code>\u00a0which has the full SSN. The full query is below:<\/p>\n
DROP TABLE IF EXISTS #t;\r\nWITH DIGITS (DIGIT)\r\nAS (SELECT *\r\nFROM\r\n(\r\nVALUES\r\n('0'),\r\n('1'),\r\n('2'),\r\n('3'),\r\n('4'),\r\n('5'),\r\n('6'),\r\n('7'),\r\n('8'),\r\n('9')\r\n) AS v (x) )\r\nSELECT p.PersonID,\r\np.FirstName,\r\np.LastName,\r\nd9.real_ssn\r\nINTO #t\r\nFROM dbo.People AS p\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d0.DIGIT + '%'\r\n) AS d1(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd1.prefix + d0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d1.prefix + d0.DIGIT + '%'\r\n) AS d2(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd2.prefix + d0.DIGIT + '-'\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d2.prefix + d0.DIGIT + '%'\r\n) AS d3(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd3.prefix + d0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d3.prefix + d0.DIGIT + '%'\r\n) AS d4(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd4.prefix + d0.DIGIT + '-'\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d4.prefix + d0.DIGIT + '%'\r\n) AS d5(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd5.prefix + d0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d5.prefix + d0.DIGIT + '%'\r\n) AS d6(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd6.prefix + d0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d6.prefix + d0.DIGIT + '%'\r\n) AS d7(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd7.prefix + d0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d7.prefix + d0.DIGIT + '%'\r\n) AS d8(prefix)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd8.prefix + d0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE p.SSN LIKE d8.prefix + d0.DIGIT + '%'\r\n) AS d9(real_ssn);<\/pre>\n
On my machine, this query takes an average of 5952 ms to finish. Here\u2019s a sample of the results:<\/p>\n
\"a12_sample_results\"<\/p>\n
Not bad to unmask one million SSNs.<\/p>\n
Looping Even Faster to Victory<\/h2>\n
The\u00a0LIKE<\/code>\u00a0operator is a bit heavy for what we\u2019re doing. Another way to approach the problem is to have each derived table just focus on a single digit and to concatenate them all together at the end. I found\u00a0SUBSTRING<\/code>\u00a0to be the fastest way to do this. The full query is below:<\/p>\n
DROP TABLE IF EXISTS #t;\r\nWITH DIGITS (DIGIT)\r\nAS (SELECT *\r\nFROM\r\n(\r\nVALUES\r\n('0'),\r\n('1'),\r\n('2'),\r\n('3'),\r\n('4'),\r\n('5'),\r\n('6'),\r\n('7'),\r\n('8'),\r\n('9')\r\n) AS v (x) )\r\nSELECT p.PersonID,\r\np.FirstName,\r\np.LastName,\r\nd1.DIGIT + d2.DIGIT + d3.DIGIT + '-' + d4.DIGIT + d5.DIGIT + '-' \r\n     + d6.DIGIT + d7.DIGIT + d8.DIGIT + d9.DIGIT AS real_ssn\r\nINTO #t\r\nFROM dbo.People AS p\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 1, 1) = d0.DIGIT\r\n) AS d1(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 2, 1) = d0.DIGIT\r\n) AS d2(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 3, 1) = d0.DIGIT\r\n) AS d3(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 5, 1) = d0.DIGIT\r\n) AS d4(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 6, 1) = d0.DIGIT\r\n) AS d5(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 8, 1) = d0.DIGIT\r\n) AS d6(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 9, 1) = d0.DIGIT\r\n) AS d7(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 10, 1) = d0.DIGIT\r\n) AS d8(DIGIT)\r\nCROSS APPLY\r\n(\r\nSELECT TOP 1\r\nd0.DIGIT\r\nFROM DIGITS AS d0\r\nWHERE SUBSTRING(p.SSN, 11, 1) = d0.DIGIT\r\n) AS d9(DIGIT);<\/pre>\n
 <\/p>\n
This query runs in an average on 1833 ms on my machine. The query plan looks as you might expect. Each cross apply is implemented as a parallel nested loop join against a constant scan of 10 values. On average each constant scan operator produces roughly 5.5 million rows. This makes sense, since for each loop we\u2019ll need to check an average of 5.5 values before finding a match, assuming perfectly distributed random digits. Here\u2019s a representative part of the plan:<\/p>\n
\"a12_query1\"<\/p>\n
Letting SQL Server do the Work<\/h2>\n
With nine digits we end up reading almost 50 million values from the constant scan operators. That\u2019s a lot of work. Can we write a simpler query and let SQL Server do the work for us? We know that SSNs are always numeric, so if we had a table full of all billion possible SSNs then we could join to that and just keep the value from the table. Populating a temp table with a billion rows will take too long, but we can simply split up the SSN into its natural three parts and join to those tables. One way to do this is below:<\/p>\n
SELECT TOP (100)\r\nRIGHT('0' + CAST(t.RN AS VARCHAR(10)), 2) AS NUM\r\nINTO #t_100\r\nFROM\r\n(\r\nSELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN\r\nFROM master..spt_values AS t1\r\nCROSS JOIN master..spt_values AS t2\r\n) AS t;\r\nSELECT TOP (1000)\r\nRIGHT('00' + CAST(t.RN AS VARCHAR(10)), 3) AS NUM\r\nINTO #t_1000\r\nFROM\r\n(\r\nSELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN\r\nFROM master..spt_values AS t1\r\nCROSS JOIN master..spt_values AS t2\r\n) AS t;\r\nSELECT TOP (10000)\r\nRIGHT('000' + CAST(t.RN AS VARCHAR(10)), 4) AS NUM\r\nINTO #t_10000\r\nFROM\r\n(\r\nSELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN\r\nFROM master..spt_values AS t1\r\nCROSS JOIN master..spt_values AS t2\r\n) AS t;\r\nDROP TABLE IF EXISTS #t;\r\nSELECT p.PersonID,\r\np.FirstName,\r\np.LastName,\r\nCONCAT(t1000.NUM, '-', t100.NUM, '-', t10000.NUM) AS SSN\r\nINTO #t\r\nFROM dbo.People AS p\r\nLEFT OUTER JOIN #t_1000 AS t1000\r\nON SUBSTRING(p.SSN, 1, 3) = t1000.NUM\r\nLEFT OUTER JOIN #t_100 AS t100\r\nON SUBSTRING(p.SSN, 5, 2) = t100.NUM\r\nLEFT OUTER JOIN #t_10000 AS t10000\r\nON SUBSTRING(p.SSN, 8, 4) = t10000.NUM;<\/pre>\n
The query now runs in an average of 822 ms. Note that I didn\u2019t try very hard to optimize the inserts into the temp tables because they finish almost instantly. Taking a look at the plan, we see a lot of repartition stream operators because the column for the hash join is different for each query:<\/p>\n
\"a12_repartition\"<\/p>\n
Can we go faster?<\/p>\n
Batch Mode to the Rescue<\/h2>\n
With parallel batch mode hash joins we don\u2019t need to repartition the streams of the larger outer result set. I changed the query to only look at the table with 10000 rows to get more consistent and even parallel row distribution on the temp tables. I also added a clustered index on the temp table for the same reason. In addition to that, maybe we can expect\u00a0joins to be faster<\/a>\u00a0with\u00a0INT<\/code>\u00a0join columns as opposed to\u00a0VARCHAR<\/code>. With the canonical\u00a0#BATCH_MODE_PLZ<\/code>\u00a0temp table to make the query eligible for batch mode, the query now looks like this:<\/p>\n
SELECT TOP (100000)\r\nISNULL(CAST(t.RN AS INT), 0) AS NUM\r\nINTO #t_10000\r\nFROM\r\n(\r\nSELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN\r\nFROM master..spt_values AS t1\r\nCROSS JOIN master..spt_values AS t2\r\n) AS t;\r\nCREATE CLUSTERED INDEX CI ON #t_10000 (NUM);\r\nCREATE TABLE #BATCH_MODE_PLZ\r\n(\r\nI INT,\r\nINDEX C CLUSTERED COLUMNSTORE\r\n);\r\nDROP TABLE IF EXISTS #t;\r\nSELECT p.PersonID,\r\np.FirstName,\r\np.LastName,\r\nCONCAT(t1000.NUM, '-', t100.NUM, '-', t10000.NUM) AS SSN\r\nINTO #t\r\nFROM dbo.People AS p\r\nLEFT OUTER JOIN #t_10000 AS t1000\r\nON CAST(SUBSTRING(p.SSN, 1, 3) AS INT) = t1000.NUM\r\nLEFT OUTER JOIN #t_10000 AS t100\r\nON CAST(SUBSTRING(p.SSN, 5, 2) AS INT) = t100.NUM\r\nLEFT OUTER JOIN #t_10000 AS t10000\r\nON CAST(SUBSTRING(p.SSN, 8, 4) AS INT) = t10000.NUM\r\nLEFT OUTER JOIN #BATCH_MODE_PLZ\r\nON 1 = 0;<\/pre>\n
The query now runs in an average of 330 ms. The repartition stream operators are no longer present:<\/p>\n
\"a12_no_repart\"<\/p>\n
It wasn\u2019t clear to me how to speed this query up further. The probe residuals in the hash joins are one target:<\/p>\n
\"a12_probe\"<\/p>\n
These appear because SQL Server cannot guarantee that hash collisions won\u2019t occur.\u00a0Paul White<\/a>\u00a0points out the following:<\/p>\n
\n
If the join is on a single column typed as TINYINT, SMALLINT or INTEGER and if both columns are constrained to be NOT NULL, the hash function is \u2018perfect\u2019 \u2013 meaning there is no chance of a hash collision, and the query processor does not have to check the values again to ensure they really match.<\/em><\/p>\n<\/blockquote>\n
Unfortunately, the probe residual remains even with the right temp table definition and adding explicit casts and non-null guarantees to the\u00a0SUBSTRING<\/code>\u00a0expression. Perhaps the type information is lost in the plan and cannot be taken advantage of.<\/p>\n
Final Thoughts<\/h2>\n
I don\u2019t think that there\u2019s really anything new here. This was mostly done for fun. Decoding a million SSNs in half a second is a good trick and a good reminder to be very careful with expectations around how much security data masking really gives you. Thanks for reading!<\/p>\n
Editor\u2019s note: This article was originally published at erikdarlingdata.com<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
The SQL Server 2016 dynamic data masking feature may seem like a great way to obfuscate data for downstream systems like dev and QA. Joe Obbish shows us that the data can be \u201cunmasked\u201d with T-SQL statements, so it\u2019s not secure against anyone who can write their own queries.…<\/p>\n","protected":false},"author":334526,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[124820],"coauthors":[124818],"class_list":["post-88221","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-data-masker"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/334526"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=88221"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88221\/revisions"}],"predecessor-version":[{"id":88232,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88221\/revisions\/88232"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=88221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=88221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=88221"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=88221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}