{"id":26278,"date":"2016-06-06T16:50:06","date_gmt":"2016-06-06T16:50:06","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/unmasking-the-dynamic-data-masking\/"},"modified":"2016-09-20T11:48:42","modified_gmt":"2016-09-20T11:48:42","slug":"unmasking-the-dynamic-data-masking","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/unmasking-the-dynamic-data-masking\/","title":{"rendered":"Unmasking the Dynamic Data Masking"},"content":{"rendered":"

\/*<\/span><\/p>\n

Revised 27th June<\/span><\/span><\/p>\n

 <\/span><\/p>\n

Dynamic data masking is a great product and solves some niche problems that come if you need to do certain testing with live data at the application level. You should, however, beware of using it as a database-level security device.<\/span><\/p>\n

I haven't yet used it in testing because I don't have the problem that it solves.<\/span><\/p>\n

The problem that it solves is for people doing testing, especially user-acceptance testing, of an application using live data. It is good at masking data where the user is unable to make direct access to the database to execute queries.<\/span><\/p>\n

If you want to do user-acceptance testing, you simply use a database role for whom the data is masked. Nothing else in the data or application needs to be changed, though you need to be careful about some functions such as editing of (potentially masked) data.<\/span><\/span><\/p>\n

Dynamic data masking isn't an effective database-level security device. A determined person can still get at the data. The algorithm is greatly improved over the earlier pre-release versions of SQL Server 2016, but it isn't yet perfect. It can still be hacked. Let me demonstrate *\/<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

USE<\/span> PhilFactor<\/span>    <\/span>\/*we'll use our sample SQL Server 2016 database<\/span><\/span><\/p>\n

 <\/span><\/span><\/span><\/p>\n

The first thing we do is to create a number table if it doesn't exist.<\/span><\/p>\n

*\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/* Drop table numbers (only execute this if you're making changes) *\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

IF<\/span> NOT<\/span> EXISTS<\/span> <\/span>(<\/span>SELECT<\/span> 1 FROM<\/span> information_schema<\/span>.<\/span>tables<\/span> WHERE<\/span> Table_Name<\/span>=<\/span>'numbers'<\/span>)<\/span><\/span><\/p>\n

       <\/span> CREATE<\/span> TABLE<\/span> numbers<\/span> <\/span> (<\/span>number<\/span> INT<\/span> PRIMARY<\/span> KEY<\/span>);<\/span><\/span><\/p>\n

IF<\/span> NOT<\/span> EXISTS<\/span> <\/span>(<\/span>SELECT<\/span> 1 FROM<\/span> numbers<\/span> WHERE<\/span> number<\/span>=<\/span>10)<\/span><\/span><\/p>\n

  <\/span>BEGIN<\/span>     <\/span><\/span><\/p>\n

  <\/span>DECLARE<\/span> @digits<\/span> TABLE <\/span> (<\/span>n<\/span> CHAR<\/span>)<\/span><\/span><\/p>\n

  <\/span>INSERT<\/span> INTO<\/span> @Digits<\/span>(<\/span>n<\/span>)<\/span><\/span><\/p>\n

    <\/span> SELECT<\/span> X<\/span>.<\/span>i<\/span><\/span><\/p>\n

    <\/span> FROM <\/span>(<\/span>SELECT <\/span>(<\/span>'1'<\/span>)<\/span>UNION<\/span> SELECT <\/span>(<\/span>'2'<\/span>)<\/span>UNION<\/span> SELECT <\/span>(<\/span>'3'<\/span>)<\/span>UNION<\/span> SELECT <\/span>(<\/span>'4'<\/span>)<\/span><\/span><\/p>\n

             <\/span>UNION<\/span> SELECT <\/span>(<\/span>'5'<\/span>)<\/span> UNION<\/span> SELECT <\/span> (<\/span>'6'<\/span>)<\/span>UNION<\/span> SELECT <\/span>(<\/span>'7'<\/span>)<\/span><\/span><\/p>\n

                <\/span>UNION<\/span> SELECT <\/span>(<\/span>'8'<\/span>)<\/span>UNION<\/span> SELECT <\/span>(<\/span>'9'<\/span>)<\/span>UNION<\/span> SELECT<\/span>(<\/span>'0'<\/span>))<\/span><\/span><\/p>\n

      <\/span> AS<\/span> X<\/span>(<\/span>i<\/span>);<\/span><\/span><\/p>\n

  <\/span>INSERT<\/span> INTO<\/span> Numbers<\/span><\/span><\/p>\n

       <\/span> SELECT<\/span> convert<\/span>(<\/span>INT<\/span>,<\/span> D1<\/span>.<\/span>n<\/span> +<\/span> D2<\/span>.<\/span>n<\/span> +<\/span> D3<\/span>.<\/span>n<\/span> +<\/span> D4<\/span>.<\/span>n<\/span> +<\/span> D5<\/span>.<\/span>n<\/span> +<\/span> D6<\/span>.<\/span>n<\/span>)<\/span> AS<\/span> number<\/span><\/span><\/p>\n

    <\/span> FROM<\/span> @Digits<\/span> AS<\/span> D1<\/span>,<\/span> @Digits<\/span> AS<\/span> D2<\/span>,<\/span> @Digits<\/span> AS<\/span> D3<\/span>,<\/span> @Digits<\/span> AS<\/span> D4<\/span>,<\/span> @Digits<\/span> AS<\/span> D5<\/span>,<\/span> @Digits<\/span> AS<\/span> D6<\/span><\/span><\/p>\n

       <\/span> ORDER<\/span> BY<\/span> number<\/span>;<\/span><\/span><\/p>\n

  <\/span>DELETE<\/span> FROM<\/span> numbers<\/span> WHERE<\/span> number<\/span>=<\/span>0;<\/span>  <\/span><\/span><\/p>\n

  <\/span>END<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/*<\/span><\/p>\n

Now we will set up a mock table with some credit card numbers in it and some security digits. We will set up dynamic data masking<\/span><\/p>\n

*\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

 <\/span>revert<\/span><\/span><\/p>\n

IF <\/span> (<\/span>object_id<\/span>(<\/span>'dbo.MyCreditCards'<\/span>)<\/span> IS<\/span> NOT<\/span> NULL)<\/span><\/span><\/p>\n

       <\/span> DROP<\/span> TABLE<\/span> dbo<\/span>.<\/span>MyCreditCards<\/span><\/span><\/p>\n

GO<\/span><\/p>\n

CREATE<\/span> TABLE<\/span> dbo<\/span>.<\/span>MyCreditCards<\/span> <\/span>(<\/span><\/span><\/p>\n

  <\/span> creditCard_ID<\/span> INT<\/span> IDENTITY<\/span>,<\/span><\/span><\/p>\n

  <\/span>Provider<\/span> VARCHAR<\/span>(<\/span>30),<\/span><\/span><\/p>\n

  <\/span>Number<\/span> VARCHAR<\/span>(<\/span>100)<\/span> MASKED<\/span> WITH <\/span> (<\/span>FUNCTION<\/span> =<\/span> 'partial(1,"XXXXXXX",0)'<\/span>)<\/span> NULL,<\/span><\/span><\/p>\n

  <\/span>digits<\/span> int<\/span> MASKED<\/span> WITH <\/span>(<\/span>FUNCTION<\/span> =<\/span> 'default()'<\/span>)<\/span> NULL,<\/span><\/span><\/p>\n

  <\/span>Balance<\/span> money<\/span> MASKED<\/span> WITH <\/span>(<\/span>FUNCTION<\/span> =<\/span> 'default()'<\/span>)<\/span> NULL,<\/span><\/span><\/p>\n

  <\/span> Credit_index<\/span> float<\/span> MASKED<\/span> WITH <\/span> (<\/span>FUNCTION<\/span> =<\/span> 'default()'<\/span>)<\/span> NULL,<\/span><\/span><\/p>\n

  <\/span> Known_Default_Average<\/span> Decimal<\/span>(<\/span>8,<\/span>2)<\/span> MASKED<\/span> WITH <\/span> (<\/span>FUNCTION<\/span> =<\/span> 'default()'<\/span>)<\/span> NULL<\/span><\/span><\/p>\n

  <\/span>)<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/* now we will set up the table with some mock data *\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

INSERT<\/span> INTO<\/span> MyCreditCards<\/span> <\/span>(<\/span>Provider<\/span>,<\/span>Number<\/span>,<\/span>digits<\/span>,<\/span> balance<\/span>,<\/span> Credit_index<\/span>,<\/span>Known_Default_Average<\/span>)<\/span><\/span><\/p>\n

 <\/span>SELECT<\/span> provider<\/span>,<\/span> number<\/span>,<\/span> digits<\/span>,<\/span> balance<\/span>,<\/span> Credit_index<\/span>,<\/span>Known_Default_Average<\/span> FROM <\/span>(<\/span>VALUES<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Airplus'<\/span>,<\/span>'122000000000003'<\/span>,<\/span> 563,<\/span> 345.23,<\/span>2.444,<\/span> 23.23),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'American Express'<\/span>,<\/span>'34343434343434'<\/span>,<\/span> 143,<\/span> 56.44,<\/span> 1.45367,<\/span>87.34),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'American Express'<\/span>,<\/span>'378282246310005'<\/span>,<\/span> 541,<\/span> 234.98,<\/span> 1.455,<\/span>45.32),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'American Express'<\/span>,<\/span>'371449635398431'<\/span>,<\/span> 243,<\/span> 249.00,<\/span> 1.397,<\/span> 19.45),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'American Express Corporate'<\/span>,<\/span>'378734493671000'<\/span>,<\/span>576,<\/span> 45.78,<\/span> 4.00,<\/span> 83.20),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Australian BankCard'<\/span>,<\/span>'5610591081018250'<\/span>,<\/span>837,<\/span> 341.727,<\/span> 86.5510803384479,<\/span> 1.16),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Dankort (PBS)'<\/span>,<\/span>'5019717010103742'<\/span>,<\/span>717,<\/span> 979.6372,<\/span> 28.0325248353335,<\/span> 1.70),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Diners Club'<\/span>,<\/span>'30569309025904'<\/span>,<\/span>813,<\/span> 492.1039,<\/span> 19.9180960566355,<\/span> 2.67),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Diners Club'<\/span>,<\/span>'38520000023237'<\/span>,<\/span>595,<\/span> 764.2249,<\/span> 97.7891119833985,<\/span> 0.57),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Diners Club'<\/span>,<\/span>'36700102000000'<\/span>,<\/span>764,<\/span> 190.1438,<\/span> 64.5213314404345,<\/span> 0.28),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Diners Club'<\/span>,<\/span>'36148900647913'<\/span>,<\/span>557,<\/span> 382.0786,<\/span> 26.3416622622598,<\/span> 0.39),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Discover card'<\/span>,<\/span>'6011000400000000'<\/span>,<\/span>802,<\/span> 229.0121,<\/span> 68.0298162759421,<\/span> 0.37),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Discover card'<\/span>,<\/span>'6011111111111117'<\/span>,<\/span>433,<\/span> 871.6858,<\/span> 37.9636457995342,<\/span> 2.63),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Discover card'<\/span>,<\/span>'6011000990139424'<\/span>,<\/span>338,<\/span> 389.1325,<\/span> 51.7720675555859,<\/span> 1.91),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'JCB'<\/span>,<\/span>'3530111333300000'<\/span>,<\/span>275,<\/span> 563.3207,<\/span> 93.2751041740529,<\/span> 0.90),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'JCB'<\/span>,<\/span>'3566002020360505'<\/span>,<\/span>872,<\/span> 595.6164,<\/span> 33.9023412614606,<\/span> 0.20),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'JCB'<\/span>,<\/span>'3528000700000000'<\/span>,<\/span>712,<\/span> 460.8667,<\/span> 24.9165346314742,<\/span> 0.06),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Laser'<\/span>,<\/span>'630495060000000000'<\/span>,<\/span>139,<\/span> 730.9588,<\/span> 42.4225722171471,<\/span> 0.31),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Laser'<\/span>,<\/span>'630490017740292441'<\/span>,<\/span>701,<\/span> 188.6773,<\/span> 71.1843278921136,<\/span> 0.68),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Maestro'<\/span>,<\/span>'6759649826438453'<\/span>,<\/span>196,<\/span> 807.5152,<\/span> 51.2412214103347,<\/span> 0.62),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Maestro'<\/span>,<\/span>'6799990100000000019'<\/span>,<\/span>573,<\/span> 150.622,<\/span> 77.7391501500919,<\/span> 1.20),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Mastercard'<\/span>,<\/span>'5555555555554444'<\/span>,<\/span>949,<\/span> 363.7482,<\/span> 78.6266174333294,<\/span> 0.61),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Mastercard'<\/span>,<\/span>'5454545454545454'<\/span>,<\/span>357,<\/span> 906.3862,<\/span> 53.1121607996114,<\/span> 0.44),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'MasterCard'<\/span>,<\/span>'5105105105105100'<\/span>,<\/span>207,<\/span> 146.4457,<\/span> 18.1780982223237,<\/span> 0.60),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Switch\/Solo (Paymentech)'<\/span>,<\/span>'6331101999990016'<\/span>,<\/span>952,<\/span> 334.8059,<\/span> 72.4790652373242,<\/span> 0.32),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa'<\/span>,<\/span>'4111111111111111'<\/span>,<\/span>989,<\/span> 120.9948,<\/span> 26.132675796809,<\/span>1.16),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa'<\/span>,<\/span>'4012888888881881'<\/span>,<\/span>555,<\/span> 751.5164,<\/span> 99.6884540550823,<\/span> 0.72),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa'<\/span>,<\/span>'4222222222222'<\/span>,<\/span>364,<\/span> 462.6201,<\/span> 63.5308831811561,<\/span> 2.40),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa'<\/span>,<\/span>'4444333322221111'<\/span>,<\/span>615,<\/span> 922.3543,<\/span> 27.8983184452626,<\/span> 1.86),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa'<\/span>,<\/span>'4911830000000'<\/span>,<\/span>681,<\/span> 744.0226,<\/span> 84.3608046245579,<\/span> 2.82),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa'<\/span>,<\/span>'4917610000000000'<\/span>,<\/span>557,<\/span> 151.2514,<\/span> 79.6490148839769,<\/span> 1.31),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa Debit'<\/span>,<\/span>'4462030000000000'<\/span>,<\/span>734,<\/span> 564.2765,<\/span> 42.9801020044741,<\/span> 0.85),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa Debit'<\/span>,<\/span>'4917610000000000003'<\/span>,<\/span>790,<\/span> 387.2116,<\/span> 1.67513696647954,<\/span> 1.88),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa Electron (UK only)'<\/span>,<\/span>'4917300800000000'<\/span>,<\/span>390,<\/span> 599.0702,<\/span> 87.6530341495075,<\/span> 0.45),<\/span><\/span><\/p>\n

   <\/span><\/span> (<\/span>'Visa Purchasing'<\/span>,<\/span>'4484070000000000'<\/span>,<\/span>499,<\/span> 249.4198,<\/span> 6.42316139788514,<\/span> 2.58)<\/span><\/span><\/p>\n

 <\/span>)<\/span>CreditCards<\/span>(<\/span>Provider<\/span>,<\/span>Number<\/span>,<\/span>digits<\/span>,<\/span> balance<\/span>,<\/span> credit_index<\/span>,<\/span>Known_Default_Average<\/span>)<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

 <\/span>Select<\/span> *<\/span> from<\/span> MyCreditCards<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

GO<\/span><\/p>\n

 <\/span>use<\/span> philFactor<\/span><\/span><\/p>\n

\/* we can, of course select all the columns because we have permission to do so as dbo (or whatever) *\/<\/span><\/span><\/p>\n

\"Unmasked.bmp\"<\/a><\/p>\n

\/* now we set up the TestUser who shouldn't receive any sensitive data *\/<\/span><\/p>\n

CREATE<\/span> USER<\/span> TestUser<\/span> WITHOUT<\/span> LOGIN<\/span>;<\/span><\/span><\/p>\n

GRANT<\/span> SELECT<\/span> ON<\/span> dbo<\/span>.<\/span>MyCreditCards<\/span> TO<\/span> TestUser<\/span>;<\/span><\/span><\/p>\n

GRANT<\/span> SELECT<\/span> ON<\/span> dbo<\/span>.<\/span>numbers<\/span> TO<\/span> TestUser<\/span>;<\/span><\/span><\/p>\n

\/* now we can execute a query as the test user and confirm that we can't see the sensitive data *\/<\/span><\/span><\/p>\n

EXECUTE<\/span> AS<\/span> USER<\/span> =<\/span> 'TestUser'<\/span>;<\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> MyCreditCards<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\"Masked.bmp\"<\/a><\/p>\n

\/* REVERT; – we can use this to revert to our normal DBO role *\/<\/span><\/p>\n

SELECT<\/span> CURRENT_USER<\/span> \/* to make sure we are the correct Test User role *\/<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/* However, those digits are as easy to get as taking candy from a baby. For a start, one can get the integers just by joining to a number table *\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

SELECT<\/span> CreditCard_ID<\/span>,<\/span> numbers<\/span>.<\/span>number<\/span> FROM<\/span><\/span><\/p>\n

numbers<\/span> INNER<\/span> JOIN<\/span> MyCreditCards<\/span><\/span><\/p>\n

ON<\/span> numbers<\/span>.<\/span>number<\/span>=<\/span>MyCreditCards<\/span>.<\/span>digits<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/* and money values and approximate numbers can be easily be retrieved too within a single query, but only as floats.<\/span><\/span><\/p>\n

Probably accurate enough for the villains *\/<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

Select<\/span><\/p>\n

  <\/span>square<\/span>(<\/span>sqrt<\/span>(<\/span>convert<\/span>(<\/span>Decimal<\/span>(<\/span>18,<\/span>8),<\/span>digits<\/span>))),<\/span><\/span><\/p>\n

  <\/span>square<\/span>(<\/span>sqrt<\/span>(<\/span>Balance<\/span>)),<\/span><\/span><\/p>\n

  <\/span>square<\/span>(<\/span>sqrt<\/span>(<\/span>convert<\/span>(<\/span>Decimal<\/span>(<\/span>18,<\/span>8),<\/span>credit_index<\/span>))),<\/span><\/span><\/p>\n

  <\/span>square<\/span>(<\/span>sqrt<\/span>(<\/span>Known_Default_Average<\/span>))<\/span><\/span><\/p>\n

    <\/span> from<\/span> MyCreditCards<\/span><\/span><\/p>\n

\/* you can do better if you can put up with a temporary table or table variable *\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

Create<\/span> table<\/span> #temp<\/span>  <\/span><\/span>(<\/span><\/span><\/p>\n

  <\/span>digits<\/span> float<\/span>,<\/span><\/span><\/p>\n

  <\/span>Balance<\/span> float<\/span>,<\/span><\/span><\/p>\n

  <\/span> Credit_index<\/span> float<\/span>,<\/span><\/span><\/p>\n

  <\/span> Known_Default_Average<\/span> float<\/span><\/span><\/p>\n

  <\/span>)<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

insert<\/span> into<\/span> #temp<\/span><\/span><\/p>\n

  <\/span>Select<\/span><\/span><\/p>\n

    <\/span> square<\/span>(<\/span>sqrt<\/span>(<\/span>convert<\/span>(<\/span>Decimal<\/span>(<\/span>18,<\/span>8),<\/span>digits<\/span>))),<\/span><\/span><\/p>\n

    <\/span> square<\/span>(<\/span>sqrt<\/span>(<\/span>Balance<\/span>)),<\/span><\/span><\/p>\n

    <\/span> square<\/span>(<\/span>sqrt<\/span>(<\/span>convert<\/span>(<\/span>Decimal<\/span>(<\/span>18,<\/span>8),<\/span>credit_index<\/span>))),<\/span><\/span><\/p>\n

    <\/span> square<\/span>(<\/span>sqrt<\/span>(<\/span>Known_Default_Average<\/span>))<\/span><\/span><\/p>\n

  <\/span>from<\/span> MyCreditCards<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

Select<\/span> convert<\/span>(<\/span>int<\/span>,<\/span> digits<\/span>),<\/span> convert<\/span>(<\/span>money<\/span>,<\/span>Balance<\/span>),<\/span><\/span><\/p>\n

       <\/span> credit_index<\/span>,<\/span> Convert<\/span>(<\/span>Decimal<\/span>(<\/span>8,<\/span>2),<\/span>Known_Default_Average<\/span>)<\/span><\/span><\/p>\n

  <\/span>from<\/span> #temp<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/*<\/span><\/p>\n

 <\/span><\/span><\/span><\/p>\n

What about the strings? Well, the masking algorithm is quite good at struggling but eventually, you can trick it before breaking into a sweat. Firstly, we will try to mislead it into revealing the string containing the credit card number.*\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

SELECT<\/span> MyCreditCards<\/span>.<\/span>number<\/span>,<\/span><\/span><\/p>\n

(<\/span>SELECT<\/span> char<\/span>(<\/span>TheASCIIValue<\/span>.<\/span>number<\/span>)<\/span><\/span><\/p>\n

 <\/span>FROM<\/span> numbers<\/span> i<\/span><\/span><\/p>\n

   <\/span> INNER<\/span> JOIN<\/span> numbers<\/span> TheASCIIValue<\/span><\/span><\/p>\n

    <\/span> ON<\/span> substring<\/span>(<\/span>MyCreditCards<\/span>.<\/span>number<\/span>,<\/span>i<\/span>.<\/span>number<\/span>,<\/span>1)=<\/span>char<\/span>(<\/span>TheASCIIValue<\/span>.<\/span>number<\/span>)<\/span> COLLATE<\/span> Latin1_General_CS_AS<\/span><\/span><\/p>\n

 <\/span>WHERE<\/span> TheASCIIValue<\/span>.<\/span>number<\/span> <<\/span>255 AND<\/span> i<\/span>.<\/span>number<\/span><=<\/span>len<\/span>(<\/span>MyCreditCards<\/span>.<\/span>number<\/span>)<\/span><\/span><\/p>\n

 <\/span>ORDER<\/span> BY<\/span> i<\/span>.<\/span>number<\/span><\/span><\/p>\n

 <\/span>FOR<\/span> XML<\/span> PATH<\/span>(<\/span>''<\/span>),<\/span> TYPE<\/span>).<\/span>value<\/span>(<\/span>'.'<\/span>,<\/span> 'varchar(max)'<\/span>)<\/span> TRANSLATION<\/span><\/span><\/p>\n

FROM<\/span>  <\/span>MyCreditCards<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/* neat, but it doesn't work. The algorithm can detect that you are working on MyCreditCards which is supposed to be masked. It manages to put a mask on the result *\/<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\"FirstFailure.bmp\"<\/a><\/p>\n

\/* We need to try harder. Basically, this works*\/<\/span><\/p>\n

 <\/span><\/span><\/p>\n

SELECT<\/span> CreditCard_ID<\/span>,<\/span> char<\/span>(<\/span>numbers<\/span>.<\/span>number<\/span>)<\/span> FROM<\/span><\/span><\/p>\n

numbers<\/span> INNER<\/span> JOIN<\/span> MyCreditCards<\/span><\/span><\/p>\n

ON<\/span> numbers<\/span>.<\/span>number<\/span>=<\/span>ascii<\/span>(<\/span>substring<\/span>(<\/span>MyCreditCards<\/span>.<\/span>number<\/span>,<\/span>1,<\/span>1))<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\/* you can see the first digit in the credit card number! From then on in, it is routine. Don't worry that this looks clunky. It is here just to demonstrate that you can see the string *\/<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

DECLARE<\/span> @TheCreditCardCharacters<\/span> TABLE<\/span>(<\/span>Creditcard_ID<\/span> INT<\/span>,<\/span>TheIndex<\/span> INT<\/span>,<\/span>TheCharacter<\/span> CHAR<\/span>(<\/span>1))<\/span><\/span><\/p>\n

INSERT<\/span> INTO<\/span> @TheCreditCardCharacters<\/span> <\/span> (<\/span>Creditcard_ID<\/span>,<\/span>TheIndex<\/span>,<\/span>TheCharacter<\/span>)<\/span><\/span><\/p>\n

  <\/span>SELECT<\/span> creditcard_id<\/span>,<\/span> TheLength<\/span>.<\/span>number<\/span>,<\/span> char<\/span>(<\/span>numbers<\/span>.<\/span>number<\/span>)<\/span><\/span><\/p>\n

FROM<\/span> MyCreditCards<\/span><\/span><\/p>\n

CROSS<\/span> JOIN<\/span> numbers<\/span> Thelength<\/span><\/span><\/p>\n

INNER<\/span> JOIN<\/span> numbers<\/span><\/span><\/p>\n

ON<\/span> numbers<\/span>.<\/span>number<\/span>=<\/span>ascii<\/span>(<\/span>substring<\/span> <\/span>(<\/span>MyCreditCards<\/span>.<\/span>number<\/span>,<\/span>TheLength<\/span>.<\/span>number<\/span>,<\/span>1));<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

SELECT<\/span> creditcard_ID<\/span>,<\/span><\/span><\/p>\n

(<\/span>SELECT<\/span> TheCharacter<\/span><\/span><\/p>\n

 <\/span>FROM<\/span> @TheCreditCardCharacters<\/span> EachCharacter<\/span><\/span><\/p>\n

 <\/span>WHERE<\/span> AllCreditCards<\/span>.<\/span>creditcard_id<\/span>=<\/span>EachCharacter<\/span>.<\/span>CreditCard_ID<\/span><\/span><\/p>\n

 <\/span>ORDER<\/span> BY<\/span> EachCharacter<\/span>.<\/span>TheIndex<\/span><\/span><\/p>\n

 <\/span>FOR<\/span> XML<\/span> PATH<\/span>(<\/span>''<\/span>),<\/span> TYPE<\/span>).<\/span>value<\/span>(<\/span>'.'<\/span>,<\/span> 'varchar(max)'<\/span>)<\/span> TRANSLATION<\/span><\/span><\/p>\n

FROM<\/span> @TheCreditCardCharacters<\/span> AllCreditCards<\/span><\/span><\/p>\n

GROUP<\/span> BY<\/span> creditcard_id<\/span>;<\/span><\/span><\/p>\n

go<\/span><\/p>\n

 <\/span><\/span><\/p>\n

\"Translated.bmp\"<\/a><\/p>\n

\/* Pretty soon, I guess, they'll plug this leak. The algorithm is cunning but because I disguised the relationship with the<\/span><\/p>\n

table a little bit, it was fooled. Drop your guard a bit and it will put the mask back in. If you were to tidy things up a bit with a CTE (don't because it performs badly), the mask would be reimposed *\/<\/span><\/p>\n

;<\/span>WITH<\/span> TheCreditCardDetails<\/span> AS<\/span><\/span><\/p>\n

       <\/span> <\/span> (<\/span><\/p>\n

  <\/span>SELECT<\/span> creditcard_id<\/span>,<\/span> TheLength<\/span>.<\/span>number<\/span> AS<\/span> TheIndex<\/span>,<\/span> char<\/span>(<\/span>numbers<\/span>.<\/span>number<\/span>)<\/span> AS<\/span> TheCharacter<\/span><\/span><\/p>\n

FROM<\/span> MyCreditCards<\/span><\/span><\/p>\n

CROSS<\/span> JOIN<\/span> numbers<\/span> Thelength<\/span><\/span><\/p>\n

INNER<\/span> JOIN<\/span> numbers<\/span><\/span><\/p>\n

ON<\/span> numbers<\/span>.<\/span>number<\/span>=<\/span>ascii<\/span>(<\/span>substring<\/span> <\/span>(<\/span>MyCreditCards<\/span>.<\/span>number<\/span>,<\/span>TheLength<\/span>.<\/span>number<\/span>,<\/span>1)))<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

SELECT<\/span> creditcard_ID<\/span>,<\/span><\/span><\/p>\n

(<\/span>SELECT<\/span> TheCharacter<\/span><\/span><\/p>\n

 <\/span>FROM<\/span> TheCreditCardDetails<\/span> EachCharacter<\/span><\/span><\/p>\n

 <\/span>WHERE<\/span> AllCreditCards<\/span>.<\/span>creditcard_id<\/span>=<\/span>EachCharacter<\/span>.<\/span>CreditCard_ID<\/span><\/span><\/p>\n

 <\/span>ORDER<\/span> BY<\/span> EachCharacter<\/span>.<\/span>TheIndex<\/span><\/span><\/p>\n

 <\/span>FOR<\/span> XML<\/span> PATH<\/span>(<\/span>''<\/span>),<\/span> TYPE<\/span>).<\/span>value<\/span>(<\/span>'.'<\/span>,<\/span> 'varchar(max)'<\/span>)<\/span> TRANSLATION<\/span><\/span><\/p>\n

FROM<\/span> TheCreditCardDetails<\/span> AllCreditCards<\/span><\/span><\/p>\n

GROUP<\/span> BY<\/span> creditcard_id<\/span><\/span><\/p>\n

ORDER<\/span> BY<\/span> creditcard_id<\/span>;<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

\"SecondFailure.bmp\"<\/a><\/p>\n

\/* Yep, the mask is back even though the CTE returns the info. *\/<\/span><\/p>\n

\/* Beware that I'm not giving high-performance solutions, just showing you that Dynamic Data Masking is great for application user-acceptance testing, and a useful addition to SQL Server for a niche purpose, but you must not assume that it provides any security at the database level.  <\/span>If you're using DDM, and have SQL Compare, see Steve Jones' article on Using SQL Compare with Dynamic Data Masking." *\/<\/span><\/p>\n

\/* Many thanks to Aaron Bertrand for getting me interested in this topic in the course of his entertaining presentation at SQLBits, and especial thanks to Gianluca Sartori (\u00e2@spaghettidba) for his help<\/span><\/p>\n

.*\/<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

\/* Revised 27th June   Dynamic data masking is a great product and solves some niche problems that come if you need to do certain testing with live data at the application level. You should, however, beware of using it as a database-level security device. I haven't yet used it in testing because I don't……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-26278","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26278","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\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=26278"}],"version-history":[{"count":19,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26278\/revisions"}],"predecessor-version":[{"id":68418,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26278\/revisions\/68418"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=26278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=26278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=26278"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=26278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}