Encrypting Your MySQL: A How-to Guide

Data is valuable, and its importance is tremendous. You have to protect your data and, naturally, databases. The most essential thing is that you can't rely on one or two solutions. Data security requires a complex approach where each aspect is vital. Encryption is one of them.

Speaking of MySQL encryption, we deal with two scenarios: encryption of data stored in MySQL databases, and encryption of connections to MySQL servers. Each scenario involves specific methods and means.

Let us talk about the methods of encryption in MySQL databases.

Data encryption basics: symmetric and asymmetric encryption

Encryption is the process of hiding information from unauthorized access by ciphering it. The cipher is an algorithm that determines both the encryption and decryption processes.

The central element of encryption is a key. It is a piece of information defining how to encrypt and decrypt data. The key can be single, or there can be a combination of several keys, it depends on the chosen encryption type: symmetric or asymmetric.

Symmetric encryption

Symmetric encryption is an algorithm based on a single key for both encryption and decryption. The decryption of data requires a password.

Symmetric encryption algorithms are widely recognized for their strength and speed. File, device, and computer encryption relies on symmetric encryption. Database encryption also uses it.

Asymmetric encryption

Asymmetric encryption is an algorithm based on a pair of keys: a public key and a private key. A public key encrypts the message we send, and the private key decrypts that message and also signs the document to authenticate the sender.

A private key should be secured and never shared (public keys can be shared or even published). As private keys are physical files stored on the device, it is recommended to protect them with a password and encrypt the device itself.

Data-at-rest encryption

Data-at-rest encryption is the expected type of encryption used for the data held in MySQL databases, as it is supported by the default MySQL storage engine InnoDB. Data-at-rest encryption applies to general tablespaces, file-per-table tablespaces, MySQL system tablespaces, and redo and undo logs.

A two-tier encryption key architecture consists of the master encryption key and tablespace keys. When the tablespace is encrypted, the tablespace key is encrypted too. It is stored in the header of that tablespace and used by MySQL when an authorized user or an application accesses the encrypted tablespace data. Then MySQL applies the master encryption key to decrypt the tablespace key and, consequently, the tablespace data.

Note: The decrypted tablespace key is unchangeable, but it is possible to change the master key (the master key rotation). To manage the master encryption key, MySQL uses the keyring plugin provided for all MySQL editions and stored on the server's host in a local file.

Now, let us review the specific methods used in MySQL for Data-at-rest encryption.

AES Encryption

Advanced Encryption Standard (AES) is the default encryption algorithm for MySQL and MariaDB, used for data protection. it is a symmetric encryption algorithm utilizing the same key (password) for encryption and decryption operations. The default key length is 128 bits. It can be 192 or 256 bits.

At the first stage of the encryption procedure, the AES algorithm takes a single key. Further, that key is expanded to multiple keys for individual rounds, where the number of rounds depends on the key length: 10 rounds for a 128-bit key, 12 rounds for a 192-bit key, and 14 rounds for a 256-bit key.

Note: This algorithm deals with byte data (not bit data). The 128-bit block size is treated as a 16-byte block.

AES is used for both encryption and decryption operations, utilizing the functions below to encode or decode data in databases.

Encryption:

AES_ENCRYPT('string', 'key')

Decryption:

AES_DECRYPT('string', 'key')

string is the data you want to encrypt (plain text, numeric, or binary).
key is the key used for encryption and then decryption (the password).

If any of the parameters contain NULL values, the output is NULL.

Let us explore an example where we cipher and then decipher the 'phone' column of the sakila MySQL sample database using AES_ENCRYPT() and AES_DECRYPT().

First, add a new encrypted_phone column for ciphered phone numbers:

ALTER TABLE address ADD COLUMN encrypted_phone VARBINARY(128);

Apply the AES_ENCRYPT function to the 'phone' field to encrypt it ('12345678' is an example of a password here, use a strong one in work).

UPDATE address SET encrypted_phone = AES_ENCRYPT(phone,'12345678');
SELECT * FROM address AS a;

To showcase the work scenarios, we utilize dbForge Studio for MySQL, a comprehensive IDE designed for MySQL and MariaDB. The functionality of this solution offers extensive coding assistance features (auto-completion, syntax validation, code formatting, snippets, etc.) and other efficient tools for performing all database-related tasks.

Decipher that previously encrypted field with the AES_DECRYPT function and the password set in the previous case. To make sure that the decryption process is correct, add the 'phone' field:

SELECT address,phone, 
AES_DECRYPT(encrypted_phone, '12345678') AS decrypted_phone
FROM address;

AES encryption is the standard method used in MySQL and other major database providers.

Previously, they used DES (Data Encryption Standard) with a 56-bit key, but that standard was significantly less secure. As a result, DES is no longer considered appropriate.

DES was deprecated in MySQL 5.7, and completely removed in MySQL 8.0. In MariaDB, it was deprecated in MariaDB 10.0, and the next MariaDB release will not feature DES at all.

SHA2 hashing

Hashing is a transformation of some information piece into a shorter value of a fixed length which is key to the original message. Unlike encryption, which is a process of two-way data conversion, where you can convert the data into an unreadable value and then convert it back to the original information, hashing is one-way data processing (it is impossible to transform it into the original data).

The most common use cases for hashing are authentication scenarios. Digital signatures and SSL certificates are based on hashes. It is also used in MySQL and MariaDB for data protection. The recommended function is SHA2(), the most secure option.

The syntax of the function is as follows:

SHA2(string_to_hash, bit_length)

string_to_hash is the plaintext string you want to hash.
bit_length is the desired bit length of the result value (224, 256, 384, or 512 are permitted).

SELECT SHA2('password', 256);

SELECT SHA2('password', 512);

If the argument is NULL, or the desired bit length does not match permitted values, the result is NULL too.

SELECT SHA2(password, 220);

Note: The SHA2 function is only available if SSL support is available for MySQL/MariaDB.

RANDOM_BYTES encryption

The RANDOM_BYTES function returns a binary string of random bytes generated by the SSL library. The length of that binary string can be from 1 to 1024.

The syntax of the function is as follows:

RANDOM_BYTES(length)

Note: If the length value is NULL, the function will return NULL too.

The most common scenario for using RANDOM_BYTES() in practice is generating passwords. The below example demonstrates random password generation for the first 5 customers:

SELECT customer_id, first_name, last_name, 
TO_BASE64(RANDOM_BYTES(32)) AS encryption_key
FROM customer
LIMIT 5;

These are the default methods used for encrypting and protecting data in databases. Another option is encrypting the connection via data-in-transit encryption.

Data-in-transit encryption

Data-in-transit encryption protects data while transferring it from a MySQL database to an application. The data gets encrypted in the source and goes encrypted over the network to be decrypted at the destination. MySQL uses the Transport Layer Security (TLS) protocol with OpenSSL for data-in-transit encryption.

MySQL programs establish encrypted connections by default if that encryption is enabled (and supported) on the server. The unencrypted connections fail. It is possible to set the requirement for having encrypted connections only.

In MariaDB, data encryption is not applied by default to the transmission process between the clients and the server. However, if the MariaDB server is compiled with TLS support, it is possible to encrypt data-in-transit via TLS and SSL (you need to use the latest SSL versions because MariaDB does not support older ones).

Checking encrypted connections and TLS protocols

The below commands help you check if the server supports SSL/TSL encryption, which TSL versions it supports, and if the server uses encrypted connections.

SHOW VARIABLES LIKE 'have_ssl';

When the have_ssl value is YES, the server supports SSL/TLS encryption.

SHOW VARIABLES LIKE 'tls_version';

The query output presents the TLS protocols supported by the MySQL server. The results may contain one or several values.

SHOW VARIABLES LIKE 'require_secure_transport';

If the require_secure_transport value is ON, then the server uses encrypted connections.

Determining if encrypted connections are active

To check if encrypted connections are enabled, you can query the performance_schema.tls_channel_status table. This query will return the information about the TLS-encrypted connections.

SELECT * FROM performance_schema.tls_channel_status
WHERE CHANNEL='mysql_main' AND PROPERTY='Enabled';

If the VALUE column displays YES, then encrypted connections are enabled.

Conclusion

MySQL encryption is one of the critical aspects in securing MySQL databases, applied to both the stored data (specific records, entire tables, and databases) and connections to databases. In this article, we explored some methods of encrypting data and connections, illustrating them with dbForge Studio for MySQL, an IDE for MySQL development and management, which includes a lot more options for securing MySQL databases and servers in the dedicated Security Manager where you can configure your security settings appropriately and straightforwardly in a visual mode.