Seven Must-Dos to Secure MySQL 8.0

Most database breaches are blamed on insiders such as employees who are either malicious or whose security has been compromised. In fact, most of these breaches are actually caused by poor security configuration and privilege abuse. Every new database version brings security upgrades. Use them appropriately and your organization can secure its data and keep you out of trouble.

MySQL 8.0 was released in April 2018, bringing improvements across the board, including important security enhancements. In this blog, we will review the new security features and guide you on how to use them appropriately.

1. Use a Strong Authentication Plugin

When a client connects to the MySQL server, the server verifies which authentication plugin is configured for this user and then invokes that plugin to authenticate users who provide the correct password.

Starting with MySQL 8.0, the default authentication plugin is changed from ‘mysql_native_password’ to ‘caching_sha2_password.’ The former plugin implements the SHA1 algorithm, which is more susceptible than expected to collision attacks that fabricate the same hash value for different inputs. In addition, the hash function is calculated only on the user’s plain password. As a result, two users with the same password will have the same hash value. In such a case, an attacker that gains access to the mysql.user table can get the user’s hash value and use a rainbow table to recover the plain password.

The new default authentication plugin, ‘caching_sha2_password,’ implements SHA-256 hashing, which is much stronger because it 1) is not susceptible to collision attacks like SHA1, and 2) uses 5,000 rounds of SHA-256 transformation on a salted password (combination of the password with random data), preventing the use of rainbow tables to recover the password.

If you use MySQL native plugins – and not external authentication such as PAM, Windows login IDs, LDAP, or Kerberos – we encourage you to use ‘caching_sha2_password’ for all new users.

(Another authentication plugin, ‘sha256_password’, which was introduced in version 5.6, also uses SHA-256, and from security perspective it can also be used. However, the advantage of the new default ‘caching_sha2_password’ plugin over this one is that it uses server-side caching for better performance.)

Configuration Guidelines:

Here’s how to create new users with the new default authentication plugin, ‘caching_sha2_password’:

For current users authenticated with ‘mysql_native_password’, here’s how to update them to ‘caching_sha2_password’:

2. Use Database Roles to Enforce Least Privilege

The Principle of Least Privilege mandates that each user is limited to access only the information they need. Role-based security can enable this: privileges are granted to roles and users are assigned to roles according to their needs. This approach is so fundamental that it is required to comply with security benchmarks such as GDPR, DISA and CIS. Without role-based access, you may end up with non-administrative users with more privileges than they need, such as an application accessing a database (applicative user) that is using an account with DBA privileges.

Such negligence creates a great surface for attacks. As reported in the Verizon Data Breach Investigations Report 2018 report, out of the 2,216 confirmed data breaches reported, more than 200 are attributed to Privilege Abuse.

Most other major relational databases implemented role-based security many years before MySQL 8.0 did. We encourage you to adopt MySQL 8.0 and start managing your privileges using roles as soon as possible.

3. Apply the Change Current Password Policy

MySQL 8.0.13 now lets you require your non-privileged users to enter their current password at the time they set a new password. Such approach can save you in several ways. For example, if an attacker who has compromised your application host machine uses a Web Shell to access a user’s database session. The attacker will not be able to change the password and lock the user out without the user’s current password.

The Change Current Password policy is off by default. You can control it globally for all non-privileged users or enforce it for individual users. We encourage you to set this policy globally. If not, set it for all your non-privileged users, especially your applicative users.

Configuration Guidelines:

To enable Current Password policy globally, put this line in the server my.cnf file:

4. Apply the Password Reuse Policy

MySQL 8.0 enables restrictions on the reuse of previous passwords. Reuse restrictions can be established based on the number of password changes, time elapsed, or both.

Reuse policy can be global, with individual accounts set to either defer to the global policy or override the global policy. We encourage you to establish the reuse policy globally, and set the values according to your security benchmark requirements.

Configuration Guidelines:

To prohibit reusing any of the last 6 passwords or passwords used within the last 365 days, put these lines in the server my.cnf file:

5. Enable FIPS Mode

FIPS Mode on the server side applies to U.S. government-approved cryptographic operations performed by the server. This includes replication (master/slave and Group Replication) and X Plugin, which run within the server, as well as the use of certain encryption ciphers during client-server communication.

MySQL 8.0 supports FIPS Mode if compiled using OpenSSL, and if an OpenSSL library and FIPS Object Module are available at runtime.

However, there is nothing about FIPS mode that prevents establishing an unencrypted connection (to do that, you can use the REQUIRE clause for CREATE USER or ALTER USER for specific user accounts, or set the require_secure_transport system variable to affect all accounts)

We encourage you to enable FIPS Mode and always establish encrypted connections.

Configuration Guidelines:

To enable FIPS Mode, put this line in the server my.cnf file:

6. Migrate Accounts from SUPER to Dynamic Privileges

In MySQL 8.0, many operations that previously required the SUPER privilege are also possible with Dynamic Privileges of more limited scope. Migrating from SUPER to Dynamic Privileges improves security by enabling DBAs to avoid granting SUPER, and tailoring user privileges more closely to the operations permitted. SUPER is now deprecated and will be removed in a future version of MySQL.

Configuration Guidelines:

Execute the following query to identify accounts granted with SUPER privilege:

For each account listed above, grant the Least Privilege it needs and revoke the SUPER privilege. For example: if user ‘admin_log’@’1.1.1.1’ requires SUPER for binary log purging, execute the following:

7. Enable Redo Log and Undo Log Data Encryption

Redo data and undo data contains sensitive information about operations performed in the database. MySQL 8.0 adds redo and undo log data encryption when data is written or read from disk. Such encryption at rest for redo and undo files is very important. There is no reason to use the tablespace encryption feature introduced in MySQL 5.7 without also encrypting the redo and undo data. We encourage you to configure these files to be encrypted.

Configuration Guidelines:

Redo log encryption and Undo log encryption are disabled by default. After you configure encryption prerequisites, you can enable them by putting these lines in the server my.cnf file:

Summary

MySQL 8.0 provides new important security enhancements. We encourage you to adopt the new release and follow our guidelines in order to use them appropriately. With most database breaches today the result of bad configuration and privilege abuse, adopting and configure the new security enhancements properly will secure your data and most likely keep you out of trouble.