How to Apply Secure WordPress MySQL Database Privileges

Last updated on December 06th, 2014 by Robert Abela. Filed under WordPress Security Hacks

As seen in Why minimum MySQL user WordPress database privileges improve security, it is very important to assign the minimum required database privileges to the MySQL user being used by WordPress to access the MySQL WordPress database, i.e. the user specified in WordPress wp-config.php file.

It is acceptable to allow ALL database privileges to the MySQL user you will be using in the WordPress wp-config.php file during installation. Though this should be just a temporary measure until the WordPress installation creates all the necessary tables and other database objects in the MySQL database for WordPress to function.

Secure WordPress MySQL database user privileges

For normal WordPress blog or website operations, such as posting blog posts, uploading media files, posting comments, creating new WordPress users and installing WordPress plugins, the MySQL user used from WordPress to connect to the MySQL database only needs DATA READ and DATA WRITE access to the MySQL databases. The MySQL user does not need to alter the database structure or any type of administrative permissions on the database, such as Grant.

How to assign DATA READ and WRITE only database privileges

Edit database privileges of the MySQL user with phpMyAdmin

Once logged in to the MySQL phpMyAdmin web interface (accessible via most hosting providers Cpanel), click on the Users tab and then on the Edit Privileges of the MySQL user you are using to access the WordPress MySQL database as highlighted in the below screenshot.

edit mysql database user privileges from phpMyAdmin

Select the WordPress MySQL database to assign privileges to the user

Once in the users’s privileges window, scroll down to the section Database-Specific privileges and from the drop down menu select the WordPress MySQL database. Click on Go. In the below example the WordPress MySQL database is ‘pr34r8ijf21’.

MySQL user Select database specific privileges

Assign the user privileges to the WordPress MySQL database

For normal WordPress operation the MySQL user only needs to read and write data from and to the database. So in this case only allow the below list of permissions under the data column, as seen in the below screenshot:

  • Select
  • Insert
  • Update
  • Delete

assinging MySQL database specific permissions

Once you assign the required privileges, click on the Go button to apply the privileges.

Assign MySQL user privileges using MySQL command line

If you are using MySQL command line, you can use the below SQL syntax to assign data read and data write only privileges to the user which WordPress will be using to access the MySQL database, i.e. the user specified in the WordPress wp-config.php file.

GRANT SELECT , INSERT , UPDATE , DELETE ON  `[DATABASE]` . * TO  ‘[USER]’@'[HOST]’;

Replace [DATABASE] with the database name, [USER] with the MySQL username and [HOST] with the host, typically localhost.

Assign READ and WRITE ONLY user privileges using Cpanel

Once you login to your web hosting provider Cpanel, scroll down to the Databases section and click on MySQL databases. Once the list of MySQL databases and usernames accessing such databases loads up, click on the username being used to access the WordPress MySQL database to proceed and change the user database privileges. As usual, allow only Select, Insert, Update and Delete as shown in the below screenshot.

Assigning data read and write permissions to MySQL user on database from Cpanel

Once ready click on Make Changes button at the bottom of the privileges table to apply the changes.

MySQL user database privileges for updating WordPress

When upgrading a WordPress installation, the upgrade process might need to change the WordPress MySQL database structure. In this case it is safe to grant all structure related privileges to the MySQL user being used by WordPress during the WordPress upgrade process only. Once the upgrade is successful, you can revert back the changes.

Special MySQL user database privileges to install plugins and themes

Although it is not so common, some WordPress plugins and WordPress themes might need to alter the WordPress MySQL database structure and objects, such as add tables, routines or views. In this case the above configured database permissions (data read and write only) need to be altered for such WordPress plugins and themes to be installed. Ideally you should refer to the WordPress Plugin or Theme documentation, or contact the developer to see what MySQL WordPress database privileges the MySQL user needs to install such components.

Once the WordPress plugin or WordPress theme is installed, and the required changes to the MySQL database have been applied, you can revert back to data read and data write only permissions.

WordPress Professional Security Services and Consultancy

Our team is experienced in web security and focuses on WordPress security. If you have any WordPress security queries, need to clean your website from malware or beef up the security of your WordPress, drop us an email.

WordPress Hosting, Firewall and Backup

WP White Security is hosted on A2 Hosting, protected with BBQ:Block Bad Queries Firewall and backed up with BlogVault online WordPress backup service

Leave a Reply

Your email address will not be published. Required fields are marked *