Why Am I Getting the 'Host 'xxx.xx.xxx.xxx' is Not Allowed to Connect' MySQL Error?

Troubleshooting "Host 'xxx.xx.xxx.xxx' is Not Allowed to Connect" Error

Problem:
When attempting to connect to a MySQL server remotely, users encounter the error "Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server," despite having the appropriate user permissions in the database.

Possible Cause:

This error often stems from security precautions or misconfigured user permissions.

Solution:

1. Add a New Administrator Account:

Create a dedicated administrator account with restricted access:

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

2. Modify Existing User Permissions:

Edit the user permissions for the existing 'root' user, granting it fewer privileges and restricting access to a specific host:

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
mysql> GRANT SELECT, UPDATE ON table_name TO 'root'@'specific_ip';

3. Remove Wildcard Permissions:

Delete any user entries in the database that contain wildcard characters (%) or (_). Replace them with specific host values.

4. Flush Privileges:

After making any changes to user permissions, remember to issue a FLUSH PRIVILEGES statement to refresh the grant tables:

mysql> FLUSH PRIVILEGES;

Note:

It is generally not advisable to grant excessive privileges (e.g., ALL PRIVILEGES ON .) to users who require only limited access. Consider granting only the minimum permissions necessary for their specific roles.

The above is the detailed content of Why Am I Getting the 'Host 'xxx.xx.xxx.xxx' is Not Allowed to Connect' MySQL Error?. For more information, please follow other related articles on the PHP Chinese website!