Why Do I Get 'Host 'xxx.xx.xxx.xxx' is Not Allowed to Connect to This MySQL Server' Even with a '%' Host Entry?

Remote MySQL Connection Error: "Host Not Allowed"

Connecting remotely to MySQL can sometimes result in the error message "Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server." Despite having a root user entry with a host value of both 'localhost' and '%', the connection fails.

This issue is typically encountered due to security measures implemented by MySQL. One possible solution is to create a new administrative user with specific privileges and access restrictions:

Creating a New Administrative User:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' ->     WITH GRANT OPTION;

While granting extensive privileges is convenient, it is not recommended due to security risks. Instead, it's prudent to:

• Limit user access to the specific hosts and IPs required.
• Grant only the necessary privileges for the task at hand.

MySQL FAQ Insight:

The MySQL FAQ provides further clarification on this issue:

"If you cannot figure out why you get 'Access denied', remove all user table entries that have host values containing wildcards (e.g., '% or _'). It's common to accidentally insert an entry with host='%' and user='some_user' to enable localhost connections. However, it won't work because the default privileges include an entry with host='localhost' and user=''. The localhost entry is more specific and will be used instead. To allow localhost connections, insert a second entry with host='localhost' and user='some_user', or delete the default localhost entry and issue a FLUSH PRIVILEGES statement."

The above is the detailed content of Why Do I Get 'Host 'xxx.xx.xxx.xxx' is Not Allowed to Connect to This MySQL Server' Even with a '%' Host Entry?. For more information, please follow other related articles on the PHP Chinese website!