August 3, 2018 at 12:26 pm
I'm working on a project where the source data (out of my control) uses social security numbers to uniquely identify people. I don't even want to import SSN's into my database (in my control), let alone include them on reports (as is currently happening). But I need some way to be able to correlate an SSN in the source data with a [whatever value I use] in my own data.
So, I could maybe do something like hash the SSN's during the ETL step and store the encoded values in my database. But then I wind up with a huge 64-character or 128-character string. That's fine behind the scenes, but I also need some reasonable way to display that value on reports. It would be nice if I could have some kind of YouTube-like format, 10 digits or so of human-readable characters.
Any thoughts on how to do a one-way hash of a SSN (fixed 9 digit length) that results in a short-ish value without totally compromising security?
Or is the only option to store the full 64/128-character hashed value and then map it to some other YouTube-like unique identifier that I'd have to create for each record using perhaps a homemade function or something? I'd prefer not to use an auto-incrementing number, because I know that common wisdom is not to display those to end-users ("why do I see #100 and #102 but not #101???"). SQL Server doesn't seem to have anything other than GUID (also too long) built in, so my function would have to create a random string of letters and numbers and make sure it has not already been used in the table. Seems annoying and kinda hoopty. 🙁
August 3, 2018 at 1:01 pm
You could use a SEQUENCE, or if that is not available, insert the SSN into table with an identity and use the identity value as a link back to the SSN.
Typically on reports you'd show AT MOST the last 4 digits of SSN, and even that's not a good idea.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 3, 2018 at 1:42 pm
Yes, my concern with an incrementing, numeric, identity-type column is that I know it's generally discouraged to make those client-facing. And what I specifically need here is a reasonable-size value that will be client-facing.
It would be great if there was a way to repeatably one-way convert a SSN into a 10-digit alphanumeric value. Any ideas along those lines?
August 3, 2018 at 2:01 pm
autoexcrement - Friday, August 3, 2018 1:41 PMYes, my concern with an incrementing, numeric, identity-type column is that I know it's generally discouraged to make those client-facing. And what I specifically need here is a reasonable-size value that will be client-facing.It would be great if there was a way to repeatably one-way convert a SSN into a 10-digit alphanumeric value. Any ideas along those lines?
It'd need to be unique for each ssn, which would allow it to be breakable. Don't see why the client can't see an identity in this specific case -- general rules are just that, general, known to deserve breaking at times.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 3, 2018 at 2:08 pm
I definitely appreciate your feedback. Maybe I am just overthinking it. Thanks!
August 3, 2018 at 7:09 pm
autoexcrement - Friday, August 3, 2018 2:08 PMI definitely appreciate your feedback. Maybe I am just overthinking it. Thanks!
You need to NOT try to build your own decoder ring on this one. Even using something like hashbytes with the SHA2_512 algorithm won't protect you if someone gets ahold of the data because there are only 10 billion possible SSNs and even just one CPU server can hack all those in just a day or two using rainbow tables, etc. Someone with a rack of GPUs can do it in sub-second times. You need to use properly salted encryption with a master encryption key and the whole 9 yards for this stuff and then, yes, you can create a surrogate integer value for the encrypted data.
And make damned sure you have the key(s) safely protected in a physical safe somewhere because if you lose the key, your data is toast and so are you.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2018 at 7:12 pm
p.s. And, as a fellow data professional, thank you VERY much for trying to protect people. Now all you have to do is turn in the side-stepping beach creatures that are not only transmitting SSNs in unsecured clear text but you'll also need to make sure that no one has stored the files you receive or backed them up in such a fashion that the "data at rest" isn't usable by someone trying to make you read about yourself and the company you work for in the morning news.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2018 at 8:23 pm
Thanks as always for weighing in Jeff. I've read up on salted hashed values before, so conceptually it makes perfect sense to me. But I've never actually implemented anything like that before, so I'm still kinda fuzzy on the wheres and hows of storing the "salt" or "master encryption key". Do you have a favorite methodology for that? Or a link to any articles that outline it in a way that you agree with?
August 3, 2018 at 11:01 pm
An option would be to use an encrypted column for the SSN and if you need 10 digits, then use a seeded bigint identity column as a key.
😎
August 4, 2018 at 9:36 am
autoexcrement - Friday, August 3, 2018 8:23 PMThanks as always for weighing in Jeff. I've read up on salted hashed values before, so conceptually it makes perfect sense to me. But I've never actually implemented anything like that before, so I'm still kinda fuzzy on the wheres and hows of storing the "salt" or "master encryption key". Do you have a favorite methodology for that? Or a link to any articles that outline it in a way that you agree with?
Ah... my apologies for not including a link. :blush: Eirikur provided good cover for the very same link I would have pointed you to. Thanks, Eirikur.:exclamationmark:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2018 at 9:57 am
Jeff Moden - Saturday, August 4, 2018 9:36 AMautoexcrement - Friday, August 3, 2018 8:23 PMThanks as always for weighing in Jeff. I've read up on salted hashed values before, so conceptually it makes perfect sense to me. But I've never actually implemented anything like that before, so I'm still kinda fuzzy on the wheres and hows of storing the "salt" or "master encryption key". Do you have a favorite methodology for that? Or a link to any articles that outline it in a way that you agree with?Ah... my apologies for not including a link. :blush: Eirikur provided good cover for the very same link I would have pointed you to. Thanks, Eirikur.:exclamationmark:
You are welcome Jeff, and you would have done the same 😉
😎
August 6, 2018 at 7:43 am
For storing of SSN, you might consider adding a check digit to the value, so that it does become 10 digits (or 11 or whatever). That way they are trying to hack into the wrong number of digits, which should slow down the process a bit at least.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2018 at 8:00 am
ScottPletcher - Monday, August 6, 2018 7:43 AMFor storing of SSN, you might consider adding a check digit to the value, so that it does become 10 digits (or 11 or whatever). That way they are trying to hack into the wrong number of digits, which should slow down the process a bit at least.
If it's stored that way in plain text, a hacker will eat that up in less than a second. The only right way to protect such things is with properly salted encryption.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2018 at 8:14 am
Jeff Moden - Monday, August 6, 2018 8:00 AMScottPletcher - Monday, August 6, 2018 7:43 AMFor storing of SSN, you might consider adding a check digit to the value, so that it does become 10 digits (or 11 or whatever). That way they are trying to hack into the wrong number of digits, which should slow down the process a bit at least.If it's stored that way in plain text, a hacker will eat that up in less than a second. The only right way to protect such things is with properly salted encryption.
Not talking about plain text, but encrypting a value with extra digit(s) added. Could even add random digits in fixed locations, just to make it harder for someone to know how many digits they were trying to hack.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2018 at 8:23 am
I still just don't fully understand how salts and the like are supposedly so helpful, because it seems like they have to be stored somewhere. Either in code or in an environment variable or somewhere. Why is there an assumption that that's safe when a database isn't?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply