Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
HTML Download (TGZ) - 315.8Kb
HTML Download (Zip) - 324.5Kb


Security in MySQL  /  ...  /  Using General-Purpose Keyring Functions

7.4.8.2 Using General-Purpose Keyring Functions

Before using the keyring user-defined functions (UDFs), install them according to the instructions provided in Section 7.4.8.1, “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 Section 7.4.8.1, “Installing or Uninstalling General-Purpose Keyring Functions”.

  • The keyring UDFs invoke keyring service functions (see 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 7.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, with the additional constraint that keys cannot be longer than 2,048 bytes (16,384 bits), due to limitations of the UDF interface. See Section 7.4.7, “Supported Keyring Key Types”.

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 7.4.7, “Supported Keyring Key Types”.

To be able to check the return type regardless of whether an error occurs, use SELECT ... INTO @var_name and test the variable value:

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:

  1. Define wrapper stored programs that encapsulate the required key operations and have a DEFINER value equal to the key owner.

  2. Grant the EXECUTE privilege for specific stored programs to the individual users who should be able to invoke them.

  3. 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                 |
+----------------------------------+

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.