Problem:
I am encountering an login problem when setting up users in mysql. Old users can login as expected, for newly created users I cannot login and get an error message.
Executed SQL:
CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON somedb.* TO 'monty'@'%';
FLUSH PRIVILEGES;
Expected:
I would now expect to be allowed to login as user monty from my client code (what works with already existing users).
Error: The login fails with following error message in my client code:
SQLSTATE[HY000] [1524] Plugin '*937217B9DD8E6E458FC3F434BB7292891****' is not loaded'
More Details
when I check the mysql.user table, I see that the plugin column contains the hashed password of the user which is 937217B9DD8E6E458FC3F434BB7292891****
.
So I assume there is an error with the auth plugin and followed the migration guidelines(https://dev.mysql.com/doc/refman/5.6/en/account-upgrades.html).
Altering the user with
ALTER USER 'monty'@'%' IDENTIFIED WITH mysql_native_password BY 'some_pass';
helps. However, as soon as I do a flush privileges
the login fails again.
Involved system is a docker container build from mysql/mysql-server.
mysql Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using EditLine wrapper
uname -a:
Linux 1046ac1aaeee 4.2.0-27-generic
#32~14.04.1-Ubuntu SMP Fri Jan 22 15:32:26 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
mysql auth vars
mysql> show variables like '%auth'
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
| secure_auth | ON |
+-------------------------------+-----------------------+
2 rows in set (0.00 sec)
Question
How do I get the newly created users login to work permanently (even after a flush privileges)?
Best Answer
This issue is explained here here.
Run
mysql_upgrade -u root -p
to fix the issue.OR
If you are creating the user and getting this issue, then use BY instead of WITH in your query.