After working with PHI for many years now, I can appreciate the importance of data masking. While normally the first approach is to encrypt the data it is often too much security for the purpose of preventing unauthorized access. Often I have had to sign very complicated NDAs just to ensure that I don’t access unauthorized data even by mistake. Hopefully all that will change now that we have Dynamic data masking.
So how is dynamic data masking (DDM) different from encryption? Well the simple answer is in every way. Encryption uses a salt and encryption key to jumble the data both at the page level and in motion. Encryption uses additional resources in order to achieve this and therefore brings with it performance implications too. We can avoid these challenges by using dynamic data mask because it applies the mask once the data has been fetched from disk therefore there is no overhead at the disk and CPU level. In addition unlike encryption where we need all the values to be unique after encryption in DDM the concept if to hide the data under a common default value therefore preventing people from deriving meaning from the data.
So how do you enabled DDM?
You don't it's enabled by default, but you can disable it using the trace flag
DBCC TRACEOFF (209, 219,−1)
Since the masking happens at the columns level you need to keep in mind the data types being used. There are two ways you can mask the data, using the default values or choosing a custom format of your own.
Create TABLE [dbo].[DDM](
[id] [int] IDENTITY(1,1) masked with ( function='default()') NOT NULL,
[Name] [varchar](20) masked with ( function='default()'),
[Age] [int] masked with ( function='default()'),
[DOB] [date] masked with ( function='default()'),
[Createtime] [datetime2](7) masked with ( function='default()'),
[Email] [varchar](50) masked with ( function='email()'),
[CustomPhNum1] [varchar](20) masked with ( function='partial(1,"XX-XX-XXX",3)'),
[CustomPhNum2] [varchar](20) NULL
) ON [PRIMARY]
The above example shows how to create a table with a few columns that are masked more details on the feature can be found in the link under references. You will need to save the table create script since the generate scripts or other ways in SSMS will not script out these DDM syntax.
Also if you really want to hack the data you can simple put the output in a temp table and read from there even if the datatypes are the same temptables could be used anywhere in the code and as a result the data is not masked. Might be worthwhile to consider masking of the last select comes from a temptable.
Identity columns cannot be masked so if used for things like account number, order number etc then be careful.
References
https://msdn.microsoft.com/en-us/library/mt130841.aspx
Please Consider Subscribing