The development team is receiving production data (as Database backup files) in order to fix bugs and application enhancements. The Development team restore these backups in their environment and perform the application development work/bug fixing etc..


Client is concerned about exposing sensitive production data such as PII(Personal Identifiable Information) to the development team. Client needs to screen these sensitive data while preserving its properties where the dev team can perform day to day work without interruption.

There is no requirement of reversing the screened data.

Probable Solution 1 : Data Masking

As I see the most correct solution for this problem is identify the PII database fields and perform Data masking. One problem we are facing is we need to preserve data properties in the DB such as follows.

  • The masked data length cannot go beyond the particular field length.
  • The masked data should be in the same data type as original data : Eg if the DB field type is int the masked data also should be an int
  • If the masked result of value ABC is XYZ, all instances of data ABC should masked to XYZ (This is because some PIIs are using as DB keys)


  • Since we do not need to reverse masked data, can we use hashing to accomplish this? If so , how to maintain the data properties I described above ?

  • If we are going to use encryption, can we preserve data properties ? What are the best algorithms we do have?

  • Are there any other techniques do we have to accomplish this objective ?

  • Can we prevent data Data inference using data masking ?

  • Note that you may also see performance changes; the indexes built using the modified data will not splay out into the same tree as the real data, and depending on the size of the tables, the performance implications can be noticeable. – gowenfawr Apr 19 '17 at 13:06
  • @ gowenfawr: Thanks for your feedback. Performance impact is not a big concern here because these masked data only using in the development environment. – user3496510 Apr 19 '17 at 13:48
  • can I trade your developers for my developers? :) :) :) – gowenfawr Apr 19 '17 at 14:22
  • "If the masked result of value ABC is XYZ, all instances of data ABC should masked to XYZ (This is because some PIIs are using as DB keys)" This hurts a lot : any solution will end up being much more complex. PII should not be spread everywhere in the DB. – ForguesR Apr 19 '17 at 14:24
  • @Forgus: Thanks for the comment. This is how it was designed and we need to live with it :( – user3496510 Apr 19 '17 at 16:48

Data masking is a common request and various vendors have their own solution or rely on 3rd party solutions. You could implement your own (for example using hashing) but as you pointed out, it would be difficult to maintain data integrity, constraints and formats. For example, you might have a national identity column which is validated using modulo X (or some other verification mechanism). How would you mask such columns but preserving the validation?

Using tools already available will solve this for you. Just as an example of features such tools offer (and I'm not implying you should use this specific tool), take a look at Oracle own solution for data masking and subsetting. Quote from Data Sheet describing some of the data masking formats supported:

  • Encryption encrypts the sensitive data using a key while preserving the format of the data. This transformation is useful when masked data sent to a third party has to be merged back along with further updates.
  • Format Preserving Randomization (or auto mask format) randomizes the data, preserving the input length, position, the case of the character (upper or lower), and special characters in the input.
  • Conditional Masking masks columns according to different conditions. For example, identifiers that belong to the United States can be masked using Social Security Number format and those that belong to the United Kingdom can be masked using National Insurance Number format.
  • Compound Masking groups and masks related columns together. For example, if you want to shuffle address fields like city, state, and country, then grouping city and the state will keep these columns together during the shuffling process.
  • Deterministic Masking generates consistent masked output for a given input across application schemas and databases. This makes it possible to mask names consistently or deterministically across different modules across your organization.

I believe these formats fit into your requirements.

Regardless of your RDBMS architecture, the point is the request is common, there is a market for it and on this market there are various vendors who provide the solution. You should choose one of them rather then reinventing your own.

Not the answer you're looking for? Browse other questions tagged or ask your own question.