SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Suggsted approach to masking


Suggsted approach to masking

Author
Message
T4FF
T4FF
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 184
We have a reporting system that I present multiple databases to (i.e. for multiple clients). Our UAT & DEV source systems don't provide enough business-like data for me to provide real-life situations for testing. Resource constraints also mean I'm not going to get situations keyed in. This has resulted in users developing & testing their reports on the production reporting system.

So I'm looking to present a copy of the production database (or even the production database itself) to our development servers, but in doing so I need to satisfy the compliance requirement that sensitive data is not available. Preferably I would like something that is self-maintaining i.e. that is self-aware of the user, or the environment it is on and either makes the data available or doesn't.

So what approach would you take? Would it be a script to mask the data post-restore? Or a table that is security-controlled with an overlayed view / proc that either masks the sensitive data on execute / query or decrypts the encrypted data on execute / query? Or would you use a 3rd party tool to generate duff data? Some other route?

My issue with something like Data Generator (I have the SQL Toolbelt) is that it is restricted, it's either a whole table or nothing which isn't what I want since that will result in inconsistent data. Additionally, it isn't set up to be all that automated - you need to build a project then run it from the command line, it would be amazing to be able to do something along the lines of "data generator" [table] [column] [data type]


Thanks for looking ;-)
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14811 Visits: 14396
So what approach would you take? Would it be a script to mask the data post-restore? Or a table that is security-controlled with an overlayed view / proc that either masks the sensitive data on execute / query or decrypts the encrypted data on execute / query? Or would you use a 3rd party tool to generate duff data? Some other route?

My first choice would be to restore the database while keeping users locked out, cleansing the data, then letting users in. Scripts to sanitize personally identifiable or sensitive data while maintaining the overall linkages between entities are tedious to write but are relatively easy, e.g. update this name to that name, scramble this number, rewrite that credit card number by pulling a random one from a set of check-digit-valid dummy card numbers, etc. are trivial and do not require intimate knowledge of every parent-child relationship.

Second choice would be to use a data generator but I fear that would become even more tedious depending on how good the toolset is.

My issue with something like Data Generator (I have the SQL Toolbelt) is that it is restricted, it's either a whole table or nothing which isn't what I want since that will result in inconsistent data. Additionally, it isn't set up to be all that automated - you need to build a project then run it from the command line, it would be amazing to be able to do something along the lines of "data generator" [table] [column] [data type]

Visual Studio has a data generator that may prove to be more flexible for you. I have used it to do unit testing but do not remember the details surrounding that specific nuance.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5170 Visits: 4076
we also do data masking with below steps,

1) updating credit card information (randomly generated alpha-numeric words)
2) emails : adding some irrelevant prefix to profiler name plus changing the domains.
3) upodating other payment related columnns ( full fledge columns updates)\
4) chagin the contact details too like mobile nos.

i must say all the above steps are time- consuming (week - long - running jobs at)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search