MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
MySQL Server supports a keyring service that enables internal server components and plugins to securely store sensitive information for later retrieval.
As of MySQL 5.7.13, MySQL Server includes an SQL interface for
keyring key management, implemented as a set of general-purpose
user-defined functions (UDFs) that access the functions provided
by the internal keyring service. The keyring UDFs are contained
in a plugin library file, which also contains a
keyring_udf
plugin that must be enabled prior
to UDF invocation. For these UDFs to be used, a keyring plugin
such as keyring_file
or
keyring_okv
must be enabled.
The UDFs described here are general purpose and intended for use with any keyring plugin. A given keyring plugin might have UDFs of its own that are intended for use only with that plugin; see Section 6.4.4.9, “Plugin-Specific Keyring Key-Management Functions”.
The following sections provide installation instructions for the keyring UDFs and demonstrate how to use them. For information about the keyring service functions invoked by the UDFs, see Section 5.5.6.2, “The Keyring Service”. For general keyring information, see Section 6.4.4, “The MySQL Keyring”.
This section describes how to install or uninstall the keyring
user-defined functions (UDFs), which are implemented in a
plugin library file that also contains a
keyring_udf
plugin. For general information
about installing or uninstalling plugins and UDFs, see
Section 5.5.1, “Installing and Uninstalling Plugins”, and
Section 5.6.1, “Installing and Uninstalling User-Defined Functions”.
The keyring UDFs enable keyring key management operations, but
the keyring_udf
plugin must also be
installed because the UDFs do not work correctly without it.
Attempts to use the UDFs without the
keyring_udf
plugin result in an error.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, configure the plugin directory
location by setting the value of
plugin_dir
at server startup.
The plugin library file base name is
keyring_udf
. The file name suffix differs
per platform (for example, .so
for Unix
and Unix-like systems, .dll
for Windows).
To install the keyring_udf
plugin and the
UDFs, use the INSTALL PLUGIN
and CREATE FUNCTION
statements,
adjusting the .so
suffix for your
platform as necessary:
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';
If the plugin and UDFs are used on a source replication server, install them on all replicas as well to avoid replication issues.
Once installed as just described, the plugin and UDFs remain
installed until uninstalled. To remove them, use the
UNINSTALL PLUGIN
and
DROP FUNCTION
statements:
UNINSTALL PLUGIN keyring_udf; DROP FUNCTION keyring_key_generate; DROP FUNCTION keyring_key_fetch; DROP FUNCTION keyring_key_length_fetch; DROP FUNCTION keyring_key_type_fetch; DROP FUNCTION keyring_key_store; DROP FUNCTION keyring_key_remove;
Before using the keyring user-defined functions (UDFs), install them according to the instructions provided in Installing or Uninstalling General-Purpose Keyring Functions.
The keyring UDFs are subject to these constraints:
To use any keyring UDF, the keyring_udf
plugin must be enabled. Otherwise, an error occurs:
ERROR 1123 (HY000): Can't initialize function 'keyring_key_generate'; This function requires keyring_udf plugin which is not installed. Please install
To install the keyring_udf
plugin, see
Installing or Uninstalling General-Purpose Keyring Functions.
The keyring UDFs invoke keyring service functions (see
Section 5.5.6.2, “The Keyring Service”). The service functions
in turn use whatever keyring plugin is installed (for
example, keyring_file
or
keyring_okv
). Therefore, to use any
keyring UDF, some underlying keyring plugin must be
enabled. Otherwise, an error occurs:
ERROR 3188 (HY000): Function 'keyring_key_generate' failed because underlying keyring service returned an error. Please check if a keyring plugin is installed and that provided arguments are valid for the keyring you are using.
To install a keyring plugin, see Section 6.4.4.1, “Keyring Plugin Installation”.
To use any keyring UDF, a user must possess the global
EXECUTE
privilege.
Otherwise, an error occurs:
ERROR 1123 (HY000): Can't initialize function 'keyring_key_generate'; The user is not privileged to execute this function. User needs to have EXECUTE
To grant the global EXECUTE
privilege to a user, use this statement:
GRANT EXECUTE ON *.* TO user
;
Alternatively, should you prefer to avoid granting the
global EXECUTE
privilege
while still permitting users to access specific
key-management operations, “wrapper” stored
programs can be defined (a technique described later in
this section).
A key stored in the keyring by a given user can be
manipulated later only by the same user. That is, the
value of the CURRENT_USER()
function at the time of key manipulation must have the
same value as when the key was stored in the keyring.
(This constraint rules out the use of the keyring UDFs for
manipulation of instance-wide keys, such as those created
by InnoDB
to support tablespace
encryption.)
To enable multiple users to perform operations on the same key, “wrapper” stored programs can be defined (a technique described later in this section).
Keyring UDFs support the key types and lengths supported by the underlying keyring plugin. For information about keys specific to a particular keyring plugin, see Section 6.4.4.6, “Supported Keyring Key Types and Lengths”.
To create a new random key and store it in the keyring, call
keyring_key_generate()
, passing
to it an ID for the key, along with the key type (encryption
method) and its length in bytes. The following call creates a
2,048-bit DSA-encrypted key named MyKey
:
mysql> SELECT keyring_key_generate('MyKey', 'DSA', 256);
+-------------------------------------------+
| keyring_key_generate('MyKey', 'DSA', 256) |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
A return value of 1 indicates success. If the key cannot be
created, the return value is NULL
and an
error occurs. One reason this might be is that the underlying
keyring plugin does not support the specified combination of
key type and key length; see
Section 6.4.4.6, “Supported Keyring Key Types and Lengths”.
To be able to check the return type regardless of whether an
error occurs, use SELECT ... INTO
@
and test the
variable value:
var_name
mysql>SELECT keyring_key_generate('', '', -1) INTO @x;
ERROR 3188 (HY000): Function 'keyring_key_generate' failed because underlying keyring service returned an error. Please check if a keyring plugin is installed and that provided arguments are valid for the keyring you are using. mysql>SELECT @x;
+------+ | @x | +------+ | NULL | +------+ mysql>SELECT keyring_key_generate('x', 'AES', 16) INTO @x;
mysql>SELECT @x;
+------+ | @x | +------+ | 1 | +------+
This technique also applies to other keyring UDFs that for failure return a value and an error.
The ID passed to
keyring_key_generate()
provides
a means by which to refer to the key in subsequent UDF calls.
For example, use the key ID to retrieve its type as a string
or its length in bytes as an integer:
mysql>SELECT keyring_key_type_fetch('MyKey');
+---------------------------------+ | keyring_key_type_fetch('MyKey') | +---------------------------------+ | DSA | +---------------------------------+ mysql>SELECT keyring_key_length_fetch('MyKey');
+-----------------------------------+ | keyring_key_length_fetch('MyKey') | +-----------------------------------+ | 256 | +-----------------------------------+
To retrieve a key value, pass the key ID to
keyring_key_fetch()
. The
following example uses HEX()
to
display the key value because it may contain nonprintable
characters. The example also uses a short key for brevity, but
be aware that longer keys provide better security:
mysql>SELECT keyring_key_generate('MyShortKey', 'DSA', 8);
+----------------------------------------------+ | keyring_key_generate('MyShortKey', 'DSA', 8) | +----------------------------------------------+ | 1 | +----------------------------------------------+ mysql>SELECT HEX(keyring_key_fetch('MyShortKey'));
+--------------------------------------+ | HEX(keyring_key_fetch('MyShortKey')) | +--------------------------------------+ | 1DB3B0FC3328A24C | +--------------------------------------+
Keyring UDFs treat key IDs, types, and values as binary
strings, so comparisons are case-sensitive. For example, IDs
of MyKey
and mykey
refer
to different keys.
To remove a key, pass the key ID to
keyring_key_remove()
:
mysql> SELECT keyring_key_remove('MyKey');
+-----------------------------+
| keyring_key_remove('MyKey') |
+-----------------------------+
| 1 |
+-----------------------------+
To obfuscate and store a key that you provide, pass the key
ID, type, and value to
keyring_key_store()
:
mysql> SELECT keyring_key_store('AES_key', 'AES', 'Secret string');
+------------------------------------------------------+
| keyring_key_store('AES_key', 'AES', 'Secret string') |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
As indicated previously, a user must have the global
EXECUTE
privilege to call
keyring UDFs, and the user who stores a key in the keyring
initially must be the same user who performs subsequent
operations on the key later, as determined from the
CURRENT_USER()
value in effect
for each UDF call. To permit key operations to users who do
not have the global EXECUTE
privilege or who may not be the key “owner,” use
this technique:
Define “wrapper” stored programs that
encapsulate the required key operations and have a
DEFINER
value equal to the key owner.
Grant the EXECUTE
privilege
for specific stored programs to the individual users who
should be able to invoke them.
If the operations implemented by the wrapper stored
programs do not include key creation, create any necessary
keys in advance, using the account named as the
DEFINER
in the stored program
definitions.
This technique enables keys to be shared among users and provides to DBAs more fine-grained control over who can do what with keys, without having to grant global privileges.
The following example shows how to set up a shared key named
SharedKey
that is owned by the DBA, and a
get_shared_key()
stored function that
provides access to the current key value. The value can be
retrieved by any user with the
EXECUTE
privilege for that
function, which is created in the
key_schema
schema.
From a MySQL administrative account
('root'@'localhost'
in this example),
create the administrative schema and the stored function to
access the key:
mysql>CREATE SCHEMA key_schema;
mysql>CREATE DEFINER = 'root'@'localhost'
FUNCTION key_schema.get_shared_key()
RETURNS BLOB READS SQL DATA
RETURN keyring_key_fetch('SharedKey');
From the administrative account, ensure that the shared key exists:
mysql> SELECT keyring_key_generate('SharedKey', 'DSA', 8);
+---------------------------------------------+
| keyring_key_generate('SharedKey', 'DSA', 8) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
From the administrative account, create an ordinary user account to which key access is to be granted:
mysql>CREATE USER 'key_user'@'localhost'
IDENTIFIED BY 'key_user_pwd';
From the key_user
account, verify that,
without the proper EXECUTE
privilege, the new account cannot access the shared key:
mysql> SELECT HEX(key_schema.get_shared_key());
ERROR 1370 (42000): execute command denied to user 'key_user'@'localhost'
for routine 'key_schema.get_shared_key'
From the administrative account, grant
EXECUTE
to
key_user
for the stored function:
mysql>GRANT EXECUTE ON FUNCTION key_schema.get_shared_key
TO 'key_user'@'localhost';
From the key_user
account, verify that the
key is now accessible:
mysql> SELECT HEX(key_schema.get_shared_key());
+----------------------------------+
| HEX(key_schema.get_shared_key()) |
+----------------------------------+
| 9BAFB9E75CEEB013 |
+----------------------------------+
For each general-purpose keyring user-defined function (UDF), this section describes its purpose, calling sequence, and return value. For information about the conditions under which these UDFs can be invoked, see Using General-Purpose Keyring Functions.
Given a key ID, deobfuscates and returns the key value.
Arguments:
key_id
: A string that
specifies the key ID.
Return value:
Returns the key value as a string for success,
NULL
if the key does not exist, or
NULL
and an error for failure.
Key values retrieved using
keyring_key_fetch()
are
subject to the general keyring UDF limits described in
Section 6.4.4.6, “Supported Keyring Key Types and Lengths”. A key value longer
than that length can be stored using a keyring service
function (see Section 5.5.6.2, “The Keyring Service”), but if
retrieved using
keyring_key_fetch()
is
truncated to the general keyring UDF limit.
Example:
mysql>SELECT keyring_key_generate('RSA_key', 'RSA', 16);
+--------------------------------------------+ | keyring_key_generate('RSA_key', 'RSA', 16) | +--------------------------------------------+ | 1 | +--------------------------------------------+ mysql>SELECT HEX(keyring_key_fetch('RSA_key'));
+-----------------------------------+ | HEX(keyring_key_fetch('RSA_key')) | +-----------------------------------+ | 91C2253B696064D3556984B6630F891A | +-----------------------------------+ mysql>SELECT keyring_key_type_fetch('RSA_key');
+-----------------------------------+ | keyring_key_type_fetch('RSA_key') | +-----------------------------------+ | RSA | +-----------------------------------+ mysql>SELECT keyring_key_length_fetch('RSA_key');
+-------------------------------------+ | keyring_key_length_fetch('RSA_key') | +-------------------------------------+ | 16 | +-------------------------------------+
The example uses HEX()
to
display the key value because it may contain nonprintable
characters. The example also uses a short key for brevity,
but be aware that longer keys provide better security.
keyring_key_generate(
key_id
,
key_type
,
key_length
)
Generates a new random key with a given ID, type, and length, and stores it in the keyring. The type and length values must be consistent with the values supported by the underlying keyring plugin. See Section 6.4.4.6, “Supported Keyring Key Types and Lengths”.
Arguments:
key_id
: A string that
specifies the key ID.
key_type
: A string that
specifies the key type.
key_length
: An integer that
specifies the key length in bytes.
Return value:
Returns 1 for success, or NULL
and an
error for failure.
Example:
mysql> SELECT keyring_key_generate('RSA_key', 'RSA', 384);
+---------------------------------------------+
| keyring_key_generate('RSA_key', 'RSA', 384) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
keyring_key_length_fetch(
key_id
)
Given a key ID, returns the key length.
Arguments:
key_id
: A string that
specifies the key ID.
Return value:
Returns the key length in bytes as an integer for success,
NULL
if the key does not exist, or
NULL
and an error for failure.
Example:
See the description of
keyring_key_fetch()
.
Removes the key with a given ID from the keyring.
Arguments:
key_id
: A string that
specifies the key ID.
Return value:
Returns 1 for success, or NULL
for
failure.
Example:
mysql> SELECT keyring_key_remove('AES_key');
+-------------------------------+
| keyring_key_remove('AES_key') |
+-------------------------------+
| 1 |
+-------------------------------+
keyring_key_store(
key_id
,
key_type
,
key
)
Obfuscates and stores a key in the keyring.
Arguments:
key_id
: A string that
specifies the key ID.
key_type
: A string that
specifies the key type.
key
: A string that
specifies the key value.
Return value:
Returns 1 for success, or NULL
and an
error for failure.
Example:
mysql> SELECT keyring_key_store('new key', 'DSA', 'My key value');
+-----------------------------------------------------+
| keyring_key_store('new key', 'DSA', 'My key value') |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+
keyring_key_type_fetch(
key_id
)
Given a key ID, returns the key type.
Arguments:
key_id
: A string that
specifies the key ID.
Return value:
Returns the key type as a string for success,
NULL
if the key does not exist, or
NULL
and an error for failure.
Example:
See the description of
keyring_key_fetch()
.