Last updated at Fri, 08 Dec 2017 21:53:37 GMT

Synopsis

Now a day database server is very critical and necessary component for any applications. Databases can be found in everything from web applications, web server to smartphones and other devices. Most of software applications rely on a database to store its data. This is the reason why databases are the number one target of any attackers. Among all the databases MySQL and MariaDB has become the world’s most popular open source database due to its fast performance, high reliability and ease of use. So, securing the database server and their content is a crucial part of every system administrator.

In this tutorial, we will through some basic steps to secure your MariaDB or MySQL databases on Ubuntu 16.04 server.

Prerequisites

  • Fresh Ubuntu 16.04 server installed on your system.
  • Non-root user with sudo privileges.

Install MariaDB

MariaDB is fastest growing open source database and drop-in replacement of MySQL. The latest version of the MariaDB is not available in Ubuntu default repository. So you will need to add MariaDB repository to your system.

First, import the key with the following command:

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

Next, add the repository by editing /etc/apt/sources.list file:

sudo nano /etc/apt/sources.list

Add the following line:

deb [arch=amd64,i386,ppc64el] http://ftp.utexas.edu/mariadb/repo/10.1/ubuntu xenial main

Save and close the file when you are finished, then update your repository with the following command:

sudo apt-get update -y

Finally, install MariaDB server:

sudo apt-get install mariadb-server -y

Once the MariaDB is installed, start MariaDB service and enable it to start at boot time:

sudo systemctl start mysql

sudo systemctl enable mysql

Next, it is recommended to secure MariaDB default installation. You can do this by running the mysql_secure_installation script:

sudo mysql_secure_installation

The above script will set the root password, remove anonymous users, disallow remote root login and remove the test database.

Secure MariaDB

  1. Let’s start with the most common settings by editing the default MySQL configuration file:
sudo nano /etc/mysql/my.cnf

First, you should restrict MySQL access from a remote machine. Make sure that MySQL has the following line under [mysqld] section:

 bind-address=127.0.0.1
  1. By default MysQL server listens on port 3306. So it is recommended to change the default port:
port=8087
  1. Next, you should disable the use of LOCAL INFILE, which is enabled by default. This option allows a user to read files on the local filesystem. You can disable this option by adding the following line under [mysqld] section:
 local-infile=0
  1. By default, automatic password expiration is available in MySQL. The default MySQL user password lifetime is 360 days. So it is recommended to set number of days a password is valid for. You can do this by adding the following line under [mysqld] section:
 default_password_lifetime=30

If you want passwords to never expire, you can set with the following line:

default_password_lifetime=0

The above settings will apply to all the users. If you want to set specific user expiry, then run the following query:

MariaDB [(none)]>ALTER USER 'ubuntu'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

MariaDB [(none)]> flush privileges;
  1. Next, it is important to check, whether any users without a password in MySQL are available or not.

You can list all the users with the following query:

MariaDB [(none)]>SELECT User,Host,Password FROM mysql.user;

If any user found without a password, then you should set a password for that user. You can set password with the following query:

MariaDB [(none)]>UPDATE mysql.user SET Password=PASSWORD('password') WHERE User="passwordless-user";

MariaDB [(none)]> flush privileges;
  1. The default MySQL admin username is root. So it is recommended to rename root with the long and complex name. You can do this by running the following command:
mysql -u root -p

Enter your root password when prompt. Then change the database to mysql:

MariaDB [(none)]> use mysql;

Next, rename the root user with ubuntu by running the following command:

MariaDB [mysql]> update user set user="ubuntu" where user="root";

Next, fulsh the privileges with the following command:

MariaDB [mysql]> flush privileges;
  1. During the database installation and creation process all the MySQL commands are stored in the ~/.mysql_history file. This will be very harmful for security reasons. So it is recommended to delete this file after installation is complete.
sudo rm -rf ~/.mysql_history
  1. Last, one more important things apart from all the configuration is, you should update your MySQL server regularly. You can update the MySQL server with the following command:
sudo apt-get update -y

sudo apt-get install mariadb-server -y

Conclusion

In this tutorial, we have learned how to install and configure MariaDB server. We have also learned to secure MariaDB server by setting up different configuration variables in my.cnf file. You can find more information about MariaDB/MySQL security on the MariaDB/MySQL websites.

References