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
- 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
- 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
- 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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
CREATE USER 'verifyinguser'@'localhost' IDENTIFIED BY '8374@#$%GRfDgfPTfg'; CREATE USER 'signinguser'@'localhost' IDENTIFIED BY '*&^*(9879879DDSdse'; drop schema IF EXISTS digsigndemo; create schema digsigndemo; /* Stores the document, signer, signature for the document */ CREATE TABLE `digsigndemo`.`signedocument` ( `id` INT NOT NULL, `document` VARCHAR(3000) NOT NULL, `version` INT NOT NULL, `signedby` varchar(100) NOT NULL, `signature` VARBINARY(3000) NOT NULL, PRIMARY KEY (`id`,`version`)); /* The table with the public key to use to verify documents with signature */ CREATE TABLE `digsigndemo`.`apppublickey` ( `pubkkeyuser` varchar(100) NOT NULL, `publickey` VARBINARY(3000) NOT NULL, PRIMARY KEY (`pubkkeyuser`)); /* The table with the users private key */ CREATE TABLE `digsigndemo`.`privuserprivatekey` ( `privkkeyuser` varchar(100) NOT NULL, `privatekey` VARBINARY(3000) NOT NULL, PRIMARY KEY (`privkkeyuser`)); /*GRANT TO verifyinguser */ GRANT SELECT `digsigndemo`.`signedocument` TO 'verifyinguser'@'localhost'; GRANT SELECT `digsigndemo`.`apppublickey` TO 'verifyinguser'@'localhost'; /*GRANT TO signinguser */ GRANT ALL ON digsigndemo.signedocument TO 'signinguser'@'localhost'; GRANT SELECT ON digsigndemo.apppublickey TO 'signinguser'@'localhost'; GRANT SELECT ON digsigndemo.privuserprivatekey TO 'signinguser'@'localhost'; /* Generate Key Pair */ SET @key_len = 2048; SET @algo = 'RSA'; /* Create Jacks private key */ SET @priv = CREATE_ASYMMETRIC_PRIV_KEY(@algo, @key_len); select @priv; INSERT INTO `digsigndemo`.`privuserprivatekey` (`privkkeyuser`,`privatekey`) VALUES ('signinguser@localhost', @priv); /* Create Public Key */ /* Derive corresponding public key from private key, using same algorithm */ SET @pub = CREATE_ASYMMETRIC_PUB_KEY(@algo, @priv); INSERT INTO `digsigndemo`.`apppublickey` (`pubkkeyuser`,`publickey`) VALUES ('signinguser@localhost', @pub); |
Connect to MySQL as signinguser
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
/* Signing Client – Get Public Key for connected user */ select CURRENT_USER() INTO @curuser; select 'Current user is ', @curuser; select publickey from `digsigndemo`.`apppublickey` where pubkkeyuser=@curuser INTO @webpubkey; select 'signingusers public key', @webpubkey; /* Signing Client – Encrypt Data with passphrase */ SET @agreement='I, Jack Smith, agree to pay $1000 for 50 super gizmos delivered on 06/08/2017.'; select 'Jacks agreement', @agreement; SET @dig_type = 'SHA512'; SET @algo = 'RSA'; select 'Show my private key', `privatekey` from `digsigndemo`.`privuserprivatekey` where `privkkeyuser`=@curuser; select `privatekey` from `digsigndemo`.`privuserprivatekey` where `privkkeyuser`=@curuser into @priv; -- Generate digest string SET @dig = CREATE_DIGEST(@dig_type, @agreement); select 'Show my digest', @dig; /* Sign the document */ SET @sig = ASYMMETRIC_SIGN(@algo, @dig, @priv, @dig_type); select 'My Signature is ', @sig; /* Insert the signed document */ INSERT INTO `digsigndemo`.`signedocument` (`id`,`document`,`version`,`signedby`,`signature`) VALUES (1, @agreement, 1, @curuser, @sig); /* View data for grins */ SELECT * from `digsigndemo`.`signedocument`; |
Connect to MySQL as verifyinguser
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SET @algo = 'RSA'; SET @dig_type = 'SHA512'; /* Get the document to verify */ select `signedby`,`signature`, CREATE_DIGEST(@dig_type, `document`) as digest from `digsigndemo`.`signedocument` where id=1 and version=1 into @siguser, @sig, @dig; select 'Algo', @algo, 'pub', @pub, 'type ', @dig_type, 'Document Digest', @dig; /* Get the public key so we can verify the document */ select publickey from `digsigndemo`.`apppublickey` where pubkkeyuser=@siguser INTO @pub; select 'Signing user',@siguser, ' of document 1 version 1 was ', @pub; /* Verify the document */ SET @verf = ASYMMETRIC_VERIFY(@algo, @dig, @sig, @pub, @dig_type); SELECT 'Verify', if(@verf,'Verified Document','Document Verification Failed'); |
Result – Data is untainted – as Signed by Jack – so Jill sees all is good.
|
1 |
'Verified Document' |
Connect to MySQL and hack the data – Connect as Admin
|
1
2
|
/* Make unauthorized revision to the price */ UPDATE `digsigndemo`.`signedocument` SET `document`='I, Jack Smith, agree to pay $999999 for 50 super gizmos delivered on 06/08/2017.' WHERE `id`='1' and`version`='1'; |
Connect as verifying user (replaying the above verifying code)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SET @algo = 'RSA'; SET @dig_type = 'SHA512'; /* Get the document to verify */ select `signedby`,`signature`, CREATE_DIGEST(@dig_type, `document`) as digest from `digsigndemo`.`signedocument` where id=1 and version=1 into @siguser, @sig, @dig; select 'Algo', @algo, 'pub', @pub, 'type ', @dig_type, 'Document Digest', @dig; /* Get the public key so we can verify the document */ select publickey from `digsigndemo`.`apppublickey` where pubkkeyuser=@siguser INTO @pub; select 'Signing user',@siguser, ' of document 1 version 1 was ', @pub; /* Verify the document */ SET @verf = ASYMMETRIC_VERIFY(@algo, @dig, @sig, @pub, @dig_type); SELECT 'Verify', if(@verf,'Verified Document','Document Verification Failed'); |
Result – Data is tainted – Its not as it was when Signed by Jack – so Jill sees this Hack.
|
1 |
'Document Verification Failed' |
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.
- CREATE_DIGEST – with equivalent to get the SHA512
- 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.