List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:April 16 2014 1:15pm
Subject:Re: Data masking for mysql
View as plain text  
Hello Reena,

On 4/16/2014 2:57 AM, reena.kamboj@stripped wrote:
> Client never give production db with sensitive data to oursourced dev team. But
> outsourced testing team need a clone of production db for testing. For that client can
> give a copy of production db with masked sensitive data.
> That's why data masking tool required.
>
> -----Original Message-----
> From: "Jigal van Hemert" <jigal@stripped>
> Sent: Wednesday, 16 April, 2014 11:56am
> To: mysql@stripped
> Subject: Re: Data masking for mysql
>
> Hi,
>
> On 15-4-2014 18:42, Peter Brawley wrote:
>> On 2014-04-15 5:37 AM, reena.kamboj@stripped wrote:
>>> It can be done by data masking tool itself. Its one time activity, I
>>> do not need it again & again.
>>
>> Rilly? If that's so, the data will never be accessed.
>
> I'm starting to think that a concept has been made that includes a
> database with the original data, a copy with the "masked" data and then
> there just needs to be a tool that copies the data and modifies
> ("masks") some fields. Whatever solution we come up with (views, db copy
> with an update query that modifies the data, ...) it will not be
> accepted unless it fits the original concept.
> Most likely the client came up with the concept and then this outsourced
> development team doesn't dare to suggest that a different concept is
> probably a better way to reach the goal. But, I may be wrong here :-)
>

The only tool you need is a well-formed query or set of queries and 
perhaps a function or two (either one of the built-in functions or one 
you construct yourself)

Instead of executing the direct query

     "SELECT field1, sensitiveField, field3 FROM ..."

You run some manipulation on the `sensitiveField` field like this

     "SELECT field1, CONCAT('xxxxxxxx',right(sensitiveField,4)), field3 
FROM ... "

http://dev.mysql.com/doc/refman/5.6/en/string-functions.html


If you don't like that particular transformation, how about a hashing or 
encryption function?
http://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html

The choice of how to obscure their data is up to the provider of the 
data. They choose which fields they want to hide and how they want to 
hide them. Then, they write very simple queries to create the set of 
data they want you to see.  These queries can be used to build views or 
can be used to send the data into a copy of the table or can be used to 
output the data directly to file. These are simple choices.

There is no 'tool' to do this for you. It's a very basic set of queries 
that any competent DBA should be able to provide. If neither your 
programmers and DBAs nor their programmers and DBAs can write this type 
query, you should all seriously question your abilities to be working 
with data and look into improving your skills immediately.

Regards,
-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Data masking for mysql reena.kamboj15 Apr 2014
  • Re: Data masking for mysqlReindl Harald15 Apr 2014
    • Re: Data masking for mysqlreena.kamboj15 Apr 2014
      • Re: Data masking for mysqlJigal van Hemert15 Apr 2014
        • Re: Data masking for mysqlreena.kamboj15 Apr 2014
          • Re: Data masking for mysqlJigal van Hemert15 Apr 2014
  • Re: Data masking for mysqlManuel Arostegui15 Apr 2014
    • Re: Data masking for mysqlreena.kamboj15 Apr 2014
      • Re: Data masking for mysqlReindl Harald15 Apr 2014
        • Re: Data masking for mysqlreena.kamboj15 Apr 2014
          • Re: Data masking for mysqlReindl Harald15 Apr 2014
          • Re: Data masking for mysqlPeter Brawley15 Apr 2014
            • Re: Data masking for mysqlreena.kamboj16 Apr 2014
            • Re: Data masking for mysqlJigal van Hemert16 Apr 2014
              • Re: Data masking for mysqlreena.kamboj16 Apr 2014
                • Re: Data masking for mysqlshawn l.green16 Apr 2014
  • Re: Data masking for mysqlmisiaq18 Apr 2014