How to Grant Remote Access to MySQL Users?

Granting Remote Access Permissions to MySQL Server

Users with access to a MySQL database may encounter restrictions when attempting to connect from specific locations. By default, user privileges may be limited to connections originating from the localhost, preventing remote access. To address this, administrators can grant remote access permissions to designated users, allowing them to establish connections from different machines within the same network.

Method:

To grant remote access permissions, perform the following actions:

1. Identify the User and Database:

   • Connect to the MySQL server and check the privileges of the user in question using the SHOW GRANTS command. This command will display the current access permissions for the user and their corresponding hosts.

2. Grant Remote Privileges:

   • To grant remote access privileges, use the GRANT statement with the appropriate syntax. The general format is:

     GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password' WITH GRANT OPTION;

   • Replace username with the user to be granted remote access.
   • For hostname, specify the wildcard character * to grant access from any hostname. Alternatively, provide a specific host or IP address to limit the access.
   • password represents the password that the user will use to authenticate their connection.

3. Flush Privileges:

   • After granting the privileges, execute the FLUSH PRIVILEGES command to apply the changes and update the privileges table. This step ensures that the new settings take effect immediately.

Example:

To grant root access to connect from any machine in the *.example.com domain, use the following command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%.example.com' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Alternatively, to grant root access from a specific IP address or subnet, use the following command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The above is the detailed content of How to Grant Remote Access to MySQL Users?. For more information, please follow other related articles on the PHP Chinese website!