Presentation Versus Storage

Edwin Sarmiento looks at how data is stored on disk when you use Dynamic Data Masking or Always Encrypted in SQL Server 2016:

Looking at the data, the masked columns appear as they are on disk. This validates Ronit Reger’s statement on his blog post Use Dynamic Data Masking to obfuscate your sensitive data.

* There are no physical changes to the data in the database itself; the data remains intact and is fully available to authorized users or applications.* Note that Dynamic Data Masking is not a replacement for access control mechanisms, and is not a method for physical data encryption.

In contrast, the encrypted columns are encrypted on disk and the data types are different on disk compared to how they were defined in the table schema – SSN is defined with nvarchar(11) while CreditCardNumber is defined with nvarchar(25). This means that those “valuables” are even more secured on disk, requiring additional layers of security just to get access to them.

Read the whole thing.

Related Posts

Miminal Rights For Bulk Inserts

Timothy Smith takes us through least privilege while allowing bulk insert operations: While this file path serves as a useful location for us to load flat files, we should consider that the user account that is executing the underlying insert statement must be able to read (and possibly write to) that file location. The writing […]

Read More

Auditing SQL Agent Jobs

Jason Brimhall has some clever techniques for auditing SQL Agent Jobs with Extended Events: Once upon a time, I was in the position of trying to figure out why a job failed. After a bunch of digging and troubleshooting, it was discovered that the job had changed but nobody knew when or why. Because of […]

Read More

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930