Home Technology MySQL Database How to Grant Remote Access to a MySQL Database

Tags: Cyber Security , Database Administration , MySQL

So you’ve just created a user in MySQL, but the user cannot log in, even when using the correct password. What’s the problem?
Generally MySQL users can access the server from the server itself, or from the same network subnet. But remote access to MySQL (through the Internet, or from another private network) must be granted as through a special process.
Granting Remote Access to MySQL:
An administrator must grant permission for the user to access the server from all of the remote locations where the user is authorized.
Follow these steps:
1. Log in to MySQL as an administrator. This must be done by command line. Enter the following command from a shell prompt:
mysql -u admin –p
Enter the admin password when prompted.
2. From the MySQL prompt, grant the user access to a specific database schema. The format of the command to use is:
GRANT ALL PRIVILEGES ON database.* to ‘user’@'yourremotehost' IDENTIFIED BY 'newpassword';
Where:
‘user’ is the user name of an existing MySQL account. The username is enclosed in single quotes, as shown.
database is the name of the database schema where access will be granted. Either the name of the schema can be used, or an asterisk (*) can be used to specify all databases.
‘yourremotehost’ contains either an IP address where the user will access from, or a domain name. The IP address or host name must appear in single quotes, as shown.
‘newpassword’ contains the password the user must use to access the server. The password must appear in single quotes as well.
See Examples.
3. To force the changes to take effect immediately, enter the following command::
FLUSH PRIVILEGES;
More examples:
Granting access for the user jsmith from an IP address:
GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';
Granting access from a domain:
GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'%.mycompany.com' IDENTIFIED BY 'jimspassword';
Granting access to all schemas:
GRANT ALL PRIVILEGES ON *.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';
Granting limited access rights:
GRANT ALL PRIVILEGES ON *.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';
Granting access from a specific host name on a domain:
GRANT ALL PRIVILEGES ON *.* to jsmith@'jimspc.mycompany.com' IDENTIFIED BY 'jimspassword';
A note on exactly what are you granting when you GRANT ALL?

 

By Andrew B. Bartels, September 2010

 

So you’ve just created a user in MySQL, but the user cannot log in, even when using the correct password. What’s the problem?

Generally MySQL users can access the server from the server itself, or from the same network subnet. Remote access to MySQL (through the Internet, or from another private network) must be granted as through a special process.


Granting Remote Access to MySQL:
The database administrator grants permission for the user to access the server from all of the remote locations where the user is authorized.

A detailed discussion of remote access is found in the MySQL 5.1 Reference Manual, however, some simplified steps are here:



1. Log in to MySQL as an administrator. This must be done by command line. Enter the following command from a shell prompt:


mysql -u admin –p

 

Enter the admin password when prompted.


2. From the MySQL prompt, grant the user access to a specific database schema. Some detailed examples are below, but the general format of the command to use is:


GRANT ALL PRIVILEGES ON database.* to ‘user’@'yourremotehost' IDENTIFIED BY 'newpassword';

 

Where:


‘user’ is the user name of an existing MySQL account. The username is enclosed in single quotes, as shown.


database is the name of the database schema where access will be granted. Either the name of the schema can be used, or an asterisk (*) can be used to specify all databases.


‘yourremotehost’ contains either an IP address where the user will access from, or a domain name. The IP address or host name must appear in single quotes, as shown.


‘newpassword’ contains the password the user must use to access the server. The password must appear in single quotes as well.


See some examples, below.


3. To force the changes to take effect immediately, enter the following command:


FLUSH PRIVILEGES;


Specific Examples:

 

Example A: Granting access for the user jsmith from an IP address:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';


Example B: Granting access from a domain:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'%.mycompany.com' IDENTIFIED BY 'jimspassword';


Example C: Granting access to all schemas:

GRANT ALL PRIVILEGES ON *.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';


Example D: Granting access from a specific host name on a domain:

GRANT ALL PRIVILEGES ON *.* to jsmith@'jimspc.mycompany.com' IDENTIFIED BY 'jimspassword';