When working with MySQL user privileges, many developers encounter this confusing scenario where wildcard host access (%) doesn't properly cover localhost connections. Let's break down why this happens and how to properly configure user permissions.

MySQL treats 'localhost' as a special case in its authentication system. When you create a user with '%' host specification, it doesn't automatically include 'localhost' due to MySQL's internal host resolution mechanisms. This is a common gotcha that trips up many developers.

Here's the correct way to create a user that can connect from both remote hosts and localhost:

-- Create user for remote connections
CREATE USER 'someuser'@'%' IDENTIFIED BY 'password';

-- Create user for localhost connections
CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'same_password';

-- Grant privileges to both
GRANT ALL PRIVILEGES ON somedb.* TO 'someuser'@'%';
GRANT ALL PRIVILEGES ON somedb.* TO 'someuser'@'localhost';

FLUSH PRIVILEGES;

After setting up your users, verify the configuration with:

SELECT user, host FROM mysql.user WHERE user = 'someuser';
SHOW GRANTS FOR 'someuser'@'%';
SHOW GRANTS FOR 'someuser'@'localhost';

For development environments, you can modify MySQL's configuration to skip hostname resolution:

-- Add to my.cnf or my.ini
[mysqld]
skip-name-resolve

Note: This disables hostname resolution for all connections, which might affect other functionality in production environments.

Here are proper connection commands for different scenarios:

# Localhost connection
mysql -u someuser -h 127.0.0.1 -p

# Remote connection
mysql -u someuser -h server_ip -p

• Using different passwords for '%' and 'localhost' accounts
• Forgetting to FLUSH PRIVILEGES after changes
• Mixing IPv4 (127.0.0.1) and 'localhost' connections
• Not verifying user privileges with SHOW GRANTS

For production systems, it's recommended to:

1. Use specific IP addresses instead of '%' where possible
2. Implement proper firewall rules
3. Consider using SSH tunneling for remote access
4. Regularly audit user privileges

When MySQL handles authentication, it evaluates host patterns in a specific order. The wildcard (%) doesn't automatically include localhost because MySQL treats localhost connections specially (often via UNIX sockets rather than TCP/IP).

MySQL checks user accounts in this priority:

1. 'someuser'@'localhost'
2. 'someuser'@'127.0.0.1'
3. 'someuser'@'%'

This means if any localhost entry exists (even with no privileges), it will block the wildcard entry.

To properly configure access from both localhost and remote hosts:

-- First create both user entries
CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'someuser'@'%' IDENTIFIED BY 'password';

-- Then grant privileges to both
GRANT ALL PRIVILEGES ON somedb.* TO 'someuser'@'localhost';
GRANT ALL PRIVILEGES ON somedb.* TO 'someuser'@'%';

-- Finally flush privileges
FLUSH PRIVILEGES;

Verify your setup works from all connection methods:

# TCP/IP connection
mysql -u someuser -p -h 127.0.0.1

# Socket connection
mysql -u someuser -p

# Remote connection (if applicable)
mysql -u someuser -p -h your_server_ip

The passwordless access occurs because MySQL may have an anonymous user account ('@'localhost) with higher priority. Check with:

SELECT User, Host FROM mysql.user WHERE User = '';

Remove anonymous accounts if found:

DROP USER ''@'localhost';