今天，在学习mysql授权认证时，遇到了一个问题，看下，我是如何分析的：
我在数据库内添加了一个帐号：
create databases firstdb;
grant all on firstdb.* to
‘firstdb’@’’ identified by ‘xxxxx’;
flush privileges;
(原计划用firstdb帐号登录能看到firstdb数据库，没想到发生了下面的故事，继续看，你也会成长的。)
我这样登录，mysql –ufirstdb –p 输入密码，可提示：
[root@wikiob ~]# mysql
-ufirstdb -p
Enter password:
ERROR 1045 (28000):
Access denied for user 'firstdb'@'localhost' (using password: YES)
我的密码，肯定没有问题，通过提示分析，我现在用的登录是localhost+firstdb
，但我定义的是任意主机，感觉没有匹配我想要的情况。
分析：
看下mysql.user表的情况
(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 |
| localhost | | |
| 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 |
|
localhost | | |
|
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
Enter password:
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.
(wiki@badboy:)[(none)]>
好，可以进去了。我现在来看看，我的登录帐号信息：
(firstdb@badboy:)[(none)]>select
CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
1 row in set (0.00 sec)
看到没，是匿名帐号，和我前面判断的没错,那看下这个帐号下的数据库有哪些….
(firstdb@badboy:)[(none)]>show
databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| test_db |
+--------------------+
3 rows in set (0.00 sec)
这三个数据库是怎么在匿名帐户下呢？继续分析…
看下mysql.db
(root@badboy:)[(none)]>select
host,user,db from mysql.db;
+-----------+---------+---------+
| host
| user | db |
+-----------+---------+---------+
|
| firstdb | firstdb |
| %
| | test |
| %
| | test\_% |
| localhost | dvbbs | discuz
|
| localhost | mantis | mantis
|
| localhost | test_db | test_db |
+-----------+---------+---------+
6 rows in set (0.00 sec)
再排序一次：
(root@badboy:)[(none)]>select
host,user,db from mysql.db;
+-----------+---------+---------+
|
host | user | db
|
+-----------+---------+---------+
|
localhost | dvbbs | discuz |
|
localhost | mantis | mantis |
|
localhost | test_db | test_db |
| | firstdb | firstdb |
|
% | | test |
|
% | | test\_% |
+-----------+---------+---------+
6
rows in set (0.00 sec)
根据前面登录的是匿名用户，那么只能是最后两行是匹配我的show databases;
通过这个实例，大家一定学会了，在grant一个帐号后，用此帐号登录后发现不是自己想要的结果,如何排除问题喽，加油！~
本文出自 “坏男孩” 博客，请务必保留此出处http://5ydycm.blog.51cto.com/115934/253382
实例分析mysql用户登录
社区：
