Alternative to Social Security Numbers? 1

[Floyd7]

Hi,

Is there a way to alias or mask social security numbers? I need to use them as unique ids for current and new enrollees.

I was wondering if a lookup table would work, where you switch a random autonumber for a social. If this will work, is it more secure than simply storing the socials?

Thank you all!

 

[fneily]

I worked for Social Security at their headquarters in Woodlawn, Md.
First, Social Security numbers are NOT unique. A person can have more then one SSN.
Second, it is against the law for anyone or any organization to use SSN's to track people.
A better way is to make up an unique primary key. Such as the first three letters of their last name and a number. eg. Smi1, Smi2 for John Smith, Joe Smith.

 

[Floyd7]

Yikes! Thank you. I would probably go with first name, since last name could change over time.

So going with your scenario, how would you know if you're entering someone that has already registered?

 

[fneily]

See:
thread702-1370972

 

[Floyd7]

But what would stop you from simply creating a new unique identifier for the same person? How would I know what Joe Smith_1's id is as compared to a brand new Joe Smith?

 

[TheAceMan1]

[blue]First, Social Security numbers are NOT unique. A person can have more then one SSN.
Second, [purple]it is against the law for anyone or any organization to use SSN's to track people.[/purple][/blue]

This would be primary for my library! . . . Can you provide any [blue]regulation documentation[/blue] for this?

I'm prepared to go toe to toe with this as I've been involved in a court case (in my past) for just this thing (a database using SSN as a primary key) . . . The bottom line was the [blue]Social Security Adminstration[/blue] can't accuse someone of doing exactly what they do!: [blue]Identifying someone by SSN![/blue]

Case In Point . . . The IRS is an organization that requires SSN. Yet I'll bet you've never said anything to them!

My point is your relating to [blue]Tototootoo[/blue] a sense of false security!

SSN is reqired by every company, insurance organization, IRS, and most (if not all ) you work for!

[blue]I await your answer! . . .[/blue]

[blue]I understand . . . easy to say . . . hard to prove! . . .[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[fneily]

TheAceMan1 - I never said that NOBODY uses SSN to track people or employees. The government has never ENFORCED the law. In fact, Virginia, up to a couple of years ago used SSN for their driving license id.
If you go to your nearest SS office, you can request an application for another SSN. Before you whine, try it.
"The IRS is an organization that requires SSN." So does JC Penny for a credit card. But you don't HAVE to give it to them. They don't HAVE to give you a card either. They don't HAVE to give you insurance. ETC. ETC.
You stated that the court actually RULED that the defendant can use SSN as their SOLE tracking criteria?? Please post the court's WHOLE decision with circuit or district court information.
And since, as I stated, I don't work for SS anymore, I'll have to contact my ex-peers over at headquarters for the regulations.
Of course, as you so subtly say "I understand . . . easy to say . . . hard to prove! . . .", prove with YOUR official regulations that you can use SSN for tracking.

 

[Remou]

Some notes that may be of interest:

http://www.jhu.edu/news_info/policy/ssnuse.html

 

[fneily]

Mr. Remou - nice to see an article from my alma mater referenced. JHU - 2007 lacrosse champions!! I was on the 1974 championship team myself.
But for a better reference, how about the SSA itself?
From

http://ssa-custhelp.ssa.gov/

Question
"Must I provide a Social Security number (SSN) to any business or government agency that asks?

The Privacy Act regulates the use of SSNs by government agencies. When a Federal, State, or local government agency asks an individual to disclose his or her Social Security number, the Privacy Act requires the agency to inform the person of the following: the statutory or other authority for requesting the information; whether disclosure is mandatory or voluntary; what uses will be made of the information; and the consequences, if any, of failure to provide the information.

If a business or other enterprise asks you for your SSN, you can refuse to give it. However, that may mean doing without the purchase or service for which your number was requested. For example, utility companies and other services ask for a Social Security number, but do not need it; they can do a credit check or identify the person in their records by alternative means.

Giving your number is voluntary, even when you are asked for the number directly. If requested, you should ask why your number is needed, how your number will be used, what law requires you to give your number and what the consequences are if you refuse. The answers to these questions can help you decide if you want to give your Social Security number. The decision is yours."

 

[Floyd7]

Either way, I'd rather not store them. So...does a lookup table work?

 

[fneily]

Sometimes debates arise from someone's answer. Back to your problem. You should study Normalization. You would have two fields for name, eg. FirstName, LastName. Are you just storing someone's name? Or also personal info on them such as SSN, salary, home address? If so, then you would have two tables. One table would look like this:
EmployeeID, Firstname, Lastname, OfficePhone, etc.
The other table would look like this:
EmployeeID, SSN, Salary, HomePhone, etc.
They would be in a one-to-one relationship. You may ask, then why not combine the info into one table? Security reasons. Users will access the first table to find a person at work but won't be able to see the other table containing personal information.
To check for duplicates, again, did you check:
thread702-1370972: Show Record if it already exists
Using my idea about creating the primary key (employeeID) and your idea of first name, then for John Smith it would be Joh1, For another John Smith, it would be Joh2. etc. you can create your own rules as long as it will make an unique identifier.

 

[JoeAtWork]

First, Social Security numbers are NOT unique. A person can have more then one SSN.

Yes - but multiple people can't have the same SSN. So whether you have that person's first or second SSN, you have a number that identifies that person.

That being said, I wouldn't use SSN as a unique ID because, as you've said, some people won't give it to you.

As for your point about it being against the law to use people's SSN, I think it's safe to say that it's completely unenforceable and none of us need to worry about being convicted.

Tototootoo - sorry we are hijacking your thread. I'm not sure what you're asking in your last post. If all you want is a unique identifier for each person, would not a simple autonumber suffice?

If you are looking for a way to avoid having the same person entered in the database twice, that is virtaully impossible to achieve 100% of the time. Different companies have different criteria to identify a person, some may simple go by the telephone number, some may go by a combination of fields (first name + last name + address + DOB). This is in fact why so many databases require the SSN, it is the closest you can get to uniquely identifying an individual (except for really high-tech stuff like retinal scans).

 

[Floyd7]

No problem! I think it's an interesting discussion.

I was hoping to avoid duplicating a person who has already been entered into our database.

I was thinking of assigning each social an autonumber, and then if that social was entered again, it would match up against the corresponding autonumber. I just wasn't sure that it would be any safer to have a table with SSNs in one field and corresponding numbers in another.

 

[TheAceMan1]

Tototootoo . . .

Here's a function that will [blue]convert SSN via two values producing the ciphered result.[/blue] I designed the function to be dual in purpose . . . Convert an SSN to cipher or convert a previously ciphered SSN to the origional. Note: SSN is considered as 9 text digits without the dashes:

[blue]Public Function CipherSSN(SSN As String)
   Dim Mask As String, Build As String, x As Integer
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   If Len(SSN) <> 9 Then
      Msg = "SSN Must Be Exactly 9 Digits!" & DL & _
            "Check SSN '" & SSN & "' and try again . . ."
      Style = vbInformation + vbOKOnly
      Title = "SSN Not 9 Digits Error! . . ."
      MsgBox Msg, Style, Title
      CipherSSN = Null
   Else
      Do
         x = x + 1
         
         If Asc(Left(SSN, 1)) < 58 Then
            If (x Mod 2) = 0 Then
               Mask = 64
            Else
               Mask = 32
            End If
         Else
            If (x Mod 2) = 0 Then
               Mask = -64
            Else
               Mask = -32
            End If
         End If
         
         Build = Build & Chr(Asc(Mid(SSN, x, 1)) + Mask)
      Loop Until x = 9
      
      CipherSSN = Build
   End If
   
End Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

fneily! . . .

I was [blue]just pushing for any regulations you could provide.[/blue] Didn't mean to ruffle your feathers . If you don't mind perhaps we can continue this off line (not a topic for Tek-Tips, but one I've been researching for years . . . [blue]see my profile![/blue])! I've even paid for guidance to no avail! I just want to have the right documentation for anyone who comes across this street! . . .

Ya see . . . I was called into the case as a consultant . . . not as the designer! . . . Imagine [blue]knowing how I felt realizing a DB I made could possibly bring the company down![/blue]

BTW: This issue goes back to Access 2. I was able to get ahold of the company, but its under new powers and they were very nasty as to my inquiry about the case and infor thereof (identifying myself as the DB programmer! . . .)

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Floyd7]

Thank you for the code.

Unfortunately, I'm not very familiary with VB, and I'm having trouble running the function. I've put the function in a module, and then call the module from my form w/a command button. When I click the command button, it always finds Len(SSN) <> 9, even if I enter 9 characters.
Any ideas?

Thanks again.

 

[TheAceMan1]

Tototootoo . . .

The SSN passed is a string or string variable without the dashes. For example:

Me!TextboxName = CipherSSN("123456789") returns [blue]QrStUvWxY[/blue]
and
Me!TextboxName = CipherSSN("QrStUvWxY") returns [blue]123456789[/blue]

Do not name the module the same as the function!

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Floyd7]

Hi TheAceMan1,

I'm not including dashes, nor did I name the module the same as the function (initially I did, but then I realized that didn't work).

Am I being too VB-naive by just copying your code, making sure my Social field is named SSN, and then calling the module from a command button on Click?

Thank you for your patience.

 

[randy700]

If the problem is caused because many people are unwilling to provide their social security number, how does the cipher function help? You still need the social security number for initial input...


Randy

 

[TheAceMan1]

Tototootoo . . .

Post the code in the button . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884: