question

PerWidell avatar image
PerWidell asked

Change all GUIDs in a database

I need a smart script to change all GUIDS in a database, both the constraints and foreign keys. The reason is for GDPR which needs unidentified persondata and no one should be able to reverse personal information matched to a productioncopy. If I just change name and personnumber then a DBA can still restore a productioncopy and join between the databsaes on the GUID and get the real person. It is many tables with millions of rows that needs to be converted. Anyone got a good example? I tried to create a cursor that put all GUID columns in all tables into a temporary table, then loop this and select all distinct guids and make newID() and update everywhere. But oit was not a quick operation. Is it even possible to do this? Best Regards and a happy new year! Pelle
updateguid
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
@PerWidell - The below is not an answer to your question, but a suggestion. On first glance, I think changing GUIDs may not be a good idea. While solving bugs, the analysis usually boils down to an identifier, which will be a GUID in your case. Therefore changing GUIDs can make debugging difficult. Now, your question will have solutions no doubt, but they will be complex. To mask the person-data, what you do is perfect. But then the problem here is production access. Here, you can chose to run the change name and person data script every time the production copy is restored. You can also monitor production access by not giving direct access to anyone, but by a more controlled access methodology available; example - the developer will raise a production read request stating the need and the DBA will then approve it if the need seems fine.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your suggestion, but as I wrote the solution is still reversible if you can match the guid between productiondata and masked data. So se need a way to unmask all guids as well. We are aware of the risks but debugging use a special person-data (socialsecuritynumber) cause the guids are only used for all datarelations. Cause the relations it is a risk to replace the guid.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.