question

nidheesh.r.pillai avatar image
nidheesh.r.pillai asked

How to mask a column in a table?

There is a column in one of our table that stores SSN of the client-end-users.I suggessted using the DENY DCL-feature, such that permissions to that column itself is resticted to groups of people that have access to the data, like db developers. However, we are asked to prepare an update script instead of going to DCL permission levels. Is there any other technique on how one can mask one particular column of a table in a database? Will using hashing algorithms like HASHBYTES, CHECKSUM or BINARY_CHECKSUM help? If yes, how?
sql-server-2008maskingchecksumhashbytesbinary-checksum
1 comment
10 |1200 characters needed characters left characters exceeded

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

Re-opened the question to match other answered questions.
0 Likes 0 ·
KenJ avatar image
KenJ answered
Hashing is a one-way operation. If you don't need to retrieve the SSN values again, hashing is fine and HASHBYTES will do the trick (I hope you won't be storing both the SSN and the hash). Remember, you must add salt before hashing or the SSN values aren't really protected (search online for "rainbow tables"). Brief intro to the drawbacks of hashing SSN here - [ http://techatftc.wordpress.com/2012/04/22/does-hashing-make-data-anonymous/][1] If you need to "mask" the SSN values but still get them back, you'll need to look into encryption like the MSDN article mentioned by @GPO ([ http://msdn.microsoft.com/en-au/library/ms179331.aspx][2]) - it's as easy to use as a hash and you can still get the original values back. [1]: http://techatftc.wordpress.com/2012/04/22/does-hashing-make-data-anonymous/ [2]: http://msdn.microsoft.com/en-au/library/ms179331.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

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

I had a talk with my folks here and they are okay with the approach since we are trying to implement this is non-production environments. For production, the db developers do not have the access anyway. They are also fine in not retrieving the original values back, as this data is more or less used mostly for information purposes. And Yes, you are true. I wont be storing both hashcode and SSN. Thank you for the links too, which explain the side-effects. Thanks all.
0 Likes 0 ·
GPO avatar image
GPO answered
Have a look at this example from MSDN (although as an aside, I don't much agree with their idea of storing a credit card number at all) http://msdn.microsoft.com/en-au/library/ms179331.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

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

Hi - Thank you for the suggestion. However I am not particularly looking at this solution (of creating encryption keys) at the moment. I may look into it in case my second question "Will using hashing algorithms like HASHBYTES, CHECKSUM or BINARY_CHECKSUM help and If yes, how?" turns out to be fizzy. Nevertheless, Thankyou once again for the tip.
0 Likes 0 ·