MariaDB - Rotate Encryption Keys

Summary

The purpose of this project is to provide a method to rotate all encryption keys used by MariaDB's File Key Management Plugin for every encrypted table. The provided MariaDB SQL script, rotateEncKeysSP.sql will create a stored procedure named rotateEncKeys in a database of your choosing. While this procedure can be added to any database, the user running it needs to have rights to alter every database's encrypted tables.

A temporary table named tmpEncKeyLog will be created by the stored procedure to allow the logging of the key rotation to a CSV file. This table will then be dropped upon the procdure's completion.

The stored procedure can also be automated via Event Scheduler to run on a regular schedule without user interaction. See the Automating Stored Procedure section below for more info.

This method has been tested against MariaDB 10.3, but should work as far back as version 10.1.4.

Prerequisites

You need to have MariaDB's Data at Rest Encryption already setup using the default encryption plugin, File Key Management Plugin. You also need to have created multiple encryption keys for use with this plugin or the same key will be used over and over.

If you need help with the setup, you can follow this guide on my tech blog, Labsrc.com - Please Encrypt Your Databases.

Installation

You'll need to specify a database you would like to add the stored procedure to and then run the SQL script, rotateEncKeysSP.sql, to create it. The created stored procedure will run against all databases with encrypted tables regardless of the database it resides in.

Install Command

mysql -u username -p databasename < rotateEncKeysSP.sql

Calling the Stored Procedure

The stored procedure can be called within the MariaDB console by running the following command while using the previously chosen database.

use databasename; 
call rotateEncKeys(KeyID,'Log Directory');

Stored Procedure Parameters

Parameter 1: Encryption Key ID

• All tables will rotate to the specified encryption Key ID
• If specified key doesn't exist, all tables will rollover to Key ID 1
• If Key ID 0 is used, all tables will increment their current Key ID by one. If incremented Key ID does not exist, tables will rollover to Key ID 1.

Parameter 2: Log Directory

• Log file will be saved as hostname_encKeyLog_CurrentDate_CurrentTime.csv_
• If '' is used, log file will be saved to MariaDB's datadir (Default for Ubuntu is "/var/lib/mysql/")
• Directory must allow write access to the user MariaDB runs as
• MariaDB will not allow output to "Home Directories" by default

Example Stored Procedure Usage

Example 1 - Incrementing All Encryption Keys

call rotateEncKeys(0,'');

This command will increment all encryption Key ID's by one and will output the log to your instance's default datadir. The default datadir for Ubuntu is "/var/lib/mysql". If the incremented Key ID doesn't exist, the table will rollover to Key ID 1.

Example 2 - Changing All Tables to the Same Key ID and Specifying Log Directory

call rotateEncKeys(2,'/tmp');

This command will change all encryption keys to Key ID 2 and will output the log file to the /tmp directory.

Automating Stored Procedure

To automate the stored procedure and rotate your encryption keys on a schedule, you'll need to first enable Event Scheduler in MariaDB.

Enable Event Scheduler

Edit your MariaDB config file normally found in /etc/mysql/mariadb.cnf

sudo nano /etc/mysql/mariadb.cnf

Add the following under the [mysqld]

[mysqld]
event_scheduler = ON

Save the config file, Restart MariaDB and event scheduler should now be running.

Create Scheduled Event

Now create a new event in the same database you added the stored procedure to. Log into the MariaDB console and run the following code. You can change the time, start date and frequency to your liking.

## Must use the same database the "rotateEncKeys" stored procedure was created in
use database;

## Create Schedule Event
CREATE EVENT rotateEncKeysEvent
   ## Following schedule runs once a week on Sunday at 1:00AM
   ON SCHEDULE EVERY 1 WEEK STARTS '2019-01-27 01:00:00'
   ON COMPLETION PRESERVE
   DO 
      call rotateEncKeys(0,'');

Alternatively, you can use the provided SQL script, EXAMPLE-rotateEncKeysEvent.sql which will create the event for you. Just remember to edit the schedule and call before executing it.

mysql -u username -p databasename < EXAMPLE-rotateEncKeysEvent.sql

Check Event Status

You can check the status of your event by running the following.

SHOW EVENTS\G;

Closing Comments

I created this script in my free time as MariaDB does not currently provide this feature with their encryption plugin. I may improve or alter this script in the future so check back in when you have time. If you find any issues or have ways to improve this project in any way, feel free to post.