Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

When I try to add a new user with

grant all privileges on retrosias.* to 'rs3'@'%' identified by 'a';

I can't even connect locally :

ERROR 1045 (28000): Access denied for user 'rs3'@'localhost' (using password: YES)

If I add the same user using

grant all privileges on retrosias.* to 'rs3'@'localhost' identified by 'a';

then I can connect locally but my real need is to connect from any computer.

My server version is

Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)

And I verified that users added before can still connect from anywhere (so not a firewall problem).

Where's my error ? Or what could be a workaround ?

share|improve this question
    
Try "grant * on..." instead of "grant all privileges..." –  John Gardeniers Jan 10 '13 at 2:22

2 Answers 2

mysql> select User,Host,Password from user;
+------+------------+-------------------------------------------+
| User | Host       | Password                                  |
+------+------------+-------------------------------------------+
| root | localhost  | *62395BB52702DE50773EBF629DD4AE90F07FFD94 |
| root | sgeorge-mn |                                           |
| root | 127.0.0.1  | *62395BB52702DE50773EBF629DD4AE90F07FFD94 |
| root | ::1        |                                           |
|      | localhost  |                                           |
|      | sgeorge-mn |                                           |
| suku | localhost  | *EAF5C8242B88A14545BB61062D64CA5207DD1A37 |
| rs3  | %          | *0FDB28C86F3804FCA60FA633DB4264B0EB169D9B |
| rs3  | localhost  | *667F407DE7C6AD07358FA38DAED7828A72014B4E |
+------+------------+-------------------------------------------+
9 rows in set (0.00 sec)

It is necessary to have both (% and localhost) accounts for rs3 to be able to connect from anywhere as rs3. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when rs3 connects from the localhost. As a result, rs3 would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'rs3'@'%' account and thus comes earlier in the user table sort order.

About sort order:

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means "any host" and is least specific. The empty string '' also means "any host" but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific).

For more reading : http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

share|improve this answer
    
+1 for useful complementary information. Thanks. –  dystroy Jan 10 '13 at 7:34
up vote 0 down vote accepted

Rebooting the server did accomplish what neither flush privileges nor service stop mysql / service start mysql didn't.

All the (many) users I added before reboot are now accessible.

And the new users I add now with the same commands are accessible too.

I'm reluctant to accept my answer. I don't know if this is a known/frequent problem with mysql. The fact that I had to restart not only the service but also the whole computer is pretty disturbing.

share|improve this answer
    
I accepted this answer. But if somebody comes with a convincing bug report related to my problem, I'll be interested. –  dystroy Jan 15 '13 at 7:19

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.