WordPress, the most popular CMS, runs on MySQL, the most popular database out there. Spending some time to ensure your MySQL installation and WordPress database configuration installation is adequately hardened against common attack vectors can help you reduce risks. This is especially true if you are managing your MySQL server yourself.
It is worth noting that many WordPress installations use MariaDB, which is a fork of MySQL. As both work very similarly, we will use MySQL to mean both MySQL and MariaDB. Regardless of which RDMS flavor you’re running, hardening your MySQL can help you minimize the risks of attacks from hackers. However, this does not replace other security measures, such as installing a web application firewall, ensuring you have the latest version of plugins, themes, and WordPress, and hardening WordPress.
Heads up, this article is targeted at MySQL 8.0 running on Linux (Ubuntu). While the concepts will translate to other operating systems and MySQL/MariaDB versions, the commands and file paths used in these examples may differ. Before making any changes to a production system, it is highly advised to test any changes in a staging or pre-production environment.
In this article, which is primarily aimed at those managing their own MYSQL, we offer several tips and tutorials on how to secure MySQL. Even so, the extensive list of best practices presented in this article is worth a read for anyone managing WordPress websites. Securing your MySQL server is a critical step in maintaining a secure WordPress, and protecting yourself from different types of brute force attacks, malware injection, and other types of attacks.
Table of contents
Consider using a Database as a Service (DBaaS)
Database as a Service is well worth considering if you’re not hosting WordPress on a managed plan. It replaces the traditional model of installing MySQL locally with a service you connect to. This might suit your use case if you are running your WordPress site with a hosting provider that offers managed database services. Available options generally include Amazon RDS, DigitalOcean Managed MySQL, and Linode Managed MySQL). At face value, these services can be costlier than running MySQL yourself. However, they do all the heavy lifting of running production-grade databases. Most services include security best practices presets, ongoing security patches and maintenance, and backups.
Using a Database as a Service (DBaaS) is one of the best options in terms of security and reliability. While this is not mandatory, it’s still a good-to-have. However, if you are looking to manage MySQL yourself, the following is a collection of hardening tips to keep in mind.
Keep MySQL up-to-date
Just as it’s important to ensure you’re running the latest version of WordPress, it’s important to keep MySQL up-to-date. Like most other software, updates to the MySQL server are released periodically. These updates address bugs, mitigate vulnerabilities, and provide new features. You should keep MySQL up-to-date with the latest security patches to reduce the risks of running software with known vulnerabilities. Bear in mind that once updated, you will be required to restart the ‘mysql daemon.’ This is a process that may incur some downtime. As always, plan ahead.
Run MySQL on a dedicated machine
Many WordPress installations run MySQL, PHP, and a web server (such as Nginx or Apache HTTP Server) on the same machine. This is not optimal – both in terms of performance and security. MySQL should ideally run on a dedicated server to reduce the blast radius of an attack. If an attacker manages to compromise and escalate privileges on the web server, it would be much harder for that attacker to move laterally and also compromise the MySQL server.
Bind MySQL to an IP address
You can configure MySQL to only accept TCP/IP connections from a specific IPv4 or IPv6 interface. All you need to do is set the bind-address configuration option to a specific IP address. This provides additional controls and restrictions on how client applications (in our case, WordPress) can connect to MySQL. By default, this setting is set to *, meaning that out-of-the-box MySQL will listen on all interfaces.
If not configured to listen to a specific IP, all IPs can be used to connect to MySQL. This setting is especially important to set if you are running MySQL on the same machine as a web server that you are exposing to the Internet (in this case, you should set the bind-address to 127.0.0.1 so MySQL only listens on localhost).
For example, if you want the MySQL server to only accept connections on a specific IPv4 address, you can add an entry similar to the example below. You should enter this under the [mysqld] option group in your server’s /etc/mysql/mysql.conf.d/mysqld.cnf configuration file.
bind-address=192.168.0.24
Note that once you set this, you will need to reconfigure WordPress to connect to the database using this IP address (unless it’s doing so already) since connections on other server host addresses would not be permitted.
Many WordPress installations include web-based front-end graphical management tools. Common examples include Cpanel, phpMyAdmin, or Adminer. These tools make it easier to manage MySQL and other aspects of the underlying infrastructure. While a web-based graphical interface can help you manage your MySQL databases, these interfaces can increase the attack surface by adding another vector. Furthermore, there is a risk that they’ll be discovered and abused by attackers to run destructive or malicious SQL queries against your database. Attacks may even result in a full takeover of your WordPress website.
The only safe server is the one that’s switched off and unplugged – however, risk can be managed. Uninstalling non-critical systems is one option; however, these can also be locked down and restricted to minimize the risk.
It is possible to restrict access to these tools in a variety of ways. You can install phpMyAdmin for WordPress remotely, thus minimizing risk to the web server. Alternatively, you might also want to consider using tools such as MySQL Workbench or Beekeeper Studio on your local machine and connect to your database server over an SSH tunnel.
Run the MySQL daemon using a dedicated user
As with other services running on a server, you can run the MySQL daemon under a dedicated user. When you run MySQL using a dedicated user, you can precisely define what permissions that user is given within the system. Running MySQL under a dedicated user also follows the principle of least privilege since this reduces the blast radius of a MySQL vulnerability. It also decreases the possibility of a misconfiguration being taken advantage of since a restricted user will be unable to access resources unrelated to MySQL (such as operating system configurations and secrets).
The good news is that installations via package managers (such as apt or yum) take care of this step automatically when installing MySQL. A quick way to verify if MySQL is running under a dedicated user is to run the following on the machine running the MySQL daemon.
ps -ef | egrep “^mysql.*$”
If MySQL is running using a dedicated user, you should expect to see at least one line from ps’s output returned.
Use the mysql_secure_installation script
The mysql-server package comes with a shell script utility called mysql_secure_installation. You can use this script to set up a secure starting point for the MySQL server. As such, you should run it after a fresh install of MySQL. This utility helps you:
- Set a password for root accounts
- Remove root accounts that are accessible from outside localhost
- Remove anonymous user accounts
- Remove the test database (which, by default, can be accessed by anonymous users)
To invoke mysql_secure_installation, run the following command:
sudo mysql_secure_installation
Once the setup process begins, you will be presented with several prompts asking you whether you want to enable the validate password plugin, which is used to test the strength of passwords you pick for MySQL users. It is recommended that you enable this plugin.
After you enable the validate password plugin, the script will ask you to specify a password validation policy. Here, you should choose a strong password policy. You will subsequently be asked to reset the root user’s password.
Next, the script will prompt you to remove anonymous MySQL users. This is important to reduce any chance of attackers gaining access to the database server by leveraging an anonymous MySQL user.
The next prompt will ask you if you would like to disable logins using the root user when authenticating remotely to the MySQL server. Remote authentication using the root user is dangerous and rarely required. Instead, you should either SSH onto the MySQL and use the MySQL client on the server to authenticate as the root user or, preferably, use an SSH tunnel to forward the remote MySQL port to your local machine and connect using a local client.
Next, you’ll be asked to delete the default databases (if they exist) that MySQL ships with. This is the recommended practice for production MySQL servers.
Finally, you’ll be asked if you want to reload the privileges tables for all changes that have been applied to take effect.
Create a dedicated WordPress database user
Security best practices dictate segregating users and privileges by duties or roles. This means that every application that makes use of the database should have its own dedicated user with the minimum amount of MySQL database permissions required to carry out its job. As such, you’ll ensure user privileges do not go over and above what is required.
This practice should extend to deployments running multiple WordPress websites — each WordPress website should have its own dedicated database and MySQL user. This ensures that at any time, only one user has access to one database at a time, and users cannot access other databases, avoiding unauthorized access and data breaches.
The following SQL statement (substitute <host> and <password> and <database> to fit your needs) can be used to create a dedicated user for your WordPress website and grant privileges for regular use. Keep in mind that some WordPress plugins, themes, and WordPress updates may occasionally need additional privileges to operate correctly (see the official WordPress guidance on this for more information)
Ensure that local_infile is disabled
The LOAD DATA statement allows you to load data files into database tables. Under specific conditions, this can be abused to read files from the MySQL server. As such, unless you have a specific use case for this in your WordPress site, you should disable this feature.
If MySQL and the web server are running on the same machine, it may allow an attacker to use the LOAD DATA LOCAL statement to read arbitrary files that the web server process has read access to. This assumes that an attacker has the ability to run arbitrary SQL statements against MySQL. Such may be the case with an SQL injection vulnerability or through the installation of a malicious WordPress plugin. This is yet another reason to keep your web server and database servers separate.
By default, local_infile is disabled in MySQL 8.0 (it used to be enabled by default in earlier versions of MySQL). To prevent the MySQL server from accepting LOAD DATA LOCAL statements, ensure that the mysqld daemon is started with local_infile disabled.
Disable MySQL command history
On Linux, the MySQL client logs statements executed interactively are saved to a history file (typically located in $HOME/.mysql_history). The MySQL command history should ideally be disabled since this reduces the likelihood of exposing sensitive information, such as passwords, encryption keys, or other secrets.
To verify that .mysql_history files do not exist on the system, run the following commands:
find /home -name “.mysql_history”
find /root -name “.mysql_history”
If the above commands return any output, remove any .mysql_history files. Additionally, you can set $HOME/.mysql_history as a symlink to /dev/null as follows:
ln -s /dev/null $HOME/.mysql_history
Ensure that mysqld is not started with the –skip-grant-tables argument
Should the MySQL’s root password get misplaced, while not the preferred method, some MySQL administrators may resort to setting MySQL to start with the –skip-grant-tables argument. When starting MySQL with this parameter, it will avoid checking its grant tables when a client connects or runs a query, effectively allowing anyone, anywhere (provided they can reach the database over the network), to do anything on the database server.
To ensure that –skip-grant-tables is not enabled, open your server’s /etc/mysql/mysql.conf.d/mysqld.cnf configuration file and look for skip-grant-tables. The value should either not be set, or set to skip-grant-tables = FALSE.
Back up your database
Backing up your WordPress database is absolutely crucial to be able to recover promptly from a disaster or an attack. While there is a myriad of ways to back up your WordPress database – from WordPress backup plugins and services to homegrown scripts that take a database dump periodically — the following are a few salient tips to bear in mind.
Take frequent backups
Taking regular backups is pretty obvious and self-explanatory — the more frequently you take database backups, the easier it will be to recover from a data loss incident. While the frequency of backups will depend on the type of WordPress site you are running, as a rule of thumb, taking a backup daily serves most use cases well.
Verify the integrity of your backups frequently
Your backups are only useful if they work. and you would likely prefer not to find out whilst you’re in the middle of an incident trying to recover data. The simple remediation to this is to frequently verify that your backups actually work by doing test restores every so often. A good way to do this is to set a calendar event every few months to go through a restore procedure to ensure your backups are still working as expected. Additionally, documenting database restoration steps is also a good idea — the less guesswork when responding to an incident, the better.
Store your backups securely
Never keep backups of your WordPress site on your web or database server (especially on your web server). Backups are a great place for attackers to go dumpster diving. Storing your backups in a secure offsite location is highly advisable. If you are taking periodic database dumps, consider storing your database dumps on an object storage service. These can include Amazon S3, Cloudflare R2, DigitalOcean Spaces, Linode Object Storage, etc. Taking this route can be a great, cost-effective way to store your database backups. However, do be extra careful that you do not make the storage bucket you are using publicly accessible.
Enable and enforcing TLS connections
Unless you are running MySQL on the same machine as your web server (which, as we already covered above, is not an ideal security practice), it is highly recommended to encrypt data between WordPress and MySQL using Transport Layer Security (TLS certificate), formerly referred to as Secure Socket Layer (SSL certificate).
By default, when you install MySQL, it will generate a self-signed certificate for you automatically. You can verify this by running the following (alternatively, you can use the mysql_ssl_rsa_setup script to generate new certificates).
You will need to copy over ca.pem from the above list (for example, via SCP) to the server running your WordPress website. Once you upload the ca.pem file to your WordPress server, you will need to move the certificate over to the operating system’s certificate trust store and update the certificate trust store as follows.
Heads up, the file name of the CA certificate must end with a .crt file extension (e.g. mysql-ca.crt is valid, but mysql-ca.pem.crt, or mysql-ca.pem are invalid).
sudo mv ca.pem /usr/local/share/ca-certificates/mysql-ca.crt
sudo update-ca-certificates
Next, you need to configure WordPress to use TLS when connecting to MySQL by adding the following to your wp-config.php file of your WordPress installation.
define(‘MYSQL_CLIENT_FLAGS’, MYSQLI_CLIENT_SSL);
Once you update wp-config.php, WordPress will initiate connections to your MySQL server using TLS.
Next, it is recommended that you enforce TLS connections to your MySQL server using the require_secure_transport system variable by adding the following to your /etc/mysql/mysql.conf.d/mysqld.cnf file.
require_secure_transport = ON
Finally, restart MySQL for changes to take effect.
systemctl restart mysql
Change the table prefix
By default, all WordPress tables are created with the ‘wp_’ prefix. This can make it easier for attackers to succeed in certain attacks, such as SQL injection, since they would know the names of the database tables. While this alone is not going to protect you, it’s a straightforward exercise, recommended by many as a best WordPress security practice.
You can change the database prefix during the installation process or at any point thereafter, although the latter is slightly more complex. Either way, you can find online tutorials on changing WordPress database prefix.
How to implement changes
Hopefully, this article has provided you with an overview of MySQL security hardening in the context of running a WordPress website. While there are no silver bullets in website security, with some effort, taking a layered, defense-in-depth approach to security will make attacking your website significantly more difficult for attackers.
While this guide presents a number of hardening techniques for MySQL, MySQL is just one component of the WordPress ecosystem. As such, you should also consider other aspects of WordPress security covered in our WordPress security hardening guide. This, coupled with proven security measures such as WordPress two factor authentication, will help you ensure you’re as safe as you can be.
If it feels like a lot to take in, remember that you can (and probably should) apply the various hardening techniques covered in this guide gradually.
Keeping your WordPress secure
Bear in mind that attackers are oftentimes after soft targets since they don’t need to put as much effort into exploiting weakly secured websites. Being one step ahead of the next WordPress website’s security posture makes you a less attractive target.
The post Hardening MySQL for your WordPress site appeared first on WP White Security.
*** This is a Security Bloggers Network syndicated blog from WP White Security authored by Mark Grima. Read the original post at: https://www.wpwhitesecurity.com/wordpress-mysql-hardening/