场景回顾

环境

win10、mysql 8.0.22

场景

使用 root 账户连接数据库，执行如下脚本建立用户，赋权：

  1. use mysql;
  2. select VERSION();
  3. drop user if EXISTS 'demo'@'%';
  4. create user if not EXISTS 'demo'@'%' IDENTIFIED by 'demo123';
  5. create DATABASE if not EXISTS demo_test;
  6. grant all PRIVILEGES on demo_test.* to 'demo'@'%';
  7. GRANT SELECT ON `performance_schema`.user_variables_by_thread TO 'demo'@'%';
  8. show grants for 'demo'@'%';
  9. FLUSH PRIVILEGES;
  1. 连接不上 mysql 报错 ERROR 1045 (28000): Access denied for user 'demo'@'localhost' (using password: YES)
  1. D:\devServer\mysql-8.0.22-winx64\bin>mysql.exe -udemo -pdemo123 -h127.0.0.1
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. ERROR 1045 (28000): Access denied for user 'demo'@'localhost' (using password: YES)
  5. D:\devServer\mysql-8.0.22-winx64\bin>mysql.exe -udemo -pdemo123 -hlocalhost
  6. mysql: [Warning] Using a password on the command line interface can be insecure.
  7. ERROR 1045 (28000): Access denied for user 'demo'@'localhost' (using password: YES)
  9. D:\devServer\mysql-8.0.22-winx64\bin>
  1. 建立的 demo 用户，HOST 为 %，为什么就不能 127.0.0.1 （localhost）连接呢？% 不是通配符吗，通配符不匹配本机？同事的就能连接我的，自己的反而不能？

解决过程

先看官网说明

查看官网连接失败 https://dev.mysql.com/doc/refman/8.0/en/problems-connecting.html
连接说明 https://dev.mysql.com/doc/refman/8.0/en/connection-access.html
官网说了，验证账户，将 mysql.user 加载到内存中有个顺序，先走确切的（HOST 明确的，比如ip，hostname，最后走 % 通配符）。本机的 user 数据如下：

  1. D:\devServer\mysql-8.0.22-winx64\bin>mysql.exe -uroot -p123456
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 30
  5. Server version: 8.0.22 MySQL Community Server - GPL
  7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> use mysql;
  16. Database changed
  17. mysql> select HOST,USER from users;
  18. ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
  19. mysql> select HOST,USER from user;
  20. +--------------+------------------+
  21. | HOST         | USER             |
  22. +--------------+------------------+
  23. | %            | demo             |
  24. | %            | xiaohui          |
  25. | %            | xiaohuihui       |
  26. | %            | xxhh             |
  27. | 192.168.0.12 |                  |
  28. | 192.168.1.59 | xh               |
  29. | localhost    |                  |
  30. | localhost    | mysql.infoschema |
  31. | localhost    | mysql.session    |
  32. | localhost    | mysql.sys        |
  33. | localhost    | root             |
  34. | localhost    | xiaohui          |
  35. +--------------+------------------+
  36. 12 rows in set (0.00 sec)
  38. mysql>

本次 mysql 的 user 中存在一个匿名的用户 ‘’@’localhost’ ，当用户从本地（127.0.0.1，localhost）尝试连接 mysql 时，先匹配 ‘’@’localhost’ ，验证没通过，就没有走 ‘demo’@’%’ 这条验证。
查看用户权限：

  1. ALTER user if exists ''@'localhost' IDENTIFIED BY '123456'; 
  2. FLUSH PRIVILEGES;
  4. select CURRENT_USER(), user();
  5. show grants for CURRENT_USER();
  6. -- show grants for 后面不支持 user() 函数
  7. -- show grants for USER(); 
  8. show grants for ''@'localhost';

CURRENT_USER(), user() 这两个函数将显示连接的用户和host：

  1. D:\devServer\mysql-8.0.22-winx64\bin>mysql.exe -p123456 --user=demo
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 51
  5. Server version: 8.0.22 MySQL Community Server - GPL
  7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> select current_user(),user();
  16. +----------------+----------------+
  17. | current_user() | user()         |
  18. +----------------+----------------+
  19. | @localhost     | demo@localhost |
  20. +----------------+----------------+
  21. 1 row in set (0.00 sec)
  23. mysql>