Reversing Dynamic Data Masking

Joe Obbish shows how easy it is to reverse Dynamic Data Masking:

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 CROSS APPLY for each digit in the SSN. Each CROSS APPLY only references the SSN column in the WHERE clause and returns the matching prefix of the SSN that we’ve found so far. Here’s a snippet of the code:

Click through for progressively faster solutions.  This is the main reason I do not care for DDM as a feature.  Its main benefit seems to be preventing shoulder-surfing on reports; any concerted attacker with a little bit of access to writing queries can subvert it.

Related Posts

Permissions Needed To View Permissions

Kenneth Fisher shows us what we can do to grant a low-privilege user the ability to view permissions for other users: Every now and again you’ll have a user that needs to be able to see what permissions other users have. Not change them, just look at them. In the cases I’ve seen it’s usually […]

Read More

Limiting Azure Administrator Data Access

Melissa Coates gives us a look at one aspect of Azure security: Recently a customer expressed concern that an owner of an Azure resource group automatically gains access to the data within the services contained in the resource group. In this case, the customer was specifically referring to data in Azure Data Lake Storage Gen […]

Read More

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031