问题原文
原帖链接 · lwi · CC BY-SA 3.0
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)?
社区高分解答
原帖链接 · Ajeet Khan · CC BY-SA 4.0
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.
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'xyz';
摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。
