Maxer (5/18/2014)
A company I work with stores all HR data for all past and present employees in a table that has SSN, DOB, address, and full name.This sort of sets my "Spidey Sense" tingling all that personal data in one table where all of the IT dev team can get full access.
I was wondering about some better options?
1. Limit access (for development I was thinking swap out real SSN with fakes on dev and UAT)
2. Encryption options? Column level inside SQL 2008 R2? Other options? This is a 3rd party ERD so I can't change the values in the column itself without some sort of abstraction voodoo happening?
3. Any other thoughts?
Yes...
First, thank you for the concern. I see too many people address the subject of SSNs and other senstivie information if far too cavailier a manner to be right.
In regards to the 3rd party ERD software... If it were me, I'd give them the opportunity to make the necessary changes while looking for some other software that already gives SSNs the respect they deserve. If the original company fails to comply, report them to the Social Security Administration, the SEC, and the correct agencies for PCI and then drop them like a hot potato.
As for having such information in Dev or Staging, you simply must not or it must be encrypted in such a fashion that no unauthorized person can "see" the original. The latter of those two is a tall order to comply with.
--Jeff Moden
Change is inevitable... Change for the better is not.