mercredi 23 septembre 2015

SQL update values randomly to wipe data / make it unreadable

Here is the situation, I have a table with some data that are used in Production. When i do a backup that is available for every developer, i want to change the current sensitive data to random values so that :

  • i don't wipe it completely so i still have data
  • the data are still usable (not the same value for the complete column, respect of Foreign keys, etc...)

For the moment i imagined something like this for the nvarchar fields :

update Client set Name = SUBSTRING (CONVERT(VARCHAR(32), HashBytes('MD5', Name), 2),10,30) 

i saw some posts like this :

  1. SQl Update from table of random Names
  2. SQL Update row column with random lookup value

but it seems to use big queries that i'd prefer to avoid. Isn't there a better approach to randomize a complete table without scripting specific script for each table/column ? (btw i'm using SQL Server 2014) Thanks in advance !




Aucun commentaire:

Enregistrer un commentaire