MySQL Encryption: The Easy Guide to Protect Your Data in 2022

Is your business using MySQL to store data? Then, you should take MySQL encryption seriously. I mean it.

Here’s why.

Data breaches rose to 68% last year. That’s the highest so far. Not so alarming? Wait until you know how much this thing will cost you in 2022.

USD 4.35 billion! That climbed 12.7% from USD 3.86 million in the 2020 report.

So, is your data of immense value to you? Then, know that if it falls into the wrong hands, your business is toast. Competitors can outgun you. Cybercriminals can steal your customer data. And your customers will turn against you. In the end, you will have a tarnished reputation. And you may never recover from this loss.

This has become a major concern across many industries. That’s why encryption is important.

This article will discuss one facet of data security – encryption. And encryption can protect your data in MySQL. Then, we will have a slight detour to MariaDB. But to make it easier as possible, the article will not sound like rocket science.

Here’s what we are going to cover:

• Introduction to Data Encryption
• How Does Encryption Work in MySQL
• MySQL Encryption Functions
• MySQL Encryption Examples Using dbForge Studio for MySQL
• MariaDB Encryption Examples

But before we head on to the juicy parts of this article, let’s have a few introductions.

Introduction to Data Encryption

Encryption is the process of converting information from plain text into ciphertext. The process is also known as encoding. The purpose is to hide information from unauthorized people. In short, you make it top secret. Only you or the people you trust can decode the information to make it readable again.

You may not know it but encryption has been a thing since ancient times. Fast-forward to the 19th century, the Allied and Axis powers also used it in sending top secret messages. Today, we’re using it in day-to-day tasks like shopping, sending messages, browsing a site, and more. As computing power increases, the ability and speed to crack encrypted information become easier too. So, encryption standards are constantly evolving to evade cyberattacks.

This means that you must adapt too.

So, when does encryption help you to protect your data?

• When a service provider hosts your MySQL database.
• When an attacker gains access to your database and reads it.

What data do you need to encrypt?

• Personal data, including contact information and passwords.
• Financial data
• Customer details
• Research, studies, and the like

In 2022, the following sections will explain how you will do it.

How Does Encryption Work in MySQL

Please note that encryption is just one of the ways to secure a MySQL database. And how capable encryption also depends on your license of MySQL. MySQL Community Edition uses fewer encryption features compared to MySQL Enterprise edition. We will cover both editions here.

Note also that the version of MySQL discussed here is 8.0.30 running on Ubuntu 22.04.

So, how does encryption work in MySQL?

MySQL Encrypted Connections

When you connect to a MySQL server, the password is not transmitted as plaintext. All other information is clear text. And it’s readable by anyone capable of sniffing connections.

So, you need to set up MySQL to use encrypted connections. That’s one of MySQL encryption best practices.

MySQL permits encrypted connections by default but does not require it. You need to explicitly enable the require_secure_transport variable. This will force clients to use encrypted connections. This requires that you have a certificate authority, public key, and private key on the server. If require_secure_transport is enabled, the client also needs those keys.

Another requirement is Transport Layer Security (TLS). MySQL 8 supports TLS 1.2 and 1.3.

SSL and TLS refer to the handshakes between the server and the client. A handshake doesn’t do the encryption. But it agrees on the shared secret keys and the type of encryption to be used.

So, when you connect to MySQL using encrypted connections, you need to know these. You’ll see an example of this later.

Data-at-Rest Encryption

MySQL uses the InnoDB storage engine by default. And InnoDB supports data-at-rest encryption.

The InnoDB data-at-rest encryption uses a 2-tier encryption architecture. These are master encryption keys and tablespace keys. It works like this:

• First, an encrypted key in the tablespace header is stored during tablespace creation.
• Then, a master encryption key is used when authenticated users read data. This master key is used to decrypt the tablespace key.

This architecture relies on a keyring component or plugin. The purpose is to manage the master encryption key.

All MySQL editions provide a component_keyring_file component and keyring_file plugin. It stores keyring data in a file local to the server host.

Meanwhile, MySQL Enterprise Edition offers additional keyring components and plugins:

• component_keyring_encrypted_file- Stores keyring data in an encrypted, password-protected file. This file is also local to the server host.
• keyring_encrypted_file: Uses an encrypted, password-protected file to store keyring data.
• keyring_okv: A KMIP 1.1 plugin. This is for KMIP-compatible back-end keyring storage products like Oracle Key Vault.
• keyring_aws: Uses the Amazon Web Services Key Management Service (AWS KMS). It serves as a back end for key generation and uses a local file for key storage.
• keyring_hashicorp: Communicates with HashiCorp Vault for back-end storage.

Advanced Encryption Standard (AES) is the only supported encryption algorithm. InnoDB tablespace encryption uses 2 block encryption modes. One is Electronic Codebook (ECB) for tablespace key encryption. Another is the Cipher Block Chaining (CBC) for data encryption.

Check out the examples later for tablespace encryption.

Visit the official documentation for the prerequisites, limitations, and other details.

MySQL Encryption Functions

There are several MySQL encryption functions. But we are going to emphasize 4 functions:

AES_ENCRYPT and AES_DECRYPT

AES_ENCRYPT encrypts a string using AES. AES_DECRYPT is the opposite.

Syntax:

For AES_ENCRYPT:

AES_ENCRYPT(<string to encrypt>, <key string> [,<initialization vector>][,<kdf name>][,<salt>] [,<info|number of iterations>])

For AES_DECRYPT:

AES_DECRYPT(<string to decrypt>, <key string> [,<initialization vector>][,<kdf name>][,<salt>] [,<info|number of iterations>])

Arguments:

• <string to encrypt> is the plaintext string you want to encrypt, like a password.
• <string to decrypt> is the ciphertext string you want to decrypt.

The following requires the same parameter value when used in the same system:

• <key string> is the encryption key. Or the input keying material used as the basis for deriving a key using a key derivation function (KDF). If you do not use a KDF, hash this first using SHA2.
• <initialization vector>. Depending on the block_encryption_mode variable, this is required. This is not required for aes-128-ebc (default). Other modes like aes-256-cbc require it. You can use the value generated by RANDOM_BYTES(). And keep that value in succeeding encryptions and descriptions.
• <kdf name>. Can be hkdf or pbkdf2_hmac. If you specify a KDF, <initialization vector> is required. Then, specify an optional <salt>. With hkdf, optionally set <info> with context-specific information like app details. But with pbkdf2_hmac, specify the <number of iterations> to produce the key. These will be included in the keying material.
• <salt> is the salt passed to the KDF. Though optional, salt can help prevent attacks based on dictionaries of common passwords. You can produce salt using RANDOM_BYTES(). And keep that value for succeeding encryptions and decryptions.
• <info> is the context-specific information for HKDF to include in the keying material. It can be the information about the application.
• <number of iterations> is the iteration count for the pbkdf2_hmac KDF. The minimum is ‘1000’ for the OpenSSL standard. Increasing the value gives greater resistance to brute-force attacks.

SHA2

Hashes a plaintext string based on the desired bit length. This is the recommended hashing function compared to MD5 and SHA1. Note that exploits against MD5 and SHA1 exist. And we won’t show any examples of these.

Syntax:

SHA2(<string to hash>,<bit length>)

Arguments:

<string to hash> is the plaintext you want to hash.

<bit length> can be 224, 256, 384, 512. The larger the bit, the more combinations are possible. This will also avoid collisions or the same hash for different values. SHA-256 means 2256 combinations. That number is bigger than the grains of the sand.

RANDOM_BYTES

This function returns a binary string of random bytes. The SSL library generates the random number. Permitted values of <len> range from 1 to 1024. But bytes more than 16 are ignored when used in the initialization vector.

Syntax:

RANDOM_BYTES(<len>)

Arguments:

<len> is a number from 1 to 1024.

MySQL Encryption Examples Using dbForge Studio for MySQL

Example 1: Check if Encrypted Connections are Enabled

The performance_schema.tls_channel_status table provides TLS information for the current server. Run the following to check for encrypted connection settings:

SELECT * FROM performance_schema.tls_channel_status

WHERE CHANNEL=’mysql_main’ AND PROPERTY=’Enabled’;

If the VALUE column is Yes, encrypted connections are enabled. See a sample below:

Example 2: Check if Encrypted Connections are Required and the TLS Versions Supported

The following shows how to check for 2 variables. One for the supported TLS version. Another is if encrypted connections are required:

SELECT @@tls_version, @@require_secure_transport;

Or use View Server Variables in IDE for MySQL – dbForge Studio for MySQL. Simply filter by the variable name. See the result below:

If require_secure_transport is ON, the client’s certificate information is required. The following is an example:

The above is dbForge Studio’s SSL connection properties. The client key, client certificate, and authority certificate were included. The TLS version is also specified. If everything else checks out, clicking Test Connection will prompt a good connection.

Example 3: Set Systemwide Database Encryption

The default_table_encryption variable tells you if table encryption is enabled systemwide. To enable it, run the following:

SET GLOBAL default_table_encryption = ON;

When it’s ON, encryption happens on new tables without specifying ENCRYPTION=’Y’. See below.

Example 4: Encrypt the Binary Log

To encrypt the MySQL binary log, run the following:

SET GLOBAL binlog_encryption = ON;

But why would you do this?

Because binary log files contain statements that change the database. Statements like INSERT, UPDATE, or DELETE go here. And you can view the log using the mysqlbinlog utility. Let’s say a statement contains a plaintext password like this:

USE encrypted_db;

INSERT INTO user_credentials

(id, username, password)

VALUES

(2, ‘testuser2’, ‘UltimateP@s4w0rd’);

You can view these values in the server’s terminal. Here’s a sample:

You can try this yourself. First, go to /var/lib/mysql folder in Ubuntu Terminal. Then, look for the binary log named binlog.000xxx, where the file extension is the binary file log number. Then, type a similar command in the terminal:

# mysqlbinlog –verbose binlog.000171

The –verbose option will expose the statement. And binlog.000171 is the log in question.

But if you encrypt the binary log, inserting a new record will not show the statement. See the result after encrypting the binary log and inserting a new record.

Example 5: Using AES_ENCRYPT with SHA2

Here’s an example of encrypting a password with an encryption key.

INSERT INTO user_credentials

(id, username, password)

VALUES

(1, ‘testuser1’, AES_ENCRYPT(‘UltimateP@s4w0rd’,UNHEX(SHA2(‘sshh secret!’,512))));

A few notes on this example:

• The password is a BINARY(32) column, not CHAR or VARCHAR. This avoids potential problems with trailing space removal. Or character set conversion that would change data values. These may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).
• The SHA2 hash uses a 512-bit length – the highest (SHA-512).
• UNHEX is used to convert the pairs of hexadecimal digits by SHA2 to binary. This will result in more efficient storage.

Here’s what it looks like in dbForge Studio for MySQL using the Data Viewer.

It is now unreadable.

The above method is different from MySQL.

Meanwhile, using dbForge Studio on a MariaDB database is like using it in MySQL. You just need to remember you’re in MariaDB.

Conclusion

So, what have we learned?

There are several ways to use MySQL encryption. It covers not just column data, but tables, databases, and connections. So, why not experiment on this further? Moreover, use a tool that will make you learn faster and be more productive. So, check out dbForge Studio for MySQL (and MariaDB) today and see the difference.