With the latest MySQL Innovation Release, we decided that it was time to remove the remaining weak authentication plugin: mysql_native_password.

We previously deprecated it and made it not default loaded in MySQL 8.4 LTS and, now, in 9.0 it’s gone!

Reasons

Oracle places significant attention on the security of all its products, and MySQL is no exception. The removal of the weak authentication plugin has been carefully considered, we had some extra time for the LTS release as it was initially intended for version 8.4, but it is now fully effective.

But why is the mysql_native_password considered as weak compared to more modern authentication methods like the default caching_sha2_password:

1. Weak Hashing Algorithm: mysql_native_password uses the SHA-1 hashing algorithm to hash passwords. SHA-1 is considered weak and vulnerable to certain types of cryptographic attacks, such as collision attacks, where two different inputs produce the same hash output (see SHAttered).

2. No Salt: mysql_native_password does not use salting when hashing passwords. Salting adds random data to the password before hashing, which makes it more difficult for attackers to use precomputed hashes to crack passwords. The lack of salting makes this authentication method more vulnerable to such attacks.

3. No Iterations: more secure hashing methods use multiple iterations of the hash function to slow down the hashing process, making brute-force attacks more time-consuming. mysql_native_password does not use multiple iterations, which makes it faster to compute and therefore easier to brute-force

Implications

In practice, this means that old connectors that were already struggling with MySQL 8.0, like PHP 7.2 for example, won’t be able to connect to MySQL 9.0.

Let’s have a look at this simple PHP example:

<?php
$servername = "192.168.56.1";
$username = "test_user";
$password = "xxxxxxx";

echo "PHP version: " . phpversion() . "\n";

$conn = new mysqli($servername, $username, $password );

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error . "\n");
}
echo "Connected successfully\n";

echo "MySQL version: " . $conn->server_info . "\n";

$conn->close();
?>

The script is running on a fresh Oracle Linux 8 with PHP and php-mysqlnd installed:

[root@mysql1 ~]# cat /etc/oracle-release
Oracle Linux Server release 8.6

[root@mysql1 ~]# rpm -qa | grep php
php-mysqlnd-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-cli-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-fpm-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-pdo-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-common-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64

Now let’s run the script that connects to our MySQL 9.0 server where the test_user has been created:

SQL> select version();
+-----------+
| version() |
+-----------+
| 9.0.0     |
+-----------+
1 row in set (0.0005 sec)

SQL> select user, host, plugin from mysql.user where user='test_user';
+-----------+------+-----------------------+
| user      | host | plugin                |
+-----------+------+-----------------------+
| test_user | %    | caching_sha2_password |
+-----------+------+-----------------------+
1 row in set (0.0009 sec)

[root@mysql1 ~]# php test.php
PHP version: 7.2.24
PHP Warning:  mysqli::__construct(): The server requested authentication method
 unknown to the client [caching_sha2_password] in /root/test.php on line 9
PHP Warning:  mysqli::__construct(): (HY000/2054): The server requested
 authentication method unknown to the client in /root/test.php on line 9
Connection failed: The server requested authentication method unknown to the client

Solutions

The only valid, supported, and recommended solution if you want to use MySQL 9 is to upgrade your connector. For this example, it will be required to upgrade to a more recent PHP.

A man who knows is worth two!

You can also continue to use MySQL 8. MySQL 8.4 is an LTS version that is still supported for many years (extended support will end in April 2032).

If you don’t use any support and if you run MySQL on your own, in the end, MySQL is Open Source and pluggable, you can write your own authentication plugin and why not backport the mysql_native_password one? This is not recommended of course as it won’t make it more secure but it’s feasible. Everything is (almost) feasible and that’s the reason why MySQL is cool.

This is an example:

Enjoy MySQL and enjoy secure connections to MySQL 9.0!