MySQL has been widely used across the different platforms to build scalable and high-performance applications among various industries.

Encryption as a Security Essential

In the current digital age, security has been a crucial pillar for any kind of data including MySQL. MySQL stores a lot of sensitive and valuable data like passwords, user personal information, financial data and much more.

“Encryption is the key for data security in MySQL.” It is one of the most trusted and widely adopted cybersecurity practices today. An unreadable format of the given data will be worked-in as a safeguard for any kind of data, and this can protect it from unauthorized access.

MySQL Encryption Best Practices

1. Data-at-Rest Encryption
   • Tablespace Encryption – Encrypts individual InnoDB tables
   • Transparent Data Encryption (TDE) – Automatic full-table encryption (Enterprise Edition)
   • Encrypted Binary Logs /Undo- Redo Logs – secures replication and log data
2. Data-in-Transit Encryption
   • SSL/TLS Encryption – Ensures data security and integrity in transit
   • Uses SSL/TLS protocols to encrypt traffic
3. Column-Level Encryption
   • AES_ENCRYPT () / AES_DECRYPT () – Built-in functions for encrypting/decrypting column data
   • Manually encrypts sensitive fields of the table

Encryption Using MySQL Keyring Component

MySQL data encryption can be achieved using the Keyring plugin or components.

Keyring supports the storage of confidential data securely using a Keyring plugin and component.
Keyring components and plugins both manage the Keyring data but are configured differently and may have operational differences.

The Need for the component_keyring_file (File-Based Keyring)

1. The Keyring component loading uses a manifest.
2. For Keyring components, every component has its separate configuration file. The configuration is not done using system variables.
3. A file-based storage means that the keys are kept separate from the data in a database, by adding more security levels against any unauthorized access to the database.
4. Many industry standards (such as PCI-DSS, GDPR, HIPAA) require secure key management practices for data encryption. The file-based Keyring helps meet these requirements by ensuring that the encryption keys are securely stored and isolated from the actual data.
5. Easy key rotation and management are possible.
6. The performance is better for small to medium environments. It is future-proof and consistent with newer MySQL features.

Note: Supported by MySQL Community Edition and MySQL Enterprise Edition.

Getting Started with the component_keyring_file

• On initialization of the components, the component_keyring_file reads either a global configuration file, or a local configuration file which is directed (referenced) in the global configuration file.
• If the global configuration file indicates the use of a local configuration file, the component reads its local configuration file from the data directory.
• With a local configuration, each MySQL instance can use the component_keyring_file with a distinct keyring data file, enabling isolated and secure encryption key handling across environments.

To start with the component_keyring_file for keystore management, one must need to set up the following:

1. A manifest file (to indicate to the server to load the component_keyring_file)
2. A configuration file

Manifest Files:

Name: mysqld.my

Format: Valid Json

File Permission: Read Only
(Care is needed in checking the settings of this permission file otherwise the server creates a warning in the error logs making this file read-only)

The server attempts to read its global manifest file from the directory where the server is installed. E.g., C:\Program Files\MySQL\MySQL Server 8.0\bin

If the global manifest configuration is done to use a local configuration file, the server reads its local manifest file from the data directory.

File Parameters:

“read_local_manifest”:

This parameter is allowed only in the global manifest file. If this parameter is not added in the global manifest file, it will be considered to use the local configuration only. If this parameter exists in the global manifest file, the value of this parameter whether it is true or false, would be checked to consider the global or local manifest files for the set-up.

If the value is true, the server ignores the other items in the global manifest file and reads the local manifest file. The server processes this with other items in the global manifest file and does not use the local manifest file if the value is false.

“components”:

This parameter determines which component should be loaded. The string parameter value indicates a valid component URN, like ”file://component_keyring_file”. A component URN starts with file://.

It represents the base name of the library file located in the MySQL plugin directory which implements the component.

Configuration Files:

Name: component_keyring_file.cnf

Format: Valid Json

The component reads its global configuration file from the path where the component_keyring_file library file is installed.

E.g., C:\Program Files\MySQL\MySQL Server 8.0\lib\plugin

If the global configuration file indicates use of a local configuration file, the component reads its local configuration file from the data directory.

File Parameters:

“read_local_config”:

This parameter is allowed in the global configuration file only. If the parameter is not present, the component considers the global configuration only. If this parameter exists in the global manifest file, the value of the parameter being either true or false would be considered to select either the global or local manifest files for the set-up.

If the value is true, the component does not use the other items in the global configuration file and reads the local configuration file. The component processes the other items in the global manifest file and does not use the local manifest file if the value is false.

“path”:
The parameter value is a string which indicates the keyring file path. The file path should be an absolute path, not a relative path. This is a mandatory parameter in the configuration. The component_keyring_file initialization fails otherwise.

This path must not point to or include the MySQL data directory. The path should be fully accessed (with read/write permission) by the system MySQL user. The component_keyring_file is then generated in that location with an encryption master key.

“read_only”:

This parameter value decides whether the keyring data file is read only. If the value of the item is true, it indicates read only, whereas if it is false, it indicates read/write. This is a mandatory parameter in the configuration. The component_keyring_file initialization fails otherwise.

Step By Step Guide for Local Configuration

STEP 1

To set-up a local configuration, create the following four files in locations mentioned:

Global Manifest File

Local Manifest File

Global Configuration File

Local Configuration File

STEP 2

After performing the component-specific configuration, start the server. One can verify the component installation using the query given below.

Here the component status should be marked ‘Active’ for a successful initialization.

STEP 3

Update the My.Ini file with the following additional parameters to enable encryption and restart the server.

Expected Result: The server should restart without fail.

default_table_encryption =ON

STEP 4

Encrypt existing table space if non-encrypted

If one wants to switch to an encrypted database from an already existent non-encrypted database, one can perform the following query to encrypt the existing InnoDb tablespaces manually from the code.

Security and Maintenance

To protect, manage, and secure an encryption key in hardware security modules (HSMs) or key vaults, security standards like PCI, FIPS, and others need the use of a key management system.

Key rotation is suggested for securely maintaining keys. Rotation can be done using HashiCorp Vault or AWS KMS, which support an automatic key rotation. They are highly secure tools.

Warning: If the data is encrypted in a format and the keyring file is lost, there might be permanent data loss.

• A Keyring file should be restricted to MySQL users only
• The backup of the Keyring file should be done regularly
• Keys should be rotated using advanced tools or manually
• Logs related to Keyring actions should be managed so that the changes can be traced

Practical Implications

• Financial services applications
• E commerce platform applications
• Healthcare applications
• Government and public sector systems
• Legal and insurance firm systems
• Educational systems

Conclusion

The MySQL Keyring component is an excellent choice to provide MySQL Data security by following MySQL encryption best practices. MySQL Keyring components provide a centralized solution to maintain data integrity and security for administration, with less effort to maintain encrypted data. It is also managed within the industry standards to secure against any data breaches, unauthenticated access, and compliance failures.

References

https://dev.mysql.com/doc/refman/8.4/en/keyring-component-plugin-comparison.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html 
https://dev.mysql.com/doc/refman/8.0/en/keyring-file-component.html 
https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-encrypted-innodb.html