How to Apply Secure WordPress MySQL Database Privileges

Optimizing the WordPress database user permissions isn’t typically on many WordPress administrators’ priority lists. Yet, as any administrator will tell you, WordPress is only as secure as its weakest link. Measures like firewalls and WordPress 2FA often take the limelight. However, ensuring MySQL user permissions follow the principle of least privilege is a best-practice security measure that can save you a lot of heartache.

In this article, we will go through everything you need to know about MySQL user permissions. We will pay particular attention to how this relates to WordPress. We will also include actionable how-tos that’ll help you increase the security of your WordPress database.

What are MySQL database users?

Database users are very different than WordPress users. Database users are confined to MySQL and are essentially used for reading and writing data from/to the database. Depending on the permissions assigned, the user may also be able to create databases, delete them, and a whole raft of other activities.

WordPress users, on the other hand, are confined to WordPress. They are used to publish content, install and uninstall plugins, manage users, leave comments, make purchases, and much more (or less) – depending on the permissions assigned to them.

WordPress user roles facilitate the management of such users and their permissions. However, WordPress users cannot interact with or access the database.

WordPress saves certain settings and data to the database and, as such, needs to be able to read and write to the database. It accomplishes this through a database user, which needs to have the required permissions to carry out these tasks. This user is configured in the wp-config.php configuration file.

Most of the time, the MySQL user used by WordPress needs read and write access only. However, you’ll find that in many installations, this user has full access to the WordPress MySQL database or, even worse, to the whole MySQL database server.

To keep WordPress as secure as possible, the MySQL user’s WordPress database privileges should be restricted. The MySQL user accessing the WordPress MySQL database should be assigned the minimum required database privileges (database permissions).

Why restrict MySQL user privileges?

Imagine accidentally installing a malicious plugin that has a backdoor or trojan software. Or, a malicious user gets hold of the MySQL user credentials for WordPress. In both cases, if the MySQL user’s privileges are limited to just the WordPress database, any damage will be limited and contained.

At the risk of stating the obvious, a malicious user gets access to the privileges of the account they compromise. So, if the MySQL user has full access to the WordPress database, including structure privileges, they can alter the data and the structure. It’s a bigger issue if the user account has access to other databases. This would allow the attacker to access, steal, and modify data in those databases, too. With rules and standards like GDPR and PCI DSS placing great emphasis on user data protection, any such breaches can potentially land you in hot water.

Understanding MySQL user privileges

The principle of least privileges tells us that users should have the least possible privileges to carry out their tasks. In the context of WordPress MySQL user, this is READ and WRITE privileges. However, there are scenarios where additional privileges might be required to carry out a task, such as updating the WordPress database.

As such, it is worth taking some time to understand how MySQL privileges work. This will help you ensure you are able to respond to different situations and scenarios without taking on additional risk.

Levels of privilege operations

MySQL levels of privilege operations represent the type of operations privileges afford the user. To this end, you’ll find three different operation levels:

Administrative privileges: Administrator privileges are not tied to any one database. Rather, they provide the user permissions to manage the global operations of the MySQL server.

Database privileges: Database privileges provide the user with permissions over the database and all of its child objects. They can be applied to individual databases or all databases.

Object privileges: Object privileges provide the user with access to objects within a specific database.

Static vs. dynamic privileges

MySQL privileges can be either static or dynamic. Static privileges are persistent privileges that are defined at the server level and are always available. On the other hand, dynamic privileges must be registered at runtime, after which they can be assigned. In most cases, you’ll only be dealing with static privileges.

MySQL user roles

Just like WordPress user roles, MySQL can leverage user roles to facilitate privilege management. There are no default user roles, so you’ll need to set these up yourself. Fortunately, the procedure is very easy – and will make subsequent user rights assignments much easier and quicker.

MySQL user roles enable us to create multiple roles, with each user role having enough privileges to carry out its tasks – but not more. In doing so, we can better manage risk and thus improve the security of our WordPress websites.

Secure WordPress MySQL database user privileges

For regular WordPress tasks like posting blogs, uploading media, posting comments, adding users, or installing plugins, the MySQL user only needs DATA READ and DATA WRITE access to the database. It doesn’t need permissions to change the database structure or have administrative rights like GRANT.

However, certain updates may require additional privileges. Plugin updates, for example, may include updates to the structure of the tables they use. WordPress itself might also retire and introduce different tables throughout its lifetime. In such cases, the user will need more than DATA READ and DATA WRITE, which necessitates the implementation of user roles to manage access privileges.

Why create custom user roles for MySQL

As mentioned earlier, in day-to-day activities, the MySQL user used by WordPress needs no more than READ and WRITE privileges. This will be true for 99% of the time. Yet, when a big update comes our way, we will need to escalate the privileges. This ensures updates can go through.

Of course, we can have one user with all privileges. As covered earlier in the article, this is the least safe option. We can also have two different users, each with different privileges and then we can switch between them. While this might be safer than the previous option, more users exposes us to increased risk. As such, the safest option is to have one user and multiple roles.

This will make the process of managing user rights much easier. As such, we’ll be creating two user roles. The first role will have all privileges while the second role will only have read and write privileges.

Plan of attack

As previously mentioned, MySQL does not have existing user roles you can assign to users. As such, we’ll need to create them ourselves. Fortunately, the process is very straightforward and can be executed using different methods.

First, we are going to create two new roles – one with elevated privileges and one without.

The user role with restricted privileges will have:

• SELECT
• INSERT
• UPDATE
• DELETE

On the other hand, the role with elevated privileges adds:

• CREATE
• ALTER
• DROP

We will then create a new MySQL user that our WordPress site will use to interact with the MySQL database.

Once we have the roles and the user, we will assign the user the role with restricted privileges and then update the wp-config.php file.

This gives us the result we are looking for – a WordPress MySQL user with limited privileges. When looking to install major updates, all we need to do is assign the elevated role to our user, without having to update the wp-config file. Once updates are ready, we can then revert the user to the role with restricted permissions.

Creating and assigning roles in MySQL command line

Using the command line might sound daunting, but as you’ll see throughout this exercise, it is very straightforward. If you have never accessed the command line before, we suggest trying this out on a testing server. This will help you familiarize yourself with the process.

The MySQL command line can be accessed through SSH. Some hosting providers offer a web SSH client. Alternatively, you can used an SSH client such as PuTTY.

Login in with your server username and password, and then type:

sudo mysql

This will open the MySQL command prompt as root, which, in turn, will allow us to interact with the MySQL server.

Creating the roles

Next, we are going to create two new roles. For the purpose of this tutorial, we will be naming the roles wordpress_admin and wordpress_user. However, you can name these whatever you like; adminlovespizza works just as fine. The only important thing is to make sure you can easily differentiate the two roles – the one that has extra privileges and the one that does not.

Type:

CREATE ROLE 'wordpress_admin@localhost', 'wordpress use@localhost';

This should return Query OK, as shown in the screenshot below:

Assigning privileges to roles

Next, we are going to grant each of these roles the required privileges. One role will be limited to selecting (reading), inserting, updating, and deleting records. The other role adds the alter and drop privileges. These extra privileges allow for altering the table structure and dropping tables, respectively.

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROP ON wordpress.* TO 'wordpress_admin';

GRANT SELECT,INSERT,UPDATE,DELETE ON wordpress.* TO 'wordpress_admin';

The above commands assume that the WordPress database is called wordpress. If the database is named differently, replace wordpress with the database name.

Create user and assign role

You can safely use the user that’s already configured in your wp-config.php file. However, we’ll go through the process of creating a new user for those who are installing a fresh WordPress installation.

CREATE user@host user IDENTIFIED BY 'password';

Where: 

• user is the username of the user
• host is the hostname of the MySQL machine (localhost in most cases) 
• password is a secure password for the user.

All that’s left to do now is to assign the user the role we created in the previous step:

GRANT 'wordpress_user' TO 'username';

And finally, flush privileges for the changes to take effect:

FLUSH PRIVILEGES;

If you create a new MySQL user but already have one configured in the wp-config.php file, you can disable the user used previously using the following command:

ALTER USER 'username'@'host' ACCOUNT LOCK;

Where username is the user you want to lock and host is the hostname of the server (this is usually localhost).

Changing roles

When you need to change the user’s role, all you need to do is log back into MySQL and run the role assignment command as follows:

GRANT 'role' TO 'username';

Where role is the name of the role you want to assign, and username is the user you want to assign the role to. Remember to run the flush privileges command at the end for changes to take effect.

Creating and assigning roles in phpMyAdmin

phpMyAdmin offers a friendly Graphical User Interface (GUI) to manage several aspects of your MySQL server. While you can use the phpMyAdmin GUI to create databases and users, there is no graphical interface for creating roles.

However, we can use the SQL option to run queries just like we would in the MySQL CLI.Log in to phpMyAdmin and then select the WordPress database from the right. Next, click on the SQL tab as highlighted in the screenshot below and follow the directions provided in the Creating and assigning roles in MySQL command line section, starting from Creating the roles.

Little things that add up

Securing your WordPress website can feel like it takes a lot of effort. While you might be tempted to install plugins that focus on security and call it a day, this is not the answer to a truly secure website. Plugins and themes might inadvertently include a security hole, which is why a more comprehensive approach can help you ensure you’re always as secure as can be.

Using SQL commands can feel daunting, but as this article has shown, it is easier than you might think. As always, it is best to practice on a testing site until you feel comfortable working on your live site. The result is well worth the effort.