Security is a critical component of any database application. Did you know that a default MySQL installation can leave your database vulnerable to cyber threats?
There several different implementations of the SQL database available on Linux and UNIX systems. MySQL and MariaDB are the most popular options for deploying relational databases in server environments.
In this article, we will demonstrate a few tips that will help you improve the security of your VPS hosted database. In the examples, we’ll use MySQL, however, these techniques can also be applied to other database implementations such as MariaDB.
Install MySQL Server
If you already have MySQL installed, skip to the next step. If you do not have MySQL, use these commands to install the database.
> yum install mysql-server
> apt-get install mysql-server
You will be prompted to choose a new password for the database. It’s always best to go ahead and setup the root user with a password even though it may be possible to skip this step.
Once this is complete, MySQL provides an optional command that will immediately bolster the security of your databases. Just execute the following:
> sudo mysql_secure_installation
This command will prompt you for the root password that you setup in the previous step.
Immediately thereafter, you will prompted to answer a series of questions. The first question is if you’d like to change the root password. Assuming that you have just selected a new root password, there is no need to change it.
You should answer “Y” to all of the remaining questions.
This will remove the ability for an anonymous user to log into MySQL by default. The wizard will also disable the root account from logging in remotely with root credentials.
The process will then remove some of the insecure testing databases included with the app. The last part of the process updates the running MySQL instance to reflect these changes by running a “flush privileges” command.
These basic actions will help you increase the security around a typical MySQL installation. You might be wondering, “What if I need to use my root account to remotely connect from a different server on the same private network?”
What if you need Root Remote Access to your DB?
Wouldn’t the preceding steps prevent remote root database access from being possible? The answer to this question is “Yes.”
Please keep in mind that the best practices for database security say that you should never use a root account for production databases.
If you do use the root password for remote connections, you should reconsider the overall design of your architecture. Best practices suggest that you create a user account, assign privileges and allow that account to connect to your MySQL instance.
If you do not consider this infrastructure change, please realize that you are leaving your server vulnerable since the root password may be stored on your server in a different location. Hackers could wreak havoc on your VPS if they were able to gain root access to your DB.
The Configuration Files for MySQL
The main configuration file for MySQL is called “my.cnf” and this file is located in the “/etc/mysql/” directory on Ubuntu. In other Linux distros, the configuration file may be located in the “/etc/” directory.
Let’s make some changes to this configuration file in order to further secure our MySQL instance. Let’s open the file with the following command:
> sudo nano /etc/mysql/my.cnf
The first setting that we should check is the “bind-address”. This should absolutely be setup to only listen on the loopback address. This prevents outside connections.
If you have multiple webservers, you should consider running MySQL on a private network and restrict the access using iptables. This will ensure that only webservers that actually require access to your database can become authenticated through the MySQL port (3306).
Search for the following line and change it accordingly to your needs:
> bind-address = 127.0.0.1
The next hole we will patch is a function that provides access to the underlying filesystem from within MySQL. Without patching this hole, your server can become vulnerable to a number of different cyber-attacks. You should always restrict access to this unless you absolutely need it.
local-infile=0
How to Configure SQL Logging
Another good way to keep track of what is happening with your MySQL installation is to check the logs. By checking the logs, you can sniff out suspicious activity before an attack is launched.
In order to accomplish this, we will need to add additional logging information into the configuration file.
You can set the log variable within the same “[mysqld]” section that we’ve been working in. Go ahead and add this line:
> log=/var/log/mysql-logfile
Reassigning the Root Username
Another good practice is to change the root username to something other than “Root.” This will make it more difficult for an attacker to access your DB since they will need to know the correct username before trying to crack the password.
The root login can be changed using the following command within the MySQL environment:
> rename user 'root'@'localhost' to 'newAdminUser'@'localhost';
We can see the change by using the same query we’ve been using for the User database:
> select user,host,password from mysql.user;
Note: Remember to execute the following command before exiting:
> FLUSH PRIVILEGES;
Another good practice is to implement application specific users. Each application that uses MySQL should have its own user and only that user should have privileges that are limited to the databases that they need to run.
Here is how to create a database with a dedicated user. Execute the following commands in the MySQL environment:
> create database newDB;
> CREATE USER ‘newUSER’@’localhost’ IDENTIFIED BY ‘password’;
> GRANT SELECT,UPDATE,DELETE ON newDB.* TO ‘newUSER’@’localhost’;
> FLUSH PRIVILEGES;
If you need to remove privileges, try the following command:
> REVOKE UPDATE ON newDB.* FROM 'newUSER'@'localhost';
This command will grant a user all the privileges to a specific DB:
> GRANT ALL ON newDB.* TO 'newUSER'@'localhost';
While this is a non-exhaustive list of MySQL security practices, the tips we have listed are designed to guide you in the right direction on where to concentrate your attention when deploying secured MySQL instances on your VPS.
