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?

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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