Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use the root account created the account 'a'@'%'. But I can't use the it 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 ~]# 
share|improve this question
    
I am not a specialist in MySql, but I saw this problem few times, I don't know what was the reason, the solution was explicitly definition of host in addition to 'a'@'%'. so, the first record was 'a'@'%', and the second is 'a'@'localhost'. –  com Jan 18 '12 at 7:17
    
can you please test the same thing with lower mysql version..? –  Abdul Manaf Jan 18 '12 at 9:29
    
use Cpanel Login Details To connect. –  Abu Fahim Feb 6 '13 at 18:31

3 Answers 3

up vote 11 down vote accepted

Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.

Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

Sometimes, USER() and CURRENT_USER() are different. That's because mysql authentication follows a specfic protocol.

According to MySQL 5.0 Certification Study Guide

enter image description here

pages 486,487 state the following on mysql's authentication algorithm:

There are two stages of client access control:

In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which the clent connects, the username, and the password.

In the second stage (which occurs only if a client has already connected sucessfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.

The server matches a client against entries in the grant tables based on the host from which the client connects and the user the client provides. However, it's possible for more than one record to match:

Host values in grant tables amy be specified as patterns contains wildcard values. If a grant table contains entris from myhost.example.com, %.example.com, %.com, and %, all of them match a client who connects from myhost.example.com.

Patterns are not allowed for the User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:

In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.exmaple.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

In the User column, non-blank usernames sort ahead of blank usernames. That is, non-anonymous users sort ahead of anonymous users.

The server performs this sorting when it starts. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.

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 commands

SELECT USER(),CURRENT_USER();
SELECT user,host,password FROM mysql.user;

Make sure that

  • every user has a password.
  • there are no anonymous users (when user is blank)

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 in mysql.user that allow anonymous localhost connection.

Run this query:

SELECT user,host,password FROM mysql.user WHERE user='' AND host='localhost';

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:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

  • Set a password for the root accounts
  • Remove any remotely accessible root accounts.
  • Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
  • Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).
share|improve this answer
    
Yes I tried, it do return a row with no password. Thanks for your excellent explanation and recommended the MySQL certification book. –  Yousui Jan 19 '12 at 2:39
1  
Rolando, 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? –  Craig Efrein Jan 19 '12 at 6:42
    
@Craig - Your question is very noteworthy. I will transfer it into my answer and address it there. –  RolandoMySQLDBA Jan 19 '12 at 16:11
    
Thank you for the detailed answer, in my case I had anonymous users configured somehow. –  SoWeLie Sep 4 '13 at 16:40

Did you check to make sure that MySQL is actually listening on 3306? Run a netstat -tlpn and provide the results. If you don't see 3306 then its probably not.

In my.cnf you should verify that --skip-networking is commented out

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = 65.55.55.2
# skip-networking
share|improve this answer
    
I also did the same as asked in the question. and also did what you say in your answer but there is still the same issue. –  Abdul Manaf Jan 18 '12 at 7:26
    
Can you provide the results from the following query: select user, host from mysql.user; –  Craig Efrein Jan 18 '12 at 7:32
    
Yes, MySQL is listening on port 3306. See my edit. –  Yousui Jan 18 '12 at 7:35
    
You also may want to try mysql -u user -p -h 127.0.0.1. If that works then I believe its mysql that doesn't know how to resolve localhost. An entry for localhost that points to 127.0.0.1 in your /etc/hosts file will resolve that. –  Craig Efrein Jan 18 '12 at 7:37
    
Did you run Flush Privileges? –  Craig Efrein Jan 18 '12 at 7:41

The % host wildcard doesn't match 'localhost'. By default the mysql client will try to connect via a socket rather than tcp (usually some place like /var/lib/mysql/mysql.sock). You can either change your grant to 'a'@'localhost', or force the client to operate over the TCP stack like

mysql -u a -p --proto=TCP
share|improve this answer
    
I tried, but still of no luck. –  Yousui Jan 18 '12 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.