March 12, 2019 at 12:55 pm
Or company attorney, if they have one.
March 12, 2019 at 1:03 pm
Jeff Moden - Tuesday, March 12, 2019 12:00 PMglenn.rollins3 - Tuesday, March 12, 2019 11:36 AMJeff Moden - Tuesday, March 12, 2019 11:11 AMSean Lange - Tuesday, March 12, 2019 8:33 AMglenn.rollins3 - Tuesday, March 12, 2019 8:02 AMI've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'Thoughts?
I think that storing SSN in plain text is criminal behavior.
Preach it, Brother!!! Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.
Thanks, I'll pass it on to the company's IT department.
No... That's almost useless. Pass it on to the CEO, the head of Marketing (while explaining the black eye to the company and resulting future lost business if there's a breach), the head of Finance (how much the breach will actually cost, which is more than you can currently imagine), AND the Director of IT (explain how they might not ever be able to find another job in IT if a breach occurs). If the company is public, pass all that bad news on to the Board of Directors, as well. The rank stupidity of things like clear text SSNs has got to stop.
How do you really feel about this Jeff? It is hard to be sure. LOL. I agree on all points!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2019 at 6:49 am
Of course, you could always take the brute force approach. 🙂
Create a table with all valid SSNs and set up a left join to it.
By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!
Kind of like a rainbow table used for the power of good!
March 14, 2019 at 1:32 pm
roger.plowman - Wednesday, March 13, 2019 6:49 AMOf course, you could always take the brute force approach. 🙂Create a table with all valid SSNs and set up a left join to it.
By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as a clustered index). Of course, it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!
Kind of like a rainbow table used for the power of good!
As of a few years ago, the Social Security Administration did away with the original design of the Social Security number in favor of nine digits. There's no more grouping and other built-in information in the number. The problem was that they were running out of numbers, there were too many duplicates and forgeries (think illegal aliens and fake numbers), so they just gave up.
Medicare used to use the Social Security Number, that's been replaced by a string of 11 characters which mix digits and letters. Your best bet for validation is to subscribe to a service that will essentially do what you just said – keep a list in the database of what's been done.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 14, 2019 at 9:29 pm
roger.plowman - Wednesday, March 13, 2019 6:49 AMOf course, you could always take the brute force approach. 🙂Create a table with all valid SSNs and set up a left join to it.
By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!
Kind of like a rainbow table used for the power of good!
But first, stop storing the damned things in an unencrypted fashion. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2019 at 9:34 pm
Sean Lange - Tuesday, March 12, 2019 1:03 PMJeff Moden - Tuesday, March 12, 2019 12:00 PMglenn.rollins3 - Tuesday, March 12, 2019 11:36 AMJeff Moden - Tuesday, March 12, 2019 11:11 AMSean Lange - Tuesday, March 12, 2019 8:33 AMglenn.rollins3 - Tuesday, March 12, 2019 8:02 AMI've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'Thoughts?
I think that storing SSN in plain text is criminal behavior.
Preach it, Brother!!! Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.
Thanks, I'll pass it on to the company's IT department.
No... That's almost useless. Pass it on to the CEO, the head of Marketing (while explaining the black eye to the company and resulting future lost business if there's a breach), the head of Finance (how much the breach will actually cost, which is more than you can currently imagine), AND the Director of IT (explain how they might not ever be able to find another job in IT if a breach occurs). If the company is public, pass all that bad news on to the Board of Directors, as well. The rank stupidity of things like clear text SSNs has got to stop.
How do you really feel about this Jeff? It is hard to be sure. LOL. I agree on all points!!!
I know, huh? I can't believe that any company would be so stupid but there's constant evidence that it's actually a rampant problem. Heh... I guess that one came out loud too, huh? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2019 at 6:50 am
Jeff Moden - Thursday, March 14, 2019 9:29 PMroger.plowman - Wednesday, March 13, 2019 6:49 AMOf course, you could always take the brute force approach. 🙂Create a table with all valid SSNs and set up a left join to it.
By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!
Kind of like a rainbow table used for the power of good!
But first, stop storing the damned things in an unencrypted fashion. 😉
Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.
We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.
Besides, isn't there a federal law prohibiting the use of SSN as an identification number?
March 15, 2019 at 7:03 am
Sean Lange - Tuesday, March 12, 2019 8:33 AMglenn.rollins3 - Tuesday, March 12, 2019 8:02 AMI've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'Thoughts?
I think that storing SSN in plain text is criminal behavior.
It still happens, I seem to remember supplying my SSN all over the place and could not avoid it if I didn't want to live like a hermit in a cave 😉 This means its subsequently shared with upstream servicers, etc, and that means its going to be processed, oftentimes as a lookup / matching key. This means storage, transmission, matching etc, so in my opinion its going to be a hard sale on trying to get routine processing of this data "criminalized".
March 15, 2019 at 8:32 am
roger.plowman - Friday, March 15, 2019 6:50 AMJeff Moden - Thursday, March 14, 2019 9:29 PMroger.plowman - Wednesday, March 13, 2019 6:49 AMOf course, you could always take the brute force approach. 🙂Create a table with all valid SSNs and set up a left join to it.
By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!
Kind of like a rainbow table used for the power of good!
But first, stop storing the damned things in an unencrypted fashion. 😉
Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.
We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.
Besides, isn't there a federal law prohibiting the use of SSN as an identification number?
The Social Security Act itself states that the SSN cannot be used for identification.
March 15, 2019 at 9:35 am
Lynn Pettis - Friday, March 15, 2019 8:32 AMroger.plowman - Friday, March 15, 2019 6:50 AMJeff Moden - Thursday, March 14, 2019 9:29 PMroger.plowman - Wednesday, March 13, 2019 6:49 AMOf course, you could always take the brute force approach. 🙂Create a table with all valid SSNs and set up a left join to it.
By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!
Kind of like a rainbow table used for the power of good!
But first, stop storing the damned things in an unencrypted fashion. 😉
Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.
We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.
Besides, isn't there a federal law prohibiting the use of SSN as an identification number?
The Social Security Act itself states that the SSN cannot be used for identification.
Which is bewildering because the feds require it for tax transactions and tracking.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2019 at 11:46 am
roger.plowman - Wednesday, March 13, 2019 6:49 AMThe Social Security Act itself states that the SSN cannot be used for identification.
It is also illegal to murder people. How has that worked form you 🙂 ?
Please post DDL and follow ANSI/ISO standards when asking for help.
March 15, 2019 at 11:51 am
roger.plowman - Wednesday, March 13, 2019 6:49 AM
But first, stop storing the damned things in an unencrypted fashion. 😉
Say "AMEN!" , brother!! Or go to jail...
Please post DDL and follow ANSI/ISO standards when asking for help.
March 15, 2019 at 11:55 am
Jeff Moden - Thursday, March 14, 2019 9:29 PMroger.plowman - Wednesday, March 13, 2019 6:49 AMThe Social Security Act itself states that the SSN cannot be used for identification.
It is also illegal to murder people. How has that worked form you 🙂 ?
Wow, Mr. Celko, it has been illegal to murder people longer than Social Security has been around. Even the 10 Commandments states "Thou shall not murder."
March 16, 2019 at 2:58 pm
jcelko212 32090 - Friday, March 15, 2019 11:46 AMIt is also illegal to murder people. How has that worked form you 🙂 ?
BWAAAA-HAAAAA!!! Really good! No one has found the bodies yet. 😀:D:D
(For all you paranoid folks out there, it's just a joke ;))
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2019 at 7:27 am
Jeff Moden - Thursday, March 14, 2019 9:34 PMI know, huh? I can't believe that any company would be so stupid but there's constant evidence that it's actually a rampant problem. Heh... I guess that one came out loud too, huh? 😀
Not too loud at all...perhaps not loud enough!!!! We need bigger signs with more volume to stop this kind of insanity.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply