T-SQL Encryption function sought - (1 Viewer)

[Isaac]

Hello there,

I'm coding in T-SQL in a sql server query, and the requestor wants me to de-identify that physicians' names in a way that will allow her to give each physician a copy of their report - and they will know which one they are, but won't be able to tell which one the others are. Another requirement is that the encrypted values come out the same each time - but I think this may be negotiable.

Is this even logically possible? Technically, if they have the key that says "anywhere you see XUS, it means JON", doesn't that give them some insight into the decryption and defeat the purpose?

Or is there some magical combination of logical truth and a simple encryption function I can use to make this work?

I'm close to telling her to just deID it in Excel using random mumbo jumbo (no pattern) for all 'other' physicians, and just give each copy to the different doctors.

 

[LarryE]

Why not just sort and start a new page for each doctor and distribute the report to each one. ACCESS can start a new page for each one. The other doctors never get anyone elses report.

 

[Isaac]

They want to see where they are compared to the other ones, but jsut without names.

This is all sql server, no Access involved. Just outputting data to excel and she is graphing it from there

 

[Isaac]

I suppose I can just make up an alias for each doctor's name. Each one knows their own and not the other. I should have known the problem was simpler than I was giving it credit for.

 

[tvanstiphout]

I suppose I can just make up an alias for each doctor's name. Each one knows their own and not the other. I should have known the problem was simpler than I was giving it credit for.

Yeah, either that, or totally overthinking it and calculating a hash over each physician's account info.

 

[AHeyne]

But then with a salt that none of the participants knows, otherwise they could try to create and compare hashes themselves using the names involved...

 

[tvanstiphout]

But then with a salt that none of the participants knows, otherwise they could try to create and compare hashes themselves using the names involved...

So true. I was tempted to suggest that in first instance We need to do everything possible to prevent a successful attack using rainbow tables.