今天，在学习mysql授权认证时，遇到了一个问题，看下，我是如何分析的：
我在数据库内添加了一个帐号：
create databases firstdb;
grant all on firstdb.* to ‘firstdb’@’’ identified by ‘xxxxx’;
(原计划用firstdb帐号登录能看到firstdb数据库，没想到发生了下面的故事，继续看，你也会成长的。)
我这样登录，mysql –ufirstdb –p 输入密码，可提示：
[root@wikiob ~]# mysql -ufirstdb -p
ERROR 1045 (28000): Access denied for user 'firstdb'@'localhost' (using password: YES)
我的密码，肯定没有问题，通过提示分析，我现在用的登录是localhost+firstdb
，但我定义的是任意主机，感觉没有匹配我想要的情况。
(root@badboy:)[(none)]>select host,user,password from mysql.user;
+---------------------+---------+-------------------------------------------+
| host | user | password |
+---------------------+---------+-------------------------------------------+
| localhost | root | D8BF0760B25D47A3EBF34F |
| wikiob.badboy.com | root | 0760B25D47A3EBF34F |
| 127.0.0.1 | root | 760B25D47A3EBF34F |
| wikiob.badboy.com | | |
| localhost | mantis | 36D0D144BDC21263CCFF |
| localhost | dvbbs |D1C26E56446E9DE2F52813 |
| 192.168.1.162 | root | 4D8BF0760B25D47A3EBF34F |
| 192.168.2.215 | root | 4D8BF0760B25D47A3EBF34F |
| | firstdb | 18BB99005ADCA2EC9D1E19 |
| localhost | test_db | 2A1F959FD02F964C7AF4CFC29 |
+---------------------+---------+-------------------------------------------+
11 rows in set (0.00 sec)
我们根据mysql在加载授权表时，要排序，最终排序结果：
+---------------------+---------+-------------------------------------------+
| host | user | password |
+---------------------+---------+-------------------------------------------+
| localhost | root | D8BF0760B25D47A3EBF34F |
| localhost | mantis | 36D0D144BDC21263CCFF |
| localhost | dvbbs |D1C26E56446E9DE2F52813 |
| localhost | test_db | 2A1F959FD02F964C7AF4CFC29 |
| wikiob.badboy.com | root | 0760B25D47A3EBF34F |
| wikiob.badboy.com | | |
| 127.0.0.1 | root | 760B25D47A3EBF34F |
| 192.168.1.162 | root | 4D8BF0760B25D47A3EBF34F |
| 192.168.2.215 | root | 4D8BF0760B25D47A3EBF34F |
| | firstdb | 18BB99005ADCA2EC9D1E19 |
+---------------------+---------+-------------------------------------------+
这样的话，我刚刚输入的mysql –ufirstdb –p就匹配了第5行，也就是说，客户端是localhost，帐号是任意，密码为空。
[root@wikiob ~]# mysql -ufirstdb -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.30-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
(firstdb@badboy:)[(none)]>select CURRENT_USER();
看到没，是匿名帐号，和我前面判断的没错,那看下这个帐号下的数据库有哪些….
(firstdb@badboy:)[(none)]>show databases;
(root@badboy:)[(none)]>select host,user,db from mysql.db;
+-----------+---------+---------+
+-----------+---------+---------+
| localhost | dvbbs | discuz |
| localhost | mantis | mantis |
| localhost | test_db | test_db |
+-----------+---------+---------+
(root@badboy:)[(none)]>select host,user,db from mysql.db;
+-----------+---------+---------+
+-----------+---------+---------+
| localhost | dvbbs | discuz |
| localhost | mantis | mantis |
| localhost | test_db | test_db |
+-----------+---------+---------+
根据前面登录的是匿名用户，那么只能是最后两行是匹配我的show databases;
通过这个实例，大家一定学会了，在grant一个帐号后，用此帐号登录后发现不是自己想要的结果,如何排除问题喽，加油！~
本文转自hahazhu0634 51CTO博客，原文链接：http://blog.51cto.com/5ydycm/253382，如需转载请自行联系原作者
