Sharing keys, passphrases with applications is problematic, especially with regard to encrypting data. Too often applications are developed where “the keys are left in the door” or at best “under the mat” – hard coded, in a clear text property file… exposed and vulnerable. And then there’s the whole issue of managing and changing and rotating keys.
Solving with Hybrid Approach
In this example, I’m going to show you a 2 tiered “hybrid” approach that uses Symmetric and Asymmetric methods together to protect your data. For simplicity I am going to demonstrate using SQL statements, but in practice you’d change out a few client side calls, which I comment on later. If needed, I’ll provide another blog showing a native client example in code versus SQL. But for now the point is to show the mechanics of this in its simplest form. This is just one possible use case, there are certainly other ways to tweak and change this to best fit your application needs.
Use case example – User of an app enters sensitive data that is only to be viewed by an authorized user.
Patient – Doctor
Buyer – Seller
Investor – Broker
Student – Teacher
The “low trust” app is gathering “sensitive” information. We don’t want to “trust” that app with keys or certificates. This application might be more exposed and is only providing data. The “high trust” app has authorized/trusted individuals who will see this “sensitive” data. Additionally software developers and IT admins are not authorized to see this data.
In a nutshell
On the low trust app:
- Get a public key (only encrypts)
- Generate a random key (for symmetric encryption)
- Symmetrically encrypt the sensitive data with that random key
- Store the symmetrically encrypted data
- Encrypt the random key with the public key
- Store the asymmetrically encrypted random key
On the trusted app:
- Get a private key (in this case from a protected database table)
- Decrypt the random key with the private key
- Decrypt the sensitive data with the random key – Read the data
On some schedule typically quarterly, you can rotate the private encryption key without re-encrypting the data.
- Generate a new private and public key pair
- Just Update the asymmetrically encrypted symmetric keys table by decrypting the keys with the old private key and encrypting with the new public key.
- Store the new public and private keys.
A big plus – when rotating the key you never have to actually decrypt and re-encrypt the raw data. Nor do you have to make any changes to my applications. This is a two-tiered “hybrid” approach. And there are additional benefits – symmetric encryption is high performance versus asymmetric which is computationally costly, thus with this hybrid approach your data can be quite large (say a big JSON document) and encryption performance is high.
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
- 1 – Appuser
- Don’t want to trust this user much
- The application using Appuser that gets data and stores it in the database encrypted
- 2- Trusteduser
- This user is trusted to see the data
I am using a schema called secdemo.
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
|
/* Create the schema for this demo database */ CREATE SCHEMA secdemo; /* Create the 2 user types */ CREATE USER 'appuser'@'localhost' IDENTIFIED BY ‘<STRONGPASSWORD-A>'; CREATE USER 'trusteduser'@'localhost' IDENTIFIED BY ‘<STRONGPASSWORD-B>'; /* Grant access to the trusted user */ GRANT ALL ON secdemo.* TO 'trusteduser'@'localhost'; /* Table secretsdata stores your “secret” as encrypted data */ CREATE TABLE `secdemo`.`secretsdata` ( `id` INT NOT NULL, `secret` VARBINARY(3000) NOT NULL, PRIMARY KEY (`id`)); /* secretskey – stores the encrypted secret key */ CREATE TABLE `secdemo`.`secretskey` ( `id` INT NOT NULL, `secretkey` VARBINARY(3000) NOT NULL, PRIMARY KEY (`id`)); /* secretspubkey – stores the public key – grant so web app can read the key */ CREATE TABLE `secdemo`.`apppublickey` ( `appid` INT NOT NULL, `publickey` VARBINARY(3000) NOT NULL, PRIMARY KEY (`appid`)); /* secretsprivkey – stores the private key */ /* grant above so only privileged user/app can get the private key */ |
This table stores the symmetric key encrypted with the public key. Note the privileges – there must not be a grant to secretsprivkey for appuser:
|
1
2
3
4
5
6
7
8
9
|
CREATE TABLE `secdemo`.`privuserprivatekey` ( `appid` INT NOT NULL, `privatekey` VARBINARY(3000) NOT NULL, PRIMARY KEY (`appid`)); /* GRANT TO APPUSER */ GRANT SELECT, INSERT, DELETE, UPDATE ON secdemo.secretsdata TO 'appuser'@'localhost'; GRANT SELECT ON secdemo.apppublickey TO 'appuser'@'localhost'; GRANT INSERT ON `secdemo`.`secretskey` TO 'appuser'@'localhost'; |
Now to create Asymmetric Keys:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
/* Key length in bits; make larger for stronger keys */ SET @key_len = 2048; SET @algo = 'RSA'; /* Create private key */ SET @priv = CREATE_ASYMMETRIC_PRIV_KEY(@algo, @key_len); select 'The Private Key', @priv; /* Store the private key for use by the trusteduser */ INSERT INTO `secdemo`.`privuserprivatekey` (`appid`,`privatekey`) VALUES (1, @priv); /* Server – Generate Public Key */ /* Derive corresponding public key from private key, using same algorithm */ SET @pub = CREATE_ASYMMETRIC_PUB_KEY(@algo, @priv); /* Store the private key for use by the appuser */ INSERT INTO `secdemo`.`apppublickey` (`appid`,`publickey`) VALUES (1, @pub); |
The “low trust” web application will now get the sensitive information and encrypt the data.
Connect as appuser:
|
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
|
/* Randomly generate the secret symmetric passphrase */ SET @passphrase = SHA2(lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0), 224); select 'Symmetric Passphrase', @passphrase; /* Here’s the secret */ SET @keepsecret='Santa and his Elves location is just north east of Longyearbyen, Norway'; select 'My secret prior to encrypting it', @keepsecret; /* Symmetrically encrypt the secret */ SET @enc_keepsecret=AES_ENCRYPT(@keepsecret,@passphrase); select 'The secret is unreadable now – its symmetrically encrypted ', @enc_keepsecret; /*Store it - I just hardwired in an id of 1 for this secret */ INSERT INTO `secdemo`.`secretsdata` (`id`,`secret`) VALUES (1, @enc_keepsecret); SELECT 'Decrypt just to check', AES_DECRYPT(@enc_keepsecret, @passphrase); /* Web Client – Encrypt passphrase with Public Key */ SET @key_len = 2048; SET @algo = 'RSA'; Set @enc_pp = ASYMMETRIC_ENCRYPT(@algo, @passphrase, @webpubkey); select 'Encrypted secret key', @enc_pp; /* Server - Store AES Data and PK Encrypted Passphrase */ INSERT INTO `secdemo`.`secretskey` (`id`, `secretkey`) VALUES (1, @enc_pp); |
Now the data is stored encrypted, and the symmetric key is encrypted with the public key is stored. The data can’t be decrypted with the public key. The low trust app doesn’t have any persisted keys.
|
1
2
3
4
5
6
|
/* Look at key (you can't no permission and even if could - it’s encrypted */ select * from `secdemo`.`secretskey`; /* Look at data - its encrypted */ select * from `secdemo`.`secretsdata`; |
Connect as trusteduser:
Next the trusted user wants to see the secret information:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET @key_len = 2048;SET @algo = 'RSA';" /* Read PK Encrypted Passphrase */ select secretkey from `secdemo`.`secretskey` where id=1 INTO @encpassphrase; /* Get Private Key */ select privatekey from `secdemo`.`privuserprivatekey` where appid=1 INTO @privappprivpkey; select 'privappprivpkey', @privappprivpkey; /* Decrypt passphrase with Private Key */ Set @enc_pp = ASYMMETRIC_DECRYPT(@algo, @encpassphrase, @privappprivpkey); select 'Original Symmetric Passphrase', @enc_pp; /* Privileged Client – See Secret Data - decrypt data with Passphrase */ select AES_DECRYPT(secret, @enc_pp) from `secdemo`.`secretsdata` where id=1; |
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 hybrid encryption works. In an actual client application you would basically change out 3 calls from SQL to code/language of your choice – C, C++, Java, .NET, Nodejs, PHP, etc.
-
SET @passphrase = SHA2(lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0), 224);
- To some other random to generate the secret passphrase
-
SET @enc_keepsecret=AES_ENCRYPT(@keepsecret,@passphrase);
- And make a library call to encrypt your secret information with AES
-
SET @enc_pp = ASYMMETRIC_ENCRYPT(@algo, @passphrase, @webpubkey);
- And asymmetrically encrypt your secret information
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 this data. I’ll blog about that separately.
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.
Also I used a secret key for each item I inserted. That’s just 1 way to do it. I’ve included a link to the full SQL text for download if you want to give it a try.
Conclusion
This is a simple and secure method to use encryption to protect the privacy of the data. Public key asymmetric encryption used along with symmetric encryption – thus hybrid encryption, has far more uses that it’s been applied to thus far. Which is a shame as it solves many of today’s problems quite nicely. 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.
References
- The raw data from these examples(easier to copy+paste):
hybrid-data-encryption-raw-sql.txt. - https://mysql.com/doc/refman/5.7/en/enterprise-encryption.html
- https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-database-privileges