0

I have this requirement: we are going to restore production database into UAT for testing few use cases. We should anonymise required data (Ex: SSN, Credit Card etc) from all types of users (including sysadmin, db owner). No one should be able to get real records.

Also application/UI layer not going to implement any changes (to decrypt or unmask) because of time line.

I have tried below option but not able to achieve my use case

  1. Dynamic Masking - Admin Can able to view
  2. Always Encrypted - It encrypts records (not mask)
  3. Static Mask - This override actual data and will miss real record

We have to anonymise the data from all users with out missing actual records. What is the best option to achieve this?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
PrabhuR
  • 1
  • *"with out missing actual records"* what does that mean? – Charlieface Jun 16 '22 at 20:34
  • Not the "best option", but I once obfuscated a database by doing text substitutions that mapped letters "A"-"Z" to "ABCABCABC..." and digits 1-9 to "123123123". Zeros, spacing, and punctuation were left as-is. It left enough variety to allow searches performance testing, but was generally not recognizable as real data. The drawback is that this not truly anonymous. For example: Given an known SSN, someone could still figure out if that person had a record in the PsychiatricTreatment table, so probably not good enough for PHI or other highly sensitive information.. – T N Jun 17 '22 at 00:38
  • Better would be something that dynamically generates a one-time dictionary that maps words and numbers to randomly generated substitutions. Repeated values would map to the same target value, so data distribution and joins would be similar to the original database. The dictionary would be discarded after use, so reverse engineering would be extremely difficult (but still not impossible with extensive analysis). – T N Jun 17 '22 at 00:45
  • A google search of "database anonymizer" seems to yield several tools than may serve your needs. – T N Jun 17 '22 at 00:48

0 Answers0