install mysql keyring plugin

Question

I was trying to install keyring plugin on MySQL 5.7.18.

I edited my.ini file. It was blank so I added the following text and restarted MySQL.

[mysqld]
early-plugin-load=keyring_file.dll

I ran the following command to install the plugin.

mysql> install plugin keyring_file soname 'keyring_file.dll';

I then ran the following sql to check:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

But when I tried to alter a table to use encryption, I got errors.

mysql> ALTER TABLE t1 ENCRYPTION='Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring 
plugin is loaded.

Did I miss a step somewhere?

Solution

Harriett, do the following:

• Check the user that the MySQL service runs as (e.g., NETWORK SERVICE)
• Create a keyring folder in C:/Program Files/MySQL/MySQL Server 5.7
• Explicitly grant the MySQL service user permissions on the keyring folder

By default on Windows when using keyring_file, the keyring file is stored in C:/Program Files/MySQL/MySQL Server 5.7/keyring/keyring (I determined this by running SHOW VARIABLES LIKE 'keyring%' after installing the plugin and confirming its loading as you described).

After creating the keyring folder in C:/Program Files/MySQL/MySQL Server 5.7 right-click, then Properties -> Security, then Edit -> Add etc. Once the user is added check "Modify" in addition to Read & execute, List folder contents, Read and Write.

Then restart the MySQL service and you should be able to create an encrypted table without error.

NOTE: For security reasons you should go back and remove all users/groups you don't think will absolutely need to have access to the keyring folder (e.g., local machine users). On Unix the docs recommend that the mysql user and group alone have access to the folder.