Assuming you have a backup and recovery strategy in place, how secure is your data? Does a hacker need to obtain access to your production system bypassing all the appropriate security protection you have in place, or just the unencrypted data on the backup server?
Encryption with zNcrypt
The following steps demonstrate how I setup a mysqldump encrypted backup with zNcrypt, a product from Gazzang. You can request a free trial evaluation of the software from http://gazzang.com/request-a-trial. I asked for a AWS EC2 instance, and was able to provide my bootstrap instructions for OS and MySQL installation. Following installation and configuration, the first step is to verify the zNcrypt process is running:
$ sudo ezncrypt-service status
ezncrypt | Checking system dependencies
** ezncrypt system is UP and running **
log | File: /var/log/ezncrypt/ezncrypt-service.log
If the process is not running you would find the following error message:
$ sudo ezncrypt-service status
ezncrypt | Checking system dependencies
** ezncrypt system is NOT running **
log | File: /var/log/ezncrypt/ezncrypt-service.log
$ sudo ezncrypt-service start
ezncrypt | Checking system dependencies
ezncrypt | checking encryption directories
keymgr | Retrieving key from KSS
| > Encryption password retrieved from KSS
ezncrypt | starting service
| > using "aes_256" cipher algorithm
| done!
access | Loading access control list
| done!
ezncrypt | Thank you for using ezncrypt.
log | File: /var/log/ezncrypt/ezncrypt-service.log
Under the covers you will find the following attached devices, and no actual processes.
$ df -h Filesystem ... ... /var/lib/ezncrypt/storage/encrypted_private /var/lib/ezncrypt/ezncrypted $ ps -ef | grep ezn uid 4947 3327 0 23:15 pts/3 00:00:00 grep ezn $ ps -ef | grep cry root 30 2 0 21:41 ? 00:00:00 [ecryptfs-kthrea] root 31 2 0 21:41 ? 00:00:00 [crypto] uid 4951 3327 0 23:15 pts/3 00:00:00 grep cry
The first step is to create a backup directory and encrypt all contents that are placed in the directory. ezNcrypt uses the concept of an @category for reference with an encrypted file or directory.
$ mkdir /mysql/backup/encrypted
$ sudo ezncrypt --encrypt @backup /mysql/backup/encrypted
ezncrypt | Checking system dependencies
| Verifying ezncrypt license
| getting information about location
| > path: /var/lib/ezncrypt/ezncrypted/backup
ezncrypt | Checking encryption status
| done!
keymgr | Retrieving key from KSS
| > Encryption password retrieved from KSS
| generating keys
| done!
backup | backing up data
| This can take a while. Please be patient
| > backing up /mysql/backup/encrypted
| > File: /opt/ezncrypt/backup/2012-04-27/encrypted.tar.gz
| done!
ezncrypt | encrypting files
| > checking disk space
| > encrypting /mysql/backup/encrypted
| done!
ezncrypt | congratulations. you have encrypted your Files!!
log | File: /var/log/ezncrypt/ezncrypt.log
The underlying regular directory is now replaced:
$ ls -l /mysql/backup total 0 lrwxrwxrwx 1 root root 59 2012-04-27 00:03 encrypted -> /var/lib/ezncrypt/ezncrypted/backup//mysql/backup/encrypted
Any attempts to write to this encrypted directory will now fail, even with the Linux super user:
$ mysqldump --all-databases > /mysql/backup/encrypted/edump1.sql -bash: /mysql/backup/encrypted/edump1.sql: Permission denied $ sudo mysqldump --all-databases > /mysql/backup/encrypted/edump1.sql -bash: /mysql/backup/encrypted/edump1.sql: Permission denied
In order to read and write from an encrypted directory you need to grant access controls to a given program, for example mysqldump:
$ sudo ezncrypt-access-control -a "ALLOW @backup * /usr/bin/mysqldump" passphrase: salt: Rule added
You verify the defined access control rules with:
$ sudo ezncrypt-access-control -L passphrase: salt: # - Type Category Path Process 1 ALLOW @backup * /usr/bin/mysqldump
However, writing with mysqldump still causes an error because it is the shell redirection that is performing the writing, as seen in the system error log:
$ mysqldump --all-databases > /mysql/backup/encrypted/edump1.sql -bash: /mysql/backup/encrypted/edump1.sql: Permission denied $ dmesg | tail [4138848.618559] ezncryptfs: DENIED type="acl" exec="/bin/bash" script="/dev/pts/4" comm="bash" path="/var/lib/ezncrypt/ezncrypted/backup" pid=7448 uid=1000
You can use the –result-file option with mysqldump to enable the process to create the file directly. For example:
$ time mysqldump --all-databases --result-file=/mysql/backup/encrypted/edump2.sql real 1m34.714s user 0m59.388s sys 0m9.589s $ sudo ezncrypt-run "ls -l /mysql/backup/encrypted/" passphrase: salt: total 3.0G -rw-rw-r-- 1 uid gid 2.9G 2012-04-27 02:43 edump2.sql
In this single test, the transparent encryption added only a very nominal overhead to the mysqldump test backup used. You can easily extract the file from the encrypted directory, however that would defeat the purpose of using encryption. The following syntax is shown just to confirm the validity of the encrypted file:
$ sudo /usr/sbin/ezncrypt-run "cp /mysql/backup/encrypted/edump2.sql ." passphrase: salt: $ ls –al edump* total 3916 -rw-r--r-- 1 uid gid 2.9G 2012-04-27 02:55 edump2.sql $ grep "^CREATE.*DATABASE" edump2.sql CREATE DATABASE /*!32312 IF NOT EXISTS*/ `book2` /*!40100 DEFAULT CHARACTER SET latin1 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `employees` ... CREATE DATABASE /*!32312 IF NOT EXISTS*/ `musicbrainz` ... CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` ... CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sakila` ... CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world_innodb` ... CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world_myisam` ...
When using correctly configured directories and access controls, the use is truly transparent to the backup process.
Restoring an encrypted file is a little more involved. The best approach is to create a script to perform the work, than encrypt this script. When executed, this script will have the permissions necessary to read and apply the encrypted file.
Perhaps the best tip about using this type of transparent encryption is that it is possible to encrypt the MySQL user and password securely in a plain text configuration file and used with appropriate MySQL client commands. This helps to address another common security problem.