Summary
Security is a primary responsibility of a database administrator, or better yet a data administrator as really is always about the data. As part of making sure data is secure database level encryption is recognized way of helping secure data. Databases has ways of encrypting data stored as well as data in transit here will will cover database encryption for the data stored.
Transparent Data Encryption also known as innodb tablespace encryption provides data at
rest encryption for MySQL databases. The
basic encryption available for all editions of mysql is the key ring file
plugin. The other options available are
with the MySQL Enterprise Edition and are using a key management solution such
as Oracle Key Vault (OKV), Gemalto KeySecure, Thales Vormetric Key Management
Server, Fornetix Key Orchestration or using https base APIs for Hashicorp Vault
or AWS KMS.
MySQL Encryption algorithm is an AES only with a block
encryption mode.
Keep in mind with MySQL encryption that undo log and
binary logs are not encrypted.
You cannot encrypt or decrypt a table with an INPLACE
algorithm it will always be a COPY algorithm.
Requirements
1.
MySQL 5.7.11 and higher only for key ring
encryption
2.
MySQL 5.7.13 and higher is recommended as it includes a set of user
defined functions for key ring management.
3.
Innodb file_per_table set for the mysql database
must be used
Setup the Encryption key ring file
1.
Create the directory location for the key-ring
plugin key file, set permissions so that only the mysql os user that runs the
database can access the directory.
cd /opt/mysql
mkdir tde
chown tde
chmod 700 tde
** Make sure operating system backups backup all mysql
binaries as well as the tde directory so key is backed up and is not lost in
the event of disk failure.
2.
Edit the my.cnf to setup the early-plugin-load
option and add the key ring file plugin, recommended placing key file in a
separate directory from data files.
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/opt/mysql/tde/keyring
3.
Restart the mysql database
/etc/rc.d/init.d/mysql.server
stop
/etc/rc.d/init.d/mysql.server
start
4.
Check if the plugin is now active
SELECT
PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE
PLUGIN_NAME LIKE 'keyring%';
show
plugins ;
5.
If 5.7.13 or higher install the user defined
functions for key management from mysql command line interface as a mysql SUPER
user.
mysql -u root -p
INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_generate RETURNS INTEGER
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME
'keyring_udf.so';
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME
'keyring_udf.so';
CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME
'keyring_udf.so';
Using Encryption
1.
Create a table with encryption
CREATE
TABLE t1 (c1 INT) ENCRYPTION='Y';
2.
Alter/Change an existing table to be encrypted
ALTER TABLE t1 ENCRYPTION='Y';
3. Alter/Change an Encrypted table to Non-Encrypted
ALTER TABLE t1 ENCRYPTION='N';
4.
Identify
tables using encryption
SELECT
TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE
CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
5.
Rotate the master key for an instance
ALTER
INSTANCE ROTATE INNODB MASTER KEY;
