Since OpenEMR backup consists of a MySQL dump of all tables, we want to encrypt the data contained in the backup.
Does anyone have settings for this?
We think it will be good idea to make encryption a standard feature for the backup script.
Use PGP or Truecrypt. Mount a Truecrypt volume, and run a normal backup using that volume as the drive. Dismount the volume, and poof…it is gone. You could run the emr (or whole operating system) inside a truecrypt file container. If someone shuts down the machine, the data is totally unreadable until someone with the password and/or keyfile (and who knows that the data is even on the drive) mounts the drive.
#!/bin/bash
##################################################################
# sqlbackup.sh:
# rolling sqlbackups using mysqldump
#
#
# This script will dump mysql databases to a specified directory
# with optional encrytion.
#
#
###################################################################
# program options
backup_dir=/backup
backup_age=28
use_encryption=0
use_bzip=0
nice_level=19
mysql_user=root
mysql_passfile=/etc/keys/.mysqlpass
openssl_passfile=/etc/keys/.opensslpass
date=`date +%Y%m%d`
exten=".sql"
logfile=/var/log/mysqlbackup.log
mysqldump_options=" --all-databases --single-transaction --quote-names "
# program paths
mysqldump=/usr/bin/mysqldump
openssl=/usr/bin/openssl
bzip2=/usr/bin/bzip2
# usage summary
function usage {
echo "
sqlbackup.sh:
creates backups of sql databases using the mysqldump command,
including optional encryption, bzip2 compression, and
variable backup aging
Usage: sqlbackup.sh [OPTIONS]
Options:
-h : display this help
-b <dir> : select directory to store backups. This directory
will not be created.
-e : enable encryption using openssl's des3 encryption.
-k <file> : specify file containing password to be used for encryption
This defaults to /etc/keys/.opensslpass
-u <user> : username to use for connecting to MySQL
-p </user></file><file> : specifies a file containing a password to be used for
connecting to MySQL
-z : enable bzip2 compression of backups
-a <days> : backup age in days. Backups older than this will be deleted
-n <level> : Specifies a nice level. This level will apply to all operations of the
backup. Defaults to 19 (very nice)
-l </level></days></file><file> : logfile path. Alternate path to a file for logging. Defaults
to '/var/log/mysqlbackup.log'
"
}
# echo a string to screen and log
function echo_and_log {
logstring="[ `date` ] : $*"
echo $logstring
echo $logstring >> $logfile
}
# print error to screen and exit
function exit_with_error {
echo_and_log "Error: $*"
echo_and_log "Exiting with code 1"
exit 1
}
# funtion to delete old backups
function clean_backups {
find $backup_dir -type f -mtime +${backup_age} -exec rm -f {} \;
[ "$?" == "0" ] || echo_and_log "Error encountered when cleaning old backups from $backup_dir"
}
# process command line options
while getopts ":hb:ek:u:p:za:n:l:" opt
do
case $opt in
b) backup_dir=$OPTARG ;;
e) use_encryption=1 ;;
k) openssl_passfile=$OPTARG ;;
u) mysql_user=$OPTARG ;;
p) mysql_passfile=$OPTARG ;;
z) use_bzip=1 ;;
a) backup_age=$OPTARG ;;
n) nice_level=$OPTARG ;;
l) logfile=$OPTARG ;;
h) usage
exit 0 ;;
\?) echo "unknown option"
usage
exit 1 ;;
*) usage
exit 1 ;;
esac
done
# set umask
umask 0077
# start processing
echo_and_log "Starting MySQL backup: encryption = $use_encryption, bzip = $use_bzip, backup directory = $backup_dir"
# error checking and validation
[ -d $backup_dir ] || exit_with_error "Backup directory '$backup_dir' does not exist"
[ -n "$logfile" ] || exit_with_error "No log file specified."
[ -f $mysql_passfile ] || exit_with_error "MySQL password file '$mysql_passfile' does not exist"
[ -x $mysqldump ] || exit_with_error "MySQLDump program at '$mysqldump' does not exist or is not executable"
# validate compression options
if [ "$use_bzip" == "1" ]
then
[ -x $bzip2 ] || exit_with_error "Bzip2 program at '$bzip2' does not exist or is not executable"
bzip2_command=" | nice -n $nice_level $bzip2 "
exten="${exten}.bz2"
fi
# validate encryption options
if [ "$use_encryption" == "1" ]
then
[ -x $openssl ] || exit_with_error "OpenSSL program at '$openssl' does not exist or is not executable"
[ -f $openssl_passfile ] || exit_with_error "OpenSSL password file '$openssl_passfile' does not exist"
openssl_command=" | nice -n $nice_level $openssl des3 -salt -pass file:${openssl_passfile} "
exten="${exten}.cryp"
fi
# call funtion to clean backup directory
clean_backups
# execute dump command
command="$mysqldump $mysqldump_options --user=${mysql_user} --password=$(<$mysql_passfile) $bzip2_command $openssl_command > $backup_dir/mysqlbackup_${date}${exten}"
eval $command
[ "$?" == "0" ] || exit_with_error "MySQLDump command returned non-zero error code. Backup not completed successfully"
# finish
echo_and_log "Backup complete"
exit 0
</file></dir>
The trouble with encrypting the backups as it comes out of the server is that no OS really uses the same encryption. Thus, if the server uses openssl to encrypt the backup, but the end user has a windows system, it won’t matter unless that user knows how to use openssl. I don’t know how to use openssl, and there is a lot of conflicting information regarding how to encrypt/decrypt data. Thus, there is an excellent chance that the end user will have a file of useless data: the worst kind of backup failure.
If https is being used, then the actual data transfer encrypts the data on the fly, and is decrypted at the customer’s system, so you don’t need to worry about any packet sniffing attempts. If you are using ssl only within a private network or vpn, you shouldn’t even need to purchase an ssl certificate: the certificate is only used to identify that the server is legitimate and not used to encrypt. If you are not using https for your web connections, I recommend it strongly, and there are ways to keep it cheap. I am considering writing a document on securing apache.
It seems to me the more important issue here is what to do with your data backups once you have them. You need to guard your data not only from theft, but also from disasters such as fire, earthquake, or even a benign user deleting files that don’t appear to have a purpose. You absolutely must store that data somewhere where it will be safe, preferably in more than one location away from where you use it. If you encrypt it, you must keep the key somewhere safe, in both electronic and hard-copy: none of the backups will matter if you lose the key. For example: if you follow truecrypt’s instructions and create a passphrase that truly is strong, no one will be able to decrypt it for you.
Other than being a (I like to think mindful) rant, I hope this helps!
Oh, I really like aethelwulffe’s comments about truecrypt. I have considered using truecrypt on an amazon S3 mounted volume. I don’t know how hipaa compliant it would be, but by the time the data lands on amazon’s disks, it’s form is useless to anyone without the passphrase key.