1

It seems that the solution to accepting users from localhost is to create a copy of the user with @'localhost' (in addition to user@'%'). I'm trying very hard to understand the logic here, because it seems kind of bat-nuts crazy that the 'any host' wildcard, '%', wouldn't also accept localhost connections. Creating a second user isn't very practical when dealing with a large number of users. If a user changes their own password, it would then leave the other one unaffected.

Is there any sort of workaround to allowing user@'%' to accept localhost connections?

Talik
  • 93
  • 1
  • 7

2 Answers2

3

The localhost style of connecting to mysqld forces the mysql client to authenticate via the socket file.

Connecting via the wildcard % demands the connecting to mysqld via TCP/IP.

If have myuser@'%' with a password of mys3cr3t, and you do not want to add myuser@'localhost', I would like to suggest the following:

Connect to mysql like this

mysql --protocol=tcp -umyuser -pmys3cr3t

Using --protocol=tcp forces the mysql client program to use the TCP/IP to connect

Once you connect, run this query

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

I wrote about this in the DBA StackExchange back on Jan 18, 2012.

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
2

You need to remove the anonymous users, as MySQL's pattern matching will match them first.

Run the mysql_secure_installation script or remove them manually.

Michael Hampton
  • 237,123
  • 42
  • 477
  • 940