Migration path for IDENTIFIED BY PASSWORD 'password_hash' when removed in MySQL 8

Question

We have a solution where the management of users to a MySQL database is automated. Today we use MySQL 5.6 but we want to move to MySQL 8.

According to https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/mysql-nutshell.html

IDENTIFIED BY PASSWORD

was removed. Unfortunately, this is something that we make use of. We store the hash of the password outside the database, and then whenever a user needs to be created we can use it to directly create the account without actually knowing the password.

Why not create user immediately and remove the need for external storage?

We also make use of MySQL's host concept to limit access from only certain IP addresses. However, these may change. Use case:

1. A new user 'myUser' should be given access from 1.2.3.4 with password 'myPassword'
2. The hash for 'myPassword' is calculated and stored together with other metadata in an external storage
3. A MySQL user [email protected] is created with the given hash
4. Later, user should also be given access from IP 5.6.7.8
5. No problem, a new MySQL user [email protected] is created using same hash as before

The end result is a solution that is restrictive and user isn't aware of that technically two accounts exist. We also don't know the user's password.

As far as I can understand, this solution will not work going forward with MySQL 8 so I'm looking at possible migration paths. One possible solution would be if the following was possible:

Is it possible to create a new user in MySQL and set its password and authentication plugin to exactly the same as another user, without actually knowing the underlying password with MySQL 8? If yes, we could use that with some trickery to create any additional users by simply copying the information we have. We would only need to know the password while the first initial user is created, but whenever a new IP adress is given access we can perform a "copy" logic instead.

Any ideas? Thanks in advance!

Answer 1

We will need to perform additional checks, but a workaround we have found is to simply instead manually set "authentication_string" and "plugin" in "mysql.user" to the desired values, and then execute "flush privileges". That is, to keep using 'mysql_native_password' and sha1(sha1(password)) that we know the hash is calculated as.

We then wouldn't be able to make use of the new default authentication plugin though. A quick check also says that caching_sha2_password (new default) doesn't have a stable authentication_string wrt password. Two users with the same password yielded different values so I'm assuming its implementation is more advanced in what input is used.