Protecting Data with Digital Signatures by Example using MySQL Enterprise Edition

Often databases contain data that needs to be proven as valid and authentic. We want to ensure that a known person or other sender (e.g. a trusted app) of the information can’t deny content, nor that the content can change without that person (senders) consent.

In this blog post we’re going to look closer at a method to use to verify data is authentic by implementing digital signing to support “validation” of data.  Using this method you can check and detect unauthorized changes.

Problem – Prevent/detect unauthorized data changes within MySQL ecosystem

  • Access Controls can’t keep DBAs or users with powerful data rights from doing updates and changes
  • Auditing helps – but
    • Isn’t externally verifiable
    • Its reactive versus preventive  – happens after an unwanted change
  • Encryption is an advanced access control
    • But then if at the column level data can’t be flexibly searched.
    • Not necessarily trying to “hide” this data, trying to show its Correct and Valid

Lets look at a use case

When things are OK

But when data is changed external to Jack the change is detected

In a nutshell – Solving with  Public Key cryptography

In this case Jack digitally signs the purchase order and stores it. Any changes can be detected by Jill.

Technical Summary

  1. Jack has a Private and Public Key pair associated with him.
    • Only he has access to the Private Key
    • Others have access to the Public Key
  2. Digitally signs his document with his private key
    • Create a digest on the document
    • Use digest and private key to produce a digital signature
    • Insert document with its signature
  3. Jill can verify the document is valid
    • Creates the digest for the document
    • Validate the document
      • Uses the digest, signature, and Jack’s public key
      • Its either valid or not valid.

A scripted example

I am showing in SQL for simplicity – your apps will be coded in a programming language – I’ll describe the changes needed for that at the end. Additionally you’ll see some select ‘blah blab’ @variable name along the way. These are just to echo so you can look at what’s happening.

The Users

  • Jack – signinguser –
    • Jack signs a deal – in this example a purchase agreement
  • Jill – verifyinguser –
    • Jill is in operations – she wants to validate the agreement before she executes and pays

Initial Setup – Connect to MySQL as an Admin

 

Connect to MySQL as signinguser

Connect to MySQL as verifyinguser

Result – Data is untainted – as Signed by Jack – so Jill sees all is good. 

Connect to MySQL and hack the data – Connect as Admin

Connect as verifying user (replaying the above verifying code)

Result – Data is tainted – Its not as it was when Signed by Jack – so Jill sees this Hack. 

What’s Next

So I just showed you an example. It’s not an actual application, but hopefully it makes it simple to see the mechanics of how digital signing works. In an actual client application you would basically change out 2 calls from SQL to code/language of your choice – C, C++, Java, .NET, Nodejs, PHP, etc.

  1. CREATE_DIGEST – with equivalent to get the SHA512
  2. ASYMMETRIC_SIGN – with equivalent to get the signed hash

The other change you might make is where the private key is stored. There are some more secure options related to that if you want to make sure your DBA can’t read the private.

Under the hood its OpenSSL

Our functions use the OpenSSL library so you could use OpenSSL library calls or other compatible libraries. These methods are standards so you should be able to mix and match.

Conclusion

I hope the blog shows that although PKI itself is complex, its not difficult to leverage it within your applications and make sure you have data integrity using digital signatures.  With this you can

  • Prove data is valid and authentic.
  • Confirm data ownership
  • Detect unauthorized changes

Secure your data – backed with this in combination with other techniques such as auditing and encryption

Note: I tried to keep things simple in the blog by design. I plan to write more blogs related to patterns like this for data protection and privacy.

5 thoughts on “Protecting Data with Digital Signatures by Example using MySQL Enterprise Edition

  1. Hi!
    I know it’s just a demo, but you still should use different privileges for the private key:
    In your snippets, it is kept in a table in schema “digsigndemo” on which “verifyinguser” has all privileges. So Jill could work as a hacker, modify the document and sign it, even modify Jack’s keys.
    Regards,
    Jörg

  2. Hi!
    Much better – probably the best one can do using MySQL.
    What I’d like even better is a combination of views and privileges:
    — a base table for the data:
    CREATE TABLE digsigndemo.privuserprivatekey( … );
    — a view which is user-specific:
    CREATE SQL SECURITY INVOKER VIEW digsigndemo.myprivatekey AS
    SELECT * FROM digsigndemo.privuserprivatekey
    WHERE privkkeyuser = user() WITH CHECK OPTION;
    — this view could be used by everybody, because it contains only the personal entry:
    GRANT ALL ON digsigndemo.myprivatekey TO public;
    Yes, I know MySQL doesn’t (yet?) support that approach, so I will continue dreaming 😉
    Regards,
    Jörg

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.