MySQL and MariaDB are two options to deploy relational databases in server environments. If the configuration is not done properly, both of them can turn out to be mere security liabilities. We can’t let that happen, right? So how to secure MariaDB and MySQL?
In this article, we will present you with some steps to secure your MySQL or MariaDB databases. Let’s begin!
How to secure MySQL and MariaDB databases?
To start with MySQL, it asks you to set a root password as given below:
sudo apt-get install mysql-server
This is the first step to securing your administrator account. Moreover, you have the option to set this root password any time later. But, instead of setting it in the future, you must do it now. After the installation process is done, you need to run a few scripts. Firstly, in order to create a directory for the databases, you have to use a script.
The script is, ‘mysql_install_db’. Take a look at the below-given command:
sudo mysql_install_db
Now, run another script named mysql_secure_installation.
sudo mysql_secure_installation
As soon as you run this command, it prompts you for the root password that you just set during your installation process. You will witness a set of questions on your screens, including the one to change the root password.
You can switch to a more secure password, in case you didn't set one. For all the other questions, answer Y (yes).
After following the above-mentioned steps, no one;
- Can log into MySQL by default
- Remove insecure test databases
- Disable the remote login with an administrator account
Moving on to our next segment, let us understand the current security considerations.
What are the current security considerations?
The primary theme of MySQL is that you only get access when it’s a matter of utmost importance. Hence, there are certain situations when data security is all about creating a balance between both factors, that is, security and convenience.
Now, let us talk about several ways of security for MySQL. Take a look at the below pointers carefully:
- Via My.cnf file
- To secure MySQL from within
- How to secure passwords as well as host associations
Via My.cnf file
For MySQL, the My.cnf file is known as the primary configuration file. In the case of Ubuntu, you can find this file in the "/etc/mysql/" directory. On the other hand, on any other VPS, the directory is /etc/.
Now, you need to lock down the MySQL instance. To do that, you have to perform some changes to the settings of this file.
Let’s do that!
- Go to the file that has the root privileges. The next step is to change your directory path. Take a look: sudo nano /etc/MySQL/my.cnf
- Head towards the mysqld section. Here, you have to look for the bind-address setting. The next step is to change the settings to the local loopback network device. The value is 127.0.0.1 : bind-address = 127.0.0.1
You may wonder what is the purpose of this bind-address?
Well, this assures that MySQL is accepting connections only from the local machines and not from elsewhere. Furthermore, if you are looking to attain access to this database from some other device, you have the option to connect via SSH. This helps you in database querying locally. Additionally, It also sends the result via the SSH tunnel.
Well, till now all looks good! But there is another loophole that we will fix. Within MySQL, there is a function that grants access to the filesystem. This can result in some serious security breaches and needs to be immediately put off. Moreover, you need to add a directive in order to halt the process of loading local files:
local-infile=0
Now, if any user does not have file-level privileges to the database, then this command helps to disable all the loading files from the filesystem. Additionally, keep a check that the MySQL log directory, MySQL log and error log are not readable. Follow this command to achieve the same:
sudo ls -l /var/log/mysql*
How to secure MySQL from within
Go to the MySQL prompt interface. Here you have to insert several commands. Take a look.
mysql -u root -p
It’s time to enter the root password that you had inserted in the beginning. Now, the question is how to secure passwords and host associations? Well, for that, the first step is to ensure that every user has a password or a host association in MySQL. Use the below mentioned below to get the information:
SELECT User,Host,Password FROM mysql.user;
Output:
+------------------+-----------+-------------------------------------------+
| user | host | password |
+------------------+-----------+-------------------------------------------+
| root | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| demo-user | % | |
| root | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| root | ::1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
As it is clearly seen that the second user, demo-user, does not have a password and is still valid. Such users can cause a potential threat to security. You have the liberty to set a password for this user. Use the below-mentioned command:
UPDATE mysql.user SET Password=PASSWORD('newPassWord') WHERE User="demo-user";
After you execute this command, again run the previous command, to be sure that the user now has a designated password. Take a look:
SELECT User,Host,Password FROM mysql.user;
Output:
+------------------+-----------+-------------------------------------------+
| user | host | password |
+------------------+-----------+-------------------------------------------+
| root | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| demo-user | % | *D8DECEC305209EEFEC43008E1D420E1AA06B19E0 |
| root | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| root | ::1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
Now, the password has been set but when you see it in the host column, there is still a % sign. This indicates that it’s a wildcard that implicates any random host. But you have to switch it to ‘localhost’. To do that follow the below-mentioned command:
UPDATE mysql.user SET Host='localhost' WHERE User="demo-user";
Now, all your fields are set appropriately. But, even now if you see any blank users you must remove them. Use the following command to accomplish this task:
DELETE FROM mysql.user WHERE User="";
The last step is to execute the below-mentioned command so that new permissions can be implemented:
FLUSH PRIVILEGES;
Change the root user
There are instances when an attacker tries to access your root MySQL login. When you change the root login name, it adds another layer of security for the attacker to breach.
To change the root login, use the below-stated command:
rename user 'root'@'localhost' to 'newAdminUser'@'localhost';
After executing the command, you need to flush privileges again:
FLUSH PRIVILEGES;
Now, if you want to perform administrative tasks, you have to log in to MySQL with the new username that you just created. Use this command:
mysql -u newAdminUser -p
How to implement application-specific users
You have the liberty to run the processes as an isolated user in MySQL. Now, when you have to configure a new application in order to use MySQL, the first step is to create databases:
create database testDB;
The next step is to create a user. Use this command:
CREATE USER 'demo-user'@'localhost' IDENTIFIED BY 'password';
Now, to grant permissions in MySQL to the new user, execute:
GRANT SELECT,UPDATE,DELETE ON testDB.* TO 'demo-user'@'localhost';
In case you want to revoke the privileges, you can use the below-stated command:
REVOKE UPDATE ON testDB.* FROM 'demo-user'@'localhost';
Additionally, if you want a specific database, you can mention that in the below-given command:
GRANT ALL ON testDB.* TO 'demo-user'@'localhost';
Furthermore, in order to display the current privileges of a user, we must first implement them using the below-mentioned command:
FLUSH PRIVILEGES;
show grants for 'demo-user'@'localhost';
Output:
+------------------------------------------------------------------------------------------------------------------+
| Grants for demo-user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demo-user'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, UPDATE, DELETE ON `testDB`.* TO 'demo-user'@'localhost' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Besides, you must always flush the privileges so that the new changes can be implemented.
Conclusion
When we talk about MySQL and MariaDB security practices, there are ample of them. These were some of the methods, out of many, that you can implement to secure your databases.
We hope that the article helps you in understanding the concept of security for both MySQL and MariaDB along with the respective commands.
Implement them in order to configure and secure your databases correctly so that there is no loophole left in your VPS.
People also read:
