December 27, 2008 at 4:31 am
J (12/23/2008)
This is interesting. Though I am a bit surprised a law would cover such a specific technical detail (primary key).The OP has not confirmed if this is indeed the case, but if it is indeed covered by law in "some states" eventually, the system might spill over in one of such states. (Mergers and Acquisitions, anyone? who knows?).
As far as i am converned, since there are several ways of generating unique primary keys, there is no valid reason to use SSN's for this purpose. Its sole use should be for communicating with the government (income tax statemnts, etc.). And it should reside in ONE table, not scattered throughout the database as a foreign key.
Copnsidering all the recent thefts of commercial and credit records, the SSN should be in ONE column in ONE table and if anything should be stored encrypted, this would be it.
The major hassles to victims of identity-theft far outweight the small convenience of having a ready-made primary key instead of using GUID's or identity columns. A very poor design.
It's illegal to use SSN as identification in the real world. You could use it as an identifier in a database, not that I would. However, it almost certainly would be defined as a candidate key and as such would be made into a unique constraint.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 27, 2008 at 11:22 am
Heh... let's put it in terms of common sense... whether it's legal or not, would YOU want you SSN in clear text along with other personal and financial information on some rubber's computer somewhere with no guarantee that information is safe? And is your company willing to suffer through the lawsuits and fines for the accidental release of private information?
"Enquireing mind want to know"
--Jeff Moden
December 29, 2008 at 5:30 am
Jeff Moden (12/27/2008)
Heh... let's put it in terms of common sense... whether it's legal or not, would YOU want you SSN in clear text along with other personal and financial information on some rubber's computer somewhere with no guarantee that information is safe? And is your company willing to suffer through the lawsuits and fines for the accidental release of private information?"Enquireing mind want to know"
Of course not, which is why I said wouldn't use it. It's a poor choice as a PK for a lot of reasons, not the least of which is the security aspect (strangest device in the world, the SSN, it's not secure, almost anyone can track yours down, you can't use it as identification, but it somehow identifies you to the world... weird).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 29, 2008 at 6:36 am
Dang... sorry Grant... wasn't directed at you. It was directed at folks that somehow think it's OK in any way, shape, or form, to have unecrypted SSN's and the like stored in a database.
--Jeff Moden
December 29, 2008 at 6:43 am
Nah, don't sweat it. I thought it was a strange reply, but I didn't get upset.
I'm still confused by the concept of a thing that is absolutely used for identification but can't be used for identification that is hidden by everyone, but completely available to everyone... It's just strange.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 29, 2008 at 8:58 am
The SSN was designed for the convenience of the government. Period.
In Canada, the Social Insurance Number (equivalent to the SSN in the US) was used to cross-link information on people through multiple databases. The bureaucracy went as far as to compile a "Longitudinal" record linking all income tax statements, employment records, etc. The furor caused when it was leaked forced the government to back down and order the bureaucracy to dismantle this system.
I got a copy of the information the government kept on myself. The bureaucracy thought my first employment record in 1970 was still relevant. Once in a government database, always in a government database.
When returning from another country, Canadian Customs requires that a card be filled in detailing the duration, destination, purpose of your trip. The bureaucracy used this to cross-link travel records with unemployment records to retroactively clawback the unemployment benefits of unemployed people who spent a week or two in Florida on vacation (then no longer available for employment they cannot find anyway). The proper way for these people was to ask the government to suspend the benefits and then beg for the benefits to be reinstated. OK, the government cares about taxpayers money. In the meantime, the former Finance Minister granted tax-exempt status to companies with offshore headquarters, including his family's own corporation. Banks also jumped in, depriving the government of several billion dollars.
Canada also has a Firearms Interest Police database, used to store gossip about people, including crime victims. The contents are supposedly reserved to police and you cannot even get a copy of what is in there. And no recourse to expunge data that does not belong in there. And no guarantee either that this information is not exchanged with other countries.
Now, banks have to use the SIN number to report to the government the interest earned on obligations, etc.
About the fact that you cannot use an identification number that identifies you to the rest of the world, well "it does not have to make sense, it's government policy".
A brave new world.
December 29, 2008 at 9:26 am
Jim Russell (12/22/2008)
Do you think we could convince Steve Jones to allow .sqlplan attachments?
sqlplan files can be attached as .TXT files.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 29, 2008 at 10:38 am
RBarryYoung (12/29/2008)
Jim Russell (12/22/2008)
Do you think we could convince Steve Jones to allow .sqlplan attachments?sqlplan files can be attached as .TXT files.
Or zipped and attached, which I personally prefer as it reduces the size a lot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2008 at 10:42 am
Yes, good point, Gail. I should have picked up on that from Grant... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 31, 2008 at 5:18 pm
J
In Canada, the Social Insurance Number...
I love the acronym... it's a SIN our (Orwellian) governments peg us with a number that is useful for identification (to them) but forbidden to be used by others. Banks frequently passed this information to credit reporting agencies (hundreds of thousands of SIN's at a time) to obtain a credit score for a list of it's customers that are then sent back by those same agencies so the Bank can load them for marketing, credit worthiness, etc.
Welcome to the other side of the looking glass.
--Paul Hunter
January 1, 2009 at 12:20 pm
paulhunter (12/31/2008)
Welcome to the other side of the looking glass.
Heh... more like a one way mirror... could be interpreted as "check valve". 😛
--Jeff Moden
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply