MySQL Study之--MySQL普通用户无法本地登陆

MySQL Study之--MySQL普通用户无法本地登陆

      在安装完毕MySQL后，我们通常加入拥有对应权限的普通用户用来訪问数据库。在使用用户本地登录数据库的时候，常常会出现怎么登录也无法登录的情况,可是从其他的mysqlclient却能够登录。

故障现象：
[root@mysrv ~]# mysql -u root -poracle
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> select version()g
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.25-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

创建用户并授权：
mysql> grant all on prod.* to 'rose'@'%' identified by 'rose';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for rose@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%'                                                      |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user,host from user;

+-------+-----------+
| user  | host      |
+-------+-----------+
| jerry | %         |
| rose  | %         |
| tom   | %         |
| tom1  | %         |
| tom2  | %         |
| root  | 127.0.0.1 |
| root  | ::1       |
|       | localhost |
| jerry | localhost |
| root  | localhost |
| scott | localhost |
| tom   | localhost |
|       | mysrv     |
| root  | mysrv     |
+-------+-----------+
14 rows in set (0.00 sec)

用户登陆：

[root@mysrv ~]# mysql -u rose -prose
ERROR 1045 (28000): Access denied for user 'rose'@'localhost' (using password: YES)          

---登陆失败！

[root@mysrv ~]# mysql -u rose -p     
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 22
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use prod;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'prod'--

---在不用password的情况下能够登陆，但没有权限訪问，应该是匿名用户的身份 ！

远程登陆：

---远程登陆成功！

一、登录后查看mysql.user表的情况

能够看到，我的数据库中有rose用户和匿名用户localhost;
mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for rose@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%'                                                      |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

二、在本机用rose用户登录。发现不用password能够登录。
[root@mysql01 ~]# mysql -urose -p
Enter password: 

mysql> select user(),current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| rose@localhost | @localhost     |
+----------------+----------------+
1 row in set (0.00 sec)

登录成功了，使用USER()和CURRENT_USER()两个函数查看所使用的用户。
USER()函数返回你在client登陆时指定的username和主机名。

CURRENT_USER()函数返回的是MySQL使用授权表中的哪个用户来认证你的登录请求。
这里发现，我使用'rose'@'localhost'这个账户登录数据库（由于在本地登陆时没指定主机。默认是以localhost登录），可是数据库使用的是''@'localhost'这个账户来进行登录认证。而''@'localhost'这个匿名用户是没有password的，因此我输入空password登录成功了。可是登录后，所相应的用户的匿名用户。

一般在MySQL在安装完成后，我们使用mysql_install_db这个脚本生成授权表，会默认创建''@'localhost'这个匿名用户。

正是由于这个匿名用户，影响了其它用户从本地登录的认证。
那么MySQL是怎样进行用户身份认证呢？

        一、当用户从client请求登陆时，MySQL将授权表中的条目与client所提供的条目进行比較，包含用户的username，password和主机。授权表中的Host字段是能够使用通配符作为模式进行匹配的。如test.example.com, %.example.com, %.com和%都能够匹配test.example.com这个主机。

授权表中的User字段不同意使用模式匹配，可是能够有一个空字符的username代表匿名用户。而且空字符串能够匹配全部的username。就像通配符一样。

当user表中的Host和User有多个值能够匹配client提供的主机和username时，MySQL将user表读入内存，而且依照一定规则排序，依照排序规则读取到的第一个匹配clientusername和主机名的条目对client进行身份验证。

       二、排序规则：对于Host字段，依照匹配的精确程度进行排序。越精确的排序越前，比如当匹配test.example.com这个主机时, %.example.com比%.com更精确，而test.example.com比%.example.com更精确。对于User字段，非空的字符串username比空字符串匹配的username排序更靠前。

User和Host字段都有多个匹配值，MySQL使用主机名排序最前的条目，在主机名字段同样时再选取username排序更前的条目。

因此，假设User和Host字段都有多个匹配值，主机名最精确匹配的条目被用户对用户进行认证。

了解了这个认证流程，就知道为什么server登录失败了。
     使用GaMe在本机登录数据时，不指定-h參数默觉得localhost主机登录，而在MySQL中有两个匹配的条目：'rose'@'%'  和 ''@'localhost'
匿名用户可以匹配的原因上面说过，空字符串可以匹配全部的username，就像通配符一样。
依据MySQL认证时的排序规则，第一个条目的username排序更前。第二个条目的主机名更精确。排序更前。
而MySQL会优先使用主机名排序第一的条目进行身份认证。因此''@'localhost'被用户对client进行认证。

因此，仅仅有使用匿名用户的空password才干登录进数据库。

就会出现以下的情况了。
    解决办法：删除匿名用户（只为了安全也有这个必要）
为什么root用户不会受影响，而仅仅有普通用户不能从本地登录？
由于mysql_install_db脚本会在授权表中生成'root'@'localhost'这个账户。

相同的。使用root登录MySQL 时，'root'@'localhost'和''@'localhost'都能匹配登录的账户，可是依据排序规则，主机名相同。而username非空字符串优先。因此'root'@'localhost'这个条目的排序更靠前。

使用root本地登录是不会被匿名用户遮盖。

解决方法：

授权rose用户本地登陆：

mysql> grant all on prod.* to 'rose'@'localhost' identified by 'rose';
Query OK, 0 rows affected (0.01 sec)

从本地登陆：

[root@mysrv ~]# mysql -u rose -prose
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 26
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use prod;
Database changed
mysql> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|   10 | tom   |
|   20 | jerry |
|   30 | rose  |
+------+-------+
3 rows in set (0.00 sec)

---登陆成功！