Digital signature or ID to authenticate a record

[PaulA]

Greetings, all--

Is there a way in Access 2007 where one can had a field used to contain a digital signature or ID that will authenticate a record?

I'm not talking about having a pgf of a signature that could be contained in the field, but a system that allows for the authenticity of the signature and, therefore, the record. It would perhaps require a password to use. I've seen this in PDF forms.

Any help would be appreciated.

Thanks.

Paul

 

[Uncle Gizmo]

Are you thinking of recording an electronic signature like the couriers do with their portable PDA Machines?

 

[Galaxiom]

Windows has a command that can use a security certificate to produce a digital signature of a string or verify the string and signature match. The output is a long string (4KB) which could be stored read only in a secure database against the PK of a record for comparison.

You could concatenate the fields of a record and then produce a signature for that record from you security certificate using a Shell command call.

I have some stuff at work on signing strings but I have to go out now. I will get on the VPN tonight and dig it out for you.

Meanwhile you need to get your head around creating a Security Certificate.

Here is some stuff I wrote on a thread a while back.
http://www.access-programmers.co.uk/forums/showthread.php?t=205900

The self signed certificate is the easy one but it is much better if you can create a pfx because it is transportable between machines. Unless it is transportable you will be faced with maintianing an ever increasing number of signatures as you replace machines.

 

[PaulA]

Thanks for your responses. The security certificate may be the way to go although this wouldn't be for code. This is for a database that stores medical records, such as progress notes and we would want to authenticate and secure the information.

I know PDF forms has a digital id feature that you create the id and puting the id in a field requires a password that you are prompted for when you enter the field. I was wondering if there was something like that for Access.

 

[mdlueck]

If you can live with userid logging at form level, then you could configure "Access userids" in a variety of ways. I have one application that tracks authentication based on the ID logged into Windows. Access then uses that userid string to check a permission table in the database application to detirmine what permissions that ID should be allowed within the application.

The ID # is captured each time a user makes a change to a record, along with date/timestamp of what the change was made.

Going even further I could have implemented chronological history tracking, but such was not imperative for this application. (Nice, not imperative, thus saving time and not implementing that feature.)

If you are concerned about people directly editing the tables, then such would not be enough security in your case. (In my app's case, I intend to remove all linked table objects when the app goes production, thereby eliminating table view peek holes into the BE DB tables.)

 

[Galaxiom]

This is for a database that stores medical records, such as progress notes and we would want to authenticate and secure the information.

Would you require the record to be validated by the particular user such that a database administrator would be unable to alter a record without the validation being broken?

If so the Security Certificate is a workable solution. A Security Certificate can be used to sign any string, not just code. The way I do it just pretends that the string is vb code.

The output shows the original string and the matching signature. Any change to the string will cause the verify to fail.

However the only way to verify the signature belongs to the original signer is to sign the data with their signature again and see if it matches. This is a weakness because the private key needs to be used to verify.

I know PDF forms has a digital id feature that you create the id and puting the id in a field requires a password that you are prompted for when you enter the field. I was wondering if there was something like that for Access.

This is would be using the user's Digital Certificate and signing the actual file. Windows can be set to prompt for a password when the certificate is used. At the very least it will propmt to warn the Private Key is being used. Exactly the same thing happens when the key is used to sign some text.

Windows has a set of tools for working with signatures but it is all aimed at signing files rather than strings.

http://msdn.microsoft.com/en-us/library/8s9b9yaz(v=vs.80).aspx

However you are trying to sign a record rather than a file and there is nothing native in Access to support that.

 

[Galaxiom]

I have had a project on the back burner for a long time that required something very similar to what you are trying to do. Your question has prompted me to get back to it again.

I have previously gone as far as scripting the creation of the digital signature of a record and verifying the record against the signature but hadn't gotten to the bottom of how to identify the certificate owner directly from the signature itself.

Although I have not managed to find a correlation between the characters in the signature and the published details of the certificate I have now established that the first 255 characters in the signature remain consistent for a particular signature regardless of the signed content. Therefore it would be possible to establish the identity of the signer by comparing these characters with those extracted from a reference signature explicitly supplied by user or extracted from their other records.

The generated signature string also includes the original content. Since the record to be compared already contains this data it can be removed to save storage space. The signature is concatenated back onto the data for the verification test. This would save a lot of space on large records but care would be required to exactly reproduce the original signed string or the verify would fail.

The Signer is designed to work with text so binary content such as images would have to be first encoded using somethng like Base64 (used to encode attachemnts in email) or Base85 (used to embed binary content in pdfs).

The signing and verification code itself is very simple.

Function SignText (ByVal Input as String, ByVal SignatureName As String) As String
 
   Dim Signer as Object
 
      Set Signer = CreateObject("Scripting.Signer")
      SignText = Signer.Sign(".VBS", Input, Signaturename)
 
End Function
 
 
Function VerifyText(ByVal Input As String) As Boolean
 
   Dim Signer as Object
 
      Set Signer = CreateObject("Scripting.Signer")
      VerifyText = Signer.Verify(".VBS", Input, False)
 
End Function

The False argument in the Verify Method supresses the dialog that occurs if the verify is rejected.

As you can see the Signer Class is fooled into thinking it is signing a VB Script string.

 

[PaulA]

Thank you all for your replies.

Galaxiom, it seems we are in sync with what we are trying to do here. Thank you very much with what you provided. It was very helpful and I'll explore it for providing a solution to my situation.

Happy holidays to all!

paul