MySQL5/OldPasswords

Description

The old_passwords variable changes the behaviour of password hashing that the PASSWORD function performs as well as the CREATE USER statement.

if your database configuration doesn’t use this option don’t change it. This is a deprecated variable that was useful to migrate from previous MySQL releases.

The goal of this variable is to help an application migration process from older MySQL version to newer ones. This test ensures the legacy option that allows connection from clients older than 4.1 isn’t used.

An incorrect usage of this option may degrade the security of passwords stored in the database.

Rationale

Setting the old_passwords variable to "1" will allow connections from clients older than MySQL 4.1. MySQL 4.0 reached EOL at December 31, 2008. A database server shouldn’t accept connections from clients at unsupported versions.

To support those older clients the hashing method used to store passwords in the database is weak, producing 16 bytes hashes. This setting affects any client that connect to the server, including newer ones. This means that allowing connections from those older clients reduces the security of every client connection to the database server.

The first step is to investigate if any of the current clients are using an unsupported version of MySQL. Update those client versions to the most recent compatible with the server version.

After making sure all clients are up to date, change the setting to a more secure option.

Edit the old_passwords option at the [mysqld] group in the configuration file.

Search which configuration file define the option value:

# Look every .cnf file at /etc/mysql and subdirs
grep old_passwords /etc/mysql/{,**/}*.cnf

The output of the command above will point to the culprit file:

/etc/mysql/mysql.conf.d/mysqld.cnf:old_passwords = 1

In the example above the issue is at /etc/mysql/mysql.conf.d/mysqld.cnf

Open the discovered file with your favorite text editor and replace the old_passwords value from "1" to "0" or "2":

# Ensure insecure hashing is not used
# see mysql-operous-2021-0002
old_passwords = 0  (1)
1 Available options are 0 or 2. If you use 0, the native MySQL hashing option, remove this line because it’s the default value.

Restart the database server to apply the configuration changes.

Now change the password of each user to discard the old unsafe hashes and generate new safer ones.

The SQL statement required here will vary with the MySQL version.

For 5.7 use:

ALTER USER 'username'@'hostname'
IDENTIFIED WITH mysql_native_password BY 'the-new-password';

For versions before 5.7:

SET old_passwords = 0;
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('the-new-password')
WHERE (User, Host) = ('username', 'hostname');
FLUSH PRIVILEGES;
Despite being possible to use the same password as before, consider using a new one. After all the database stored this password with a weak hash function.

The final step is to update the application configuration that connects to this MySQL server to use the new password.