There might be cases where you need to allow remote access to the WordPress database, i.e. grant access to a process or user running on a remote computer. For example if you would like to extract the WordPress Security Alerts generated by WP Activity Log plugin to a centralized logging system you have to allow the remote system to extract the alerts from the WordPress database. In such case you need to create a new user and grant the user access to the database and allow it to connect from a remote location.
This article explains how to allow or grant remote access to a MySQL database or table in a database by using MySQL command line or the web based interface phpMyAdmin.
Allow Remote Access to MySQL Database via SQL Command Line
Below are three different examples of how to allow remote access to a MySQL database using the SQL command line.
Allow Full Remote Access to a WordPress Database
In the below example we will create a MySQL user wpuser with password Pa55w0rd! and allow the user full access to the MySQL database wpdb from the IP address 192.168.2.25.
- Login to your MySQL server using the command mysql –u root –p
- Enter the root password once prompted and issue the following command:
GRANT ALL ON wpdb.* TO wpuser@`192.168.2.25` IDENTIFIED BY `Pa55w0rd!`;
Allow Read Only Remote Access to a WordPress Database
In the below example we will create a MySQL user wpuser with password Pa55w0rd! and allow the user read only access to the MySQL database wpdb from the IP address 192.168.2.25.
- Login to your MySQL server using the command mysql –u root –p
- Enter the root password once prompted and issue the following command:
GRANT SELECT ON wpdb.* TO wpuser@`192.168.2.25` IDENTIFIED BY `Pa55w0rd!`;
In the above command we are granting SELECT privilege only rather than ALL as in the previous example, hence the user can only read the data.
Allow Read Only Remote Access to a Table in a WordPress Database
In the below example we will create a MySQL user wpuser with password Pa55w0rd! and allow the user read only access to the table wp_users in the MySQL database wpdb from the IP address 192.168.2.25.
- Login to your MySQL server using the command Mysql –u root –p
- Enter the root password once prompted and issue the following command:
GRANT SELECT ON `wpdb`.`wp_users` TO 'wpuser'@'192.168.2.25' IDENTIFIED BY `Pa55w0rd!`;
In the above example we are granting SELECT privilege, i.e. read only to the table wp_users in the database wpdb (`wpdb`.`wp_users`).
Allow Remote Access to MySQL Database via phpMyAdmin
Below are three different examples of how to allow remote access to a MySQL database using phpMyAdmin, a web based MySQL administration tool.
Allow Full Remote Access to a WordPress Database
In the below example we will create a MySQL user wpuser with password Pa55w0rd! and allow the user full access to the MySQL database wpdb from the IP address 192.168.2.25.
- Login to your phpMyAdmin web interface and select the WordPress database
- Click on Privileges tab and click Add User
- Specify the following details in the Login Information section:
- User name: wpuser
- Host: 192.168.2.25
- Password / Re-type: Pa55w0rd!
- Select Grant All privileges on database “wpdb”
- Click Add User
Allow Remote Read Only Access to a Database
In the below example we will create a MySQL user wpuser with password Pa55w0rd! and allow the user read only access to the MySQL database wpdb from the IP address 192.168.2.25.
- Login to your phpMyAdmin web interface and select the database
- Click on Privileges tab and click Add User
- Specify the following details in the Login Information section:
- User name: wpuser
- Host: 192.168.2.25
- Password / Re-type: Pa55w0rd!
- Tick None in the Database for user section
- Tick Select in the Global privileges section
- Click Add User
Allow Remote Read Only Access to a Table in a Database
In the below example we will create a MySQL user wpuser with password Pa55w0rd! and allow the user read only access to the table wp_users in the MySQL database wpdb from the IP address 192.168.2.25.
- Login to your phpMyAdmin web interface and open the Users tab
- Click Add User and specify the following details in the Login Information section:
- User name: wpuser
- Host: 192.168.2.25
- Password / Re-type: Pa55w0rd!
- Tick None in the Database for user section
- Click Add User
- Once the user is created click on Edit Privileges to edit the privileges of that user as highlighted in the below screenshot
- From the drop down menu in the Database-specific privileges section select the database, in our case wpdb and click Go
- Select the table wp_users from the drop down menu in the Table-specific privileges section as shown in the below screenshot
- Highlight all the columns in the Select column to grant read only access to those fields as per the below screenshot
- Click Go to grant read only access to all the fields in that specific table.
Adequate Security Precautions When Granting Remote Access to a Database
When granting remote access to a WordPress or any other MySQL database it is important to use very strong credentials, i.e. username and password. It is also very important to only grant the required privileges. A common mistake many administrators do when granting remote access to a MySQL database, and because “it always works” is to grant full access, i.e. read, write and modify to the data and also to the database structure. You should avoid granting full privileges they are really required. To avoid granting full privileges, and to ensure the security of your WordPress and its MySQL database check what privileges are required, do some testing and assign only the required privileges.
Last but not least, as we have seen in the above example when granting remote access to a WordPress or any other MySQL database restrict access by IP address, i.e. the source IP address from where the user or application will be connecting. Therefore as a rule of thumb, always restrict access or privilege to the least possible.
