Database encrypted fields & key lifetime policies

Encryption Key Lifetimes

The majority of projects I come across that utilize or implement symmetric encryption don't necessarily address the issue of the key lifetime.

In simplest terms, the longer a key is in use is directly proportionate to the strength of the key.

As Tyle Durden puts it, "On a long enough time line, the survival rate for everyone drops to zero."

Problem With Administration

Maintaining a key's lifetime can be time consuming.

First you have to decrypt each and every field within a database using the current key, generate a new key of adequate length, then re-encrypt the data using the new key. This is, and should be an operation that occurs at a scheduled time and preferably off duty hours.

Consider the following scenario;

A key has been in use for six months, the current threat model dictates each database encryption key should be rotated every three months. Think of a public facing database server within a corporate DMZ.

Depending on the size of the database this quite literally could take hours and possibly days and its key is already past its life cycle by three months which has severely weakened the strength of its usefulness.

I Need A Robot!!

Indeed, I could use one to get me beers but modern science has yet to grant me this one simple wish.

For those with fairly large budgets enterprise solutions exist, however for those of us that exist on shoe string budgets perhaps my project; sqlSec can be of assistance.

I have been developing sqlSec to help with symmetric key rotation using a series of stored procedures in MySQL.

The robot is not really a robot, although for the daring it can be used and fired using an event scheduler to automatically rotate encryption keys.

Features?

Sure, it has quite a few:

1. Easy installation
2. Easy configuration
3. Auto-backup options
4. Scheduler ready
5. Testing tools
6. Migration tools

Download?

You can use Git (recommended) or download the package.

%> git clone https://github.com/jas-/sqlSec.git

Install?

I have made this process as easy as possible. Once you have extracted the zip file or cloned the repo simply run the installer.

%> ./install
sqlSec
Automate encryption key rotation for database
encrypted fields to meet password lifetime
security policies

Creating necessary database creation objects...

Database installation credentials

Enter MySQL username: root
Enter root MySQL password: 

Database settings

Database server name [localhost]: 
1) dhcp
Select database to use: 1

Backup directory [/tmp]: 

Create a backup?  [Y/n] 
Backup created... /tmp/2013-10-23-dhcp.sql

Creating database, users & permissions
Creating key rotaton procedures

Specify encrypted fields for database: dhcp

1) cors                  8) interfaces          15) sqlSec_settings
2) dns_servers           9) myTest              16) subnets
3) dns_zones            10) options             17) traffic
4) dnssec_keys          11) pools               18) viewServers
5) failover             12) routes              19) viewServersDetails
6) groups               13) servers             20) viewTraffic
7) hosts                14) sqlSec_map          21) Quit
Select table to view fields: 7
1) id                4) hardware-address  7) lease
2) hostname          5) subnet            8) notes
3) address           6) group             9) Main
Select field to enable encryption: 4
1) id                4) hardware-address  7) lease
2) hostname          5) subnet            8) notes
3) address           6) group             9) Main
Select field to enable encryption: 6
1) id                4) hardware-address  7) lease
2) hostname          5) subnet            8) notes
3) address           6) group             9) Main
Select field to enable encryption: 9
1) cors                  8) interfaces          15) sqlSec_settings
2) dns_servers           9) myTest              16) subnets
3) dns_zones            10) options             17) traffic
4) dnssec_keys          11) pools               18) viewServers
5) failover             12) routes              19) viewServersDetails
6) groups               13) servers             20) viewTraffic
7) hosts                14) sqlSec_map          21) Quit
Select table to view fields: 12
1) id
2) hostname
3) route
4) address
5) Main
Select field to enable encryption: 4
1) id
2) hostname
3) route
4) address
5) Main
Select field to enable encryption: 5
1) cors                  8) interfaces          15) sqlSec_settings
2) dns_servers           9) myTest              16) subnets
3) dns_zones            10) options             17) traffic
4) dnssec_keys          11) pools               18) viewServers
5) failover             12) routes              19) viewServersDetails
6) groups               13) servers             20) viewTraffic
7) hosts                14) sqlSec_map          21) Quit
Select table to view fields: 16
1) id        3) subnet    5) mask      7) route
2) hostname  4) address   6) dns       8) Main
Select field to enable encryption: 4
1) id        3) subnet    5) mask      7) route
2) hostname  4) address   6) dns       8) Main
Select field to enable encryption: 8
1) cors                  8) interfaces          15) sqlSec_settings
2) dns_servers           9) myTest              16) subnets
3) dns_zones            10) options             17) traffic
4) dnssec_keys          11) pools               18) viewServers
5) failover             12) routes              19) viewServersDetails
6) groups               13) servers             20) viewTraffic
7) hosts                14) sqlSec_map          21) Quit
Select table to view fields: 4
1) id         3) keyname    5) secret
2) hostname   4) algorithm  6) Main
Select field to enable encryption: 5
1) id         3) keyname    5) secret
2) hostname   4) algorithm  6) Main
Select field to enable encryption: 6
1) cors                  8) interfaces          15) sqlSec_settings
2) dns_servers           9) myTest              16) subnets
3) dns_zones            10) options             17) traffic
4) dnssec_keys          11) pools               18) viewServers
5) failover             12) routes              19) viewServersDetails
6) groups               13) servers             20) viewTraffic
7) hosts                14) sqlSec_map          21) Quit
Select table to view fields: 21
Cleaning up...

The process performs the following operations:

1. Creates a randomly generated user account (privilege separation)
2. Optionally creates backup of existing database prior to migration
3. Adds two tables to existing database (version info & table/field mapping table)
4. Imports stored procedures used to perform key rotation operations
5. Provides a wizard interface to add table/field mappings which require use of encryption

Testing?

Yes, yes there are. In order to run them make sure you have run the installer, selected a database to modify & then selected table & field combinations which you wish to use as encrypted fields within the database.

If you simply wish to test with bogus data you can use the following example. This example creates 100 bogus records per table/field combination specified during the installation process so the total number of records for 5 fields on 5 tables would be 500.

%> mysql -u [username] -p[password] [db-name] -e 'CALL sqlSec_DBG_FP(100)'

Next we can perform a rotation process. During this process there is quite a few things taking place.

1. A backup is created if specified to do so
2. Any table/field combinations are used within primary loop
3. The current encyption key is loaded
4. A temporary table is created
5. A view is created based on temporary table (used as cursor loop due to limitations in MySQL)
6. The table/field combination values are decrypted with current key and placed in newly created temporary table
7. A new encryption key is randomly generated
8. The decrypted data is then encrypted with the new encryption key
9. The newly encrypted data updates the original record id as to minimize disruption of record sets
10. Temporary tables & views are removed

Here is a testing procedure to perform the above process X number of times, in this case X=10.

%> mysql -u [username] -p[password] [db-name] -e 'CALL sqlSec_DBG_Test(10)'

Usage?

The easiest method of saving & retrieving data once you implement the sqlSec project would be to create stored procedures to handle access to the decrypted (plain text) of the cipher text fields. Here are a few examples:

Searching records
Here is a simple example of creating a stored procedure which will search a table which contains encrypted fields.

DELIMITER //
CREATE PROCEDURE Search(IN search_param CHAR(128))
BEGIN
 CALL KR_GK(@Secret);
 SELECT `plain_text_field`, AES_DECRYPT(BINARY(UNHEX(cipher_text_field)), SHA1(@Secret)) AS cipher_text_field WHERE `plain_text_field` LIKE search_param OR AES_DECRYPT(BINARY(UNHEX(cipher_text_field)), SHA1(@Secret)) LIKE search_param;
END//
DELIMITER ;

Adding new records
Here is an example procedure that can be used to insert new encrypted data

DELIMITER //
CREATE PROCEDURE Add(IN plain_txt_field CHAR(128), IN cipher_txt_field CHAR(128))
BEGIN
 CALL KR_GK(@Secret);
 INSERT INTO `table_name` (`plain_text_field`, `cipher_text_field`) VALUES (plain_txt_field, HEX(AES_ENCRYPT(cipher_txt_field, SHA1(@Secret))));
END//
DELIMITER ;

I hope that helps some of you with small budgets, this took me a week or so of development but should serve you well.