Obfuscation (1 Viewer)

[mwabbe]

Hello, I am trying to secure the SSN of employees. is there anyway to encode what is typed in so that when it is saved in the form it will not be understandable to an unwanted viewer and then decode it if the user needs to view it?

thanks.

 

[The_Doc_Man]

Tons of ways - but the first question is this: Are you subject to regulations or statutes on how you store the information? There are pre-defined packages that can do an encryption on any string given some sort of key value, but unless you know about applicable laws on this subject (that apply to your geographic / political region), any answer you use is probably wrong.

 

[mwabbe]

I have no idea

 

[mwabbe]

i dont care how it is stored... arabic, greek, random number and letters

 

[boblarson]

I have no idea

What business are you in? Are you in the U.S. or a different country (I'm guessing U.S. due to using SSN but need confirmation).

If you are in the U.S. and working in Healthcare for example, the HIPAA regulations have specific requirements laid out for storing social security numbers. And you MUST know what you are doing or you could wind up with a potentially devastating fine against your company should something happen and the numbers get "released into the wild."

So, you really need to find out the legal ramifications and obligations of your location before storing SSN's. It is very important.

 

[mwabbe]

yes i am in the us. thank you for the information. i will talk to them about that.

 

[mwabbe]

how would i start doing something like this

 

[boblarson]

This might give you enough, depending on your situation.

 

[mwabbe]

i have actually looked at that before but i dont know where to call it from. do i call it on the form or behind the ssn txt box and then where after that afterupdate? im not sure. thanks

 

[gemma-the-husky]

I dont quite see why a SSN something that needs to be secured.

However, if you want to roll your own, the easiest solution is to do a boolean xor on each character of the string, with some given constant

The nice thing about xor, is that if you do it again, you get the original string back.

It's probably sufficient to defeat anyone opening the table directly.


sort of this idea in general terms, although this isnt valid VBA

for x = 1 to len(SSn)
ssn(x) = ssn(x) xor someconstant
next

-----------------
I see Bob's already linked to this idea - he obviously keeps a list of useful links ready at hand

 

[The_Doc_Man]

Dave, the issue with security of the SSN isn't really the issue. It is what appears along with the SSN that complicates life tremendously.

In my Dept. of Defense job, I run into discussions of personally identifying information (PII) and the gyrations required to prevent its misuse. If you have a record containing SSN and just about any other personal thing about someone - name, phone, address are the biggies - that record must be treated in gov-speak as "FOUO" (for official use only) or "SBU" (sensitive but unclassified) data.

The levels and type of encryption required at that point become nightmarish if you don't have a proper encryption suite handy. Usually, when it gets this far, you need either your O/S vendor's encryption suite or a good commercial (third-party) package.

Which is why it can be an issue. Since HIPAA and a couple of amendments to the Privacy Act of 1975, even private business can get a regulatory slap from someone for not treating personal privacy adequately. Some states make it an expensive corporate felony that will result in hefty fines. Really hefty fines. Not to mention that careless handling of PII can open you up to lawsuits if someone steals an identity based on data they got from you.

 

[gemma-the-husky]

I see - I dont think UK bother so much - I have never seen the UK equivalent encrypted in any way.

That probably helps account for why we have more SSN numbers than people in the country.

 

[mwabbe]

so how do i call that function to work the way i want it to. well where do i call it from.
thanks

 

[boblarson]

In the Before Update event you would call it to change the value going in.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  EncryptDecrypt Me.YourControlNameWhichHasTheSSN
End Sub

 

[mwabbe]

once i get this working i want to save it in the tables that way. so on the form the user types in the number then the number is encrypted. how do i save it in the table linked to the form as the encrypted value not the original value?

 

[boblarson]

once i get this working i want to save it in the tables that way. so on the form the user types in the number then the number is encrypted. how do i save it in the table linked to the form as the encrypted value not the original value?

In the form's Before Update event as mentioned:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.YourControlNameWhichHasTheSSN = EncryptDecrypt(Me.YourControlNameWhichHasTheSSN)
End Sub

 

[gemma-the-husky]

before saving the record, therefor best done in the beforeupdate event for the form, call the encrypt routine ot turn the plaintext into the encrypted form.

in the current event, befre displaying the SSN, use the decrypt mechanism to change the data into plaintext.

you main find it nnecessary to use an unbound "working" textbox to store the visible SSN no.

 

[boblarson]

before saving the record, therefor best done in the beforeupdate event for the form, call the encrypt routine ot turn the plaintext into the encrypted form.

in the current event, befre displaying the SSN, use the decrypt mechanism to change the data into plaintext.

you main find it nnecessary to use an unbound "working" textbox to store the visible SSN no.

And I would suggest perhaps only showing the last 4 digits and give a way to get the whole thing if necessary. But most of the time it isn't necessary and having it shown at all is really a potential spot of ID theft. So, I would not show it unless needed, and only when needed.

 

[mwabbe]

well i here is what i have on the form that the data is typed in:

Private Sub Form_BeforeUpdate(Cancel As Integer)
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("EmpInfo", "audTmpEmpInfo", "ID", Nz(Me.ID, 0), bWasNewRecord)
If Not IsNull(Me.SSN) Then
Call Enc("Me.SSN")
End If
End Sub


Private Sub Form_Current()
If Not IsNull(SSN) Then
Call Dec("SSN")
End If
End Sub

and here is the enc/dec modual:

Dim sSecretData
Dim sCipherText
Dim capEData
Dim sPlainText
Dim key


Function Enc(ByVal SSN As String) As String
sSecretData = "SSN"

key = "Super duper password"


' Build up the key

Set capEData = CreateObject("CAPICOM.EncryptedData.1")
capEData.Algorithm = 3 'Use 3DES
capEData.SetSecret key
capEData.Content = sSecretData

sCipherText = capEData.Encrypt

Beep
MsgBox "Original data:" & sSecretData, vbOKOnly, ""
MsgBox "Encrypted data: " & sCipherText, vbOKOnly, ""

End Function

Function Dec(ByVal SSN As String) As String

key = "Super duper password"

capEData.Algorithm = 3
capEData.SetSecret key
capEData.Decrypt sCipherText

sPlainText = capEData.Content

MsgBox "Recoverd data: " & sPlainText, vbOKOnly, ""

End Function

after it saves it tells me from the msgbox that i has been encrypted and what it has been encrypted too but when i look in the table part it is not encrypted it still shows the original data

 

[The_Doc_Man]

OK, first... triple-DES (3DES) is probably as good a standard as you need for encryption. It emphatically meets FIPS 140-2 standards, which is what the Dept. of Defense mandates. So that part is a good approach.

Second, unless you actually stored the data encrypted you won't see it encrypted. From what you showed us of the code, you need to store the encrypted string in the control before you do the update. I see where you encrypted the string, but you called a FUNCTION using SUB syntax, so essentially there is no command to store what you computed.

I think you have the tools you need here. It is a matter of application, not theory now.

I would do this as follows. On the form requiring encryption/decryption operations, I would take all applicable fields and put them on the form as INVISIBLE bound text boxes. Permanently invisible, i.e. never changed by code. I would then put visible unbound text boxes corresponding to each item to be encrypted. With me so far?

Now in two places, I would put special code...

In the Form_Current event, I would decrypt each encrypted but invisible text box.

In the Form_BeforeUpdate event, I would decrypt each encrypted but invisible text box to see if the visible text box changed. In that case, I would encrypt the visible text box and store it in the corresponding invisible text box.

Suppose for this example that the encrypted but invisible text box is [SSNE] and the visible but unbound box is [SSNV].

In the Form_Current routine:

    [SSNV] = Dec([SSNE])

In the Form_BeforeUpdate routine

    If [SSNV] <> Dec([SSNE]) Then
        [SSNE] = Enc([SSNV])
    End If

Same concept would apply to ANYTHING you encrypted, even if it was the entire record.

Be warned that if you intend to do searches, you are going to have a lot of calls to that DEC(x) code. This would be a really good argument in and of itself for use of an autonumber or other synthetic key since the SSN in this case will be heavily encrypted. Also, you can build an index on SSN but you will have to use the ENC function on your search key, which in turn means that the little "binoculars" search icon and Find functions will be useless from the GUI. Therefore you will have a bit of extra code to manage for any forms that have an SSN search function.