I use the root account created the account 'a'@'%'
. But I can't use the account to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h
parameter. Please see the transcript below. I hope someone can help me to explain it. Thanks.
mysql> grant all on *.* to 'a'@'%' identified by a;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a' at line 1
mysql> grant all on *.* to 'a'@'%' identified by 'a';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'a'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for a@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'a'@'%' IDENTIFIED BY PASSWORD '*667F407DE7C6AD07358FA38DAED7828A72014B4E' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -h localhost -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -h 127.0.0.1 -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u a
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.5.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, 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>
mysql> status
--------------
mysql Ver 14.14 Distrib 5.5.17, for Linux (x86_64) using readline 5.1
Connection id: 20
Current database:
Current user: a@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.17 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 15 days 15 hours 20 min 18 sec
Threads: 1 Questions: 40 Slow queries: 0 Opens: 41 Flush tables: 1 Open tables: 4 Queries per second avg: 0.000
--------------
mysql>
Edit:
Yes, MySQL is listening on port 3306.
[root@localhost ~]# nmap localhost
Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2012-01-18 07:35 CST
Interesting ports on localhost.localdomain (127.0.0.1):
Not shown: 1674 closed ports
PORT STATE SERVICE
22/tcp open ssh
25/tcp open smtp
111/tcp open rpcbind
631/tcp open ipp
840/tcp open unknown
3306/tcp open mysql
Nmap finished: 1 IP address (1 host up) scanned in 0.064 seconds
[root@localhost ~]#
Best Answer
Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.
Please run this query:
USER() reports how you attempted to authenticate in mysqld
CURRENT_USER() reports how you were allowed to authenticate by mysqld
Sometimes,
USER()
andCURRENT_USER()
are different. That's because mysql authentication follows a specfic protocol.According to MySQL 5.0 Certification Study Guide
pages 486,487 state the following on mysql's authentication algorithm:
From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.
With regard to how you logged in, only
mysql -u a
worked. Go back and login again and run these commandsMake sure that
This is just a guess, but I suspect
mysql -u a
of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry inmysql.user
that allow anonymous localhost connection.Run this query:
If you get back a row with no password, that fully explains why
mysq -u a
works.UPDATE 2012-01-19 11:12 EDT
Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?
This question is an excellent heads up about MySQL user authentication.
Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user 'dbuser' to login locally (dbuser@localhost) using no password and the same user login from another server within a given netblock (dbuser@'10.1.2.20') with a password like 'pass1' and that user to login remotely from anywhere (dbuser@'%') with a remote password like 'pass2'.
Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.
This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process: