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.
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:
- A new user 'myUser' should be given access from 18.104.22.168 with password 'myPassword'
- The hash for 'myPassword' is calculated and stored together with other metadata in an external storage
- A MySQL user myUser@22.214.171.124 is created with the given hash
- Later, user should also be given access from IP 126.96.36.199
- No problem, a new MySQL user myUser@188.8.131.52 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!