September 4, 2018 at 8:23 am
I will start with some background info.
We are using SQL 2012 and wont be able to upgrade to 2016 soon. We are a healthcare organization and I am part of the team working on data security now. I have not worked on data security in my career of 5 years but I have been reading and trying to learn as much as I can.
We have started with classifying data as sensitive and less-sensitive and privacy team is all in for this task. Now, the time has come to decide what needs encryption and masking in various environments.
SENSITIVE fields: Health card number,First Name, Last name. Close to 25 fields termed as sensitive.
LESS SENSITIVE fields : Phone number, Street number. Close to 50 termed as less-sensitive.
And rest of them are not sensitive as all obv.
from my understanding,encryption for the database fields in production for sensitive fields.But, what to do for less-sensitive fields? Encryption again? There are so many fields which are listed as non-sensitive. Would that hamper performance? Will be using method listed in msdn for encryption. Link to article
If everything is encrypted, do I still need masking in test and uat?
On the other hand, we can do masking for less-sensitive fields in DEV and UAT environment. And encrypted fields which are sensitive from production copied to DEV and UAT.
We are planning to write custom functions or buy a tool from redgate(data masker) to generate fictitious data.Which would be better and why?
Also, if we encrypt all sensitive and less-sensitive fields in production, would the refresh to dev and UAT will be encrypted fields as well?
We have users accessing data via Management studio, SSRS and cube.
September 4, 2018 at 12:20 pm
86deep - Tuesday, September 4, 2018 8:23 AMI will start with some background info.
We are using SQL 2012 and wont be able to upgrade to 2016 soon. We are a healthcare organization and I am part of the team working on data security now. I have not worked on data security in my career of 5 years but I have been reading and trying to learn as much as I can.
We have started with classifying data as sensitive and less-sensitive and privacy team is all in for this task. Now, the time has come to decide what needs encryption and masking in various environments.
SENSITIVE fields: Health card number,First Name, Last name. Close to 25 fields termed as sensitive.
LESS SENSITIVE fields : Phone number, Street number. Close to 50 termed as less-sensitive.
And rest of them are not sensitive as all obv.from my understanding,encryption for the database fields in production for sensitive fields.But, what to do for less-sensitive fields? Encryption again? There are so many fields which are listed as non-sensitive. Would that hamper performance? Will be using method listed in msdn for encryption. Link to article
If everything is encrypted, do I still need masking in test and uat?On the other hand, we can do masking for less-sensitive fields in DEV and UAT environment. And encrypted fields which are sensitive from production copied to DEV and UAT.
We are planning to write custom functions or buy a tool from redgate(data masker) to generate fictitious data.Which would be better and why?Also, if we encrypt all sensitive and less-sensitive fields in production, would the refresh to dev and UAT will be encrypted fields as well?
We have users accessing data via Management studio, SSRS and cube.
Depending on how you do your encryption in production, you may need to import the encryption certificates down to non-prod environments in order to decrypt the data. You should also consider sanitizing non-production data after the restore by randomizing information or wiping it and replacing it completely.
For instance, in our non-prod environments, all emails are changed to a test email account so users don't accidentally get emails from our testing systems during QA, but also we insert random tax ID information in the tax fields, change the street addresses to either the company address or nonsensical addresses. For demo data, I like using sci-fi shows and Disneyworld references for customer names, addresses and city / states.
You'll probably need a combination of both methods. The last thing you want is sensitive production data down in non-prod that is readable by the people testing or developing your apps. So once you get the restore completed, sanitize it thoroughly. Make it a SQL Server job with a job step or 3 devoted solely to this.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply