Securing MySQL Server on Ubuntu 16.04 LTS – Installing MySQL, Part 1

Databases can be found in everything from desktop applications, web applications, corporate servers to smartphones and other devices. Almost every software program relies on some sort of database to store its data. As applications continue to grow, so is the amount of data that is being stored into their databases, which is the main reason why they are the number one target of attackers–databases frequently contain information that is useful or desirable to an attacker. Data breaches, commonly expose data containing personally identifiable information such as names, usernames, passwords, email addresses, credit card numbers, health records; as well as other confidential documents such as trade secrets or commercially sensitive information. Legislation around the world is very strict when it comes to data privacy, so much so, that substantial fines may be incurred by organizations suffering a data breach. To such an extent, it is crucial to secure databases and their content.

For the past 15 years, SQL injection has been among the most common and dangerous attacks on web applications. In SQL injection, the database server is not directly exposed to the attacker since an attacker would exploit vulnerabilities that arise as a result of poor coding practices in the frontend or backend of web applications. However, misconfiguration of the database server itself can also lead to unauthorized access or escalation of privilege which is granted via other vulnerabilities like the SQL Injection.

In this series, we will focus on how to create a more secure environment for MySQL server, which is currently the second most popular database management system (DBMS), in order to prevent common attacks, as well as to mitigate the attack vector of other vulnerabilities.

For the purposes of this article we have setup a machine running Ubuntu 16.04 LTS (Xenial Xerus) and MySQL 5.7. We have also edited our hosts file to point ‘example.com’ to the IP address of our test machine.

Installing MySQL server

MySQL server can be installed in two ways. We can either do a version-specific installation or automatically install the latest version that is available for our distribution. We will proceed with the second option, since it is always better to run the latest version of a software.

Before installing any software, we need to make sure that our system has the latest package lists from its repositories, otherwise, this might result in installing outdated software.

secuser@secureserver:/# sudo apt-get update

Now that our package lists are updated, we can proceed with the installation of the latest version of MySQL server by running the following command.

secuser@secureserver:/# sudo apt-get install mysql-server

The installer will search for the packages and dependencies that need to be installed and then we will be prompted to confirm that we want to proceed with the installation of those packages.

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
tinyca
The following NEW packages will be installed
mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7 mysql-server-core-5.7
0 to upgrade, 9 to newly install, 0 to remove and 253 not to upgrade.
Need to get 0 B/17.9 MB of archives.
After this operation, 160 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y

MySQL Setup – Root account

During the initial setup, we are being prompted to set a password for the MySQL’s root account.

Using a strong password is critical. A strong password should be longer than 12-15 characters and should be a combination of alphanumeric and special characters and ideally, it should not contain words you can find in a dictionary (e.g. – LjoS^5hwF$9Uyn*0pBEe). Many administrators, choose very easily guessable passwords for their own convenience, or forget to change passwords when the server moves into a production environment. This means that the database user account can be brute-forced either remotely or locally (requires to already have access to the server). Furthermore, simple passwords are much easier for an attacker to crack offline should they manage to steal MySQL users’ password hashes.

The following is an example of a MySQL server which is exposed over a network combined with an insecure root account password.

[*] 192.168.2.108:3444 MYSQL - Found remote MySQL version 5.7.12
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: root:admin (Incorrect: Access denied for user 'root'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: root:root (Incorrect: Access denied for user 'root'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: root:r00t (Incorrect: Access denied for user 'root'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: admin:r00t (Incorrect: Access denied for user 'admin'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: admin:toor (Incorrect: Access denied for user 'admin'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: admin:l3tme1n (Incorrect: Access denied for user 'admin'@'192.168.2.112' (using password: YES))
[+] 192.168.2.108:3444 MYSQL - Success: root:l3tme1n'

The root account should under no circumstances be used in any of our web applications. If we use the root account to connect our web application to the database, then if an attacker is able to execute commands on the SQL server, they will be able to enumerate and possibly modify and even delete data inside all the databases and tables that the database server manages.

Not only will an attacker be able to compromise data in the database server, but in the case of a server misconfiguration, as we will see later on in this article, this could result in a complete server compromise.

Testing installation

Now that the installation is finished, we can test whether the MySQL has been successfully installed and running. We can verify if the mysql daemon is running in numerous ways.

Services

We can list the services that are currently running on the system and filter to show mysql related ones

secuser@secureserver:/# ps aux | grep mysql

--> mysql 7891 0.2 7.1 1784520 145100 ? Ssl 14:32 0:00 /usr/sbin/mysqld
secuser 8093 0.0 0.0 14232 1084 pts/5 S+ 14:35 0:00 grep mysql

mysqladmin

We can use mysqladmin, which is a client for performing administrative operations on MySQL Server.

secuser@secureserver:/# mysqladmin -u root -p status

--> Uptime: 318 Threads: 1 Questions: 2 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.006

service

We can use the service command which is mostly used to start/stop services. Using the status parameter, we can see the current status of a given service.

secuser@secureserver:/# service mysql status

--> Active: active (running) since Thu 2016-07-14 11:13:03 BST; 6min ago
Main PID: 7891 (mysqld)

Should the mysql daemon not be running, we’d get the following output instead.

secuser@secureserver:/# service mysql status

--> Active: inactive (dead) since Thu 2016-07-14 11:19:50 BST; 1s ago
Main PID: 7891 (code=exited, status=0/SUCCESS)

We can also login to the MySQL server. A successful login means that the server is properly functioning.

secuser@secureserver:/# mysql -u root -p

--> Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-0ubuntu1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

---