Securing MySQL Server on Ubuntu 16.04 LTS – Configuring MySQL Securely, Part 2

In part 1 of this series, we looked at Installing MySQL Server on Ubuntu 16.04 LTS. In this second part, we will be looking at configuring MySQL securely.

Configuration

We will start off with the most common settings by opening the default MySQL configuration file using the nano text editor.

secuser@secureserver:/# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

User

MySQL server should never be run as the root user. To understand why this is so important, let’s take as an example MySQL user jason who has the FILE privilege. This means that jason can read/write files on the server. If MySQL server is running under the root account, it can read and write files as root, which on Linux systems is the super-user.

To such an extent, MySQL should only be run as an unprivileged user which minimizes the risk of users having unauthorized access to sensitive files. By default, MySQL creates the mysql user and the MySQL server should run under that account.

user = mysql

Server Binding

The bind-address option, which is currently set by default to 127.0.0.1 (localhost), tells MySQL server on which address its network socket will listen to. By using the 127.0.0.1 address, the MySQL server can only be accessed by applications which are installed on the same local environment as the MySQL server.

A web application on the same machine can access the backend database, while another computer on the local or external network will not have access to it. This means that we restrict any direct remote access to the MySQL server.

We make sure that MySQL has the following bind-address entry in mysqld.cnf.

bind-address = 127.0.0.1

Port

By default, the MySQL server listens on port 3306. By changing the default port, we can make the MySQL service harder to identify.

Port = 3444 #sample port number

We can verify that MySQL is running on the newly configured port by running netstat and using the grep utility to filter for the mysql service.

secuser@secureserver:/# netstat –ntulp | grep mysql

--> Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:3444 0.0.0.0:* LISTEN 865/mysqld

local_infile

This option, which is enabled by default, allows a user to use LOCAL in LOAD_DATA statements, which means that it allows that user to read files on the local filesystem. Having said this, for this to work, the user must have the FILE privilege granted and can only read files they have access to.

Nevertheless, it is still very dangerous to allow MySQL users to read files as they could potentially have access sensitive data.

The following is an example of saving the content of /etc/passwd into a table with the name of test.

mysql> LOAD DATA LOCAL INFILE "/etc/passwd" REPLACE INTO TABLE test FIELDS TERMINATED BY '\t' lines terminated by '\n';

We can then query the table to see the file content.

mysql> SELECT * FROM test;

-->
+----------------------------------------------------------------------+
| text |
+----------------------------------------------------------------------+
| root:x:0:0:root:/root:/bin/bash |
| daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin |
| bin:x:2:2:bin:/bin:/usr/sbin/nologin |
| sys:x:3:3:sys:/dev:/usr/sbin/nologin |
| sync:x:4:65534:sync:/bin:/bin/sync |
| games:x:5:60:games:/usr/games:/usr/sbin/nologin |
+----------------------------------------------------------------------+

We can disable this feature as follows in mysqld.cnf.

local_infile = 0

secure_file_priv

Users who have the FILE privilege can use the LOAD_FILE() or LOAD_DATA() and SELECT INTO statements to load or write data to a file.

What secure_file_priv does, is limit the usage of these operations within a specified directory which means that the user can only work with files from within that directory.

secure_file_priv = '/path/'

Symbolic links

Symbolic links, commonly referred to as symlinks, are files that link to other files (similar to shortcuts in Microsoft Windows). They are commonly used to move the data directory (the directory containing databases) to another folder or path.

Symbolic links to tables shouldn’t be allowed, especially if MySQL server is running as root (which, as we already discussed should never be the case) because under certain circumstances, users can access files outside the data directory which may lead to unauthorized access to sensitive data.

We can disable this feature as follows in mysqld.cnf.

symbolic-links = 0

default_password_lifetime

As from MySQL 5.7.4, automatic password expiration is available in MySQL. This means that we can define the number of days a password is valid for. The default global value of default_password_lifetime is 360, which means that a password must be changed approximately once a year.

If we want passwords to expire after 90 days (3 months) we would define the following in mysqld.cnf.

default_password_lifetime = 90

Otherwise, if we want passwords to never expire, we can set the following in mysqld.cnf.

default_password_lifetime = 0

Alternatively, we can specify per user expiry by using ALTER_USER in a MySQL shell as follows.

mysql> ALTER USER jason'@'localhost' PASSWORD EXPIRE INTERVAL 35 DAY;

If our database user’s password has expired, we will get the following message (for example’s sake, errors are being displayed to the user, however, in reality applications should properly handle and log database errors as opposed to printing them).

In order to set a new password, we must login to the MySQL server using the credentials of the user whose password has expired. We will use ‘jason’ as an example.

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

mysql> SET PASSWORD = PASSWORD('gsu8L54eV6UBpS5A8ZbZ');
--> Query OK, 0 rows affected, 1 warning (0.00 sec)

We then edit our web application’s configuration file and set the new password.

secuser@secureserver:/# sudo nano /var/www/html/sql.php
...

$con = mysqli_connect("localhost", "jason", "gsu8L54eV6UBpS5A8ZbZ") or
die("Could not connect: " . mysqli_error());
...

Once we save the changes and refresh the page, our web application is now back online.

AppArmor

AppArmor in Ubuntu is a kernel-integrated security module. Its primary job is permission control, which means that it checks if a program is running within its allowed environment.

Each program has its own profile which AppArmor loads once started. MySQL has its own profile that gets installed during MySQL Server setup.

To check whether MySQL’s profile is loaded we can run the following command.

secuser@secureserver:/# sudo apparmor_status

--> apparmor module is loaded.
28 profiles are loaded.
28 profiles are in enforce mode.
# We can make sure this profile is loaded
/usr/sbin/mysqld

If the profile is not loaded, we can enable it by running the following commands.

secuser@secureserver:/# sudo rm /etc/apparmor.d/disable/usr.sbin.mysqld
secuser@secureserver:/# sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

User accounts

In order for an application to access a database, it must first authenticate against the database server using a valid user account. As part of our MySQL server audit process, we need to review the user accounts and identify redundant accounts, or accounts that do not have a password specified. The latest versions of MySQL initially only create one account–root. However, older versions create also an Anonymous account which has no password.

In order to get a list of users, we must first login to the MySQL server.

secuser@secureserver:/# mysql -u root -p
Enter password:
# mysql server message
mysql>

After logging in, we can query the mysql.user table to get the user list.

mysql> SELECT User, Host, Authentication_String FROM mysql.user;

-->
+------------------+-----------+----------------------------------------+
| User | Host | Authentication_String
+------------------+-----------+----------------------------------------+
| root | localhost | *4ACFE3202A5FF5CF467898FC58AAB1D615... |
| mysql.sys | localhost | *0CC75F492CB9CCCB138B7C76F73C0B5D17... |
| debian-sys-maint | localhost | *FDD4ACF2CE3B980940298A1FD2D6A5A7ED... |
| test | localhost |
+------------------+-----------+----------------------------------------+
4 rows in set (0.00 sec)

As we can see there is the root account, and the test account which has no password. We can specify a secure password for that account as follows.

mysql> SET PASSWORD FOR 'test'@'localhost' = "uYI3cU_FreFgR*UsS2B";

--> Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Host, Authentication_String FROM mysql.user WHERE User='test';

-->
+------------------+-----------+----------------------------------------+
| User | Host | Authentication_String
+------------------+-----------+----------------------------------------+
| test | localhost | *73C084DAC378FF328AD3C131ED10F6E309... |
+------------------+-----------+----------------------------------------+
1 row in set (0.00 sec)

Alternatively, we can remove the account if we don’t need it.

mysql> DROP USER 'test'@'localhost';
--> Query OK, 0 rows affected (0.00 sec)

Having a very strong password for the root user is fundamental. Additionally, we could also rename the root account username to make it harder for an attacker to guess.

mysql> RENAME USER 'root'@'localhost' TO new_username;
--> Query OK, 0 rows affected (0.00 sec)

User Privileges

The privileges of a database user are very often overlooked. Either the administrator does not know which permissions to grant to a user and thus grants all, or, the administrator does not carefully assign the correct permissions. Permissions are key to the overall website and server security, and could help mitigate the effects of a successful attack.

In the event of our web application getting compromised, we should have the correct mechanisms in place to minimize the damage or the chance of further privilege escalation by an attacker.

Since there are too many permissions to list, we will take a look at which permissions a common website, in our case a WordPress site, needs to have in order to function normally.

During the installation, the application may use all of the permissions in order to correctly setup the tables and insert the required data. However, after the setup is finished, a typical installation will need only the following permissions for it to function, which are only related to the DATA of the database and not STRUCTURE or ADMINISTRATION.

For example, we can grant the above permissions on our user ‘jason’ on the ‘test’ database using the following SQL statement.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON Test.* TO 'jason'@'localhost';

--> Query OK, 0 rows affected (0.01 sec)

mysql> show grants FOR 'jason'@'localhost';

-->
+-----------------------------------------------------------------------+
| Grants for jason@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jason'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `Test`.* TO 'jason'@'localhost'
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

Some users prefer the graphical interface of some administration tools like phpMyAdmin and MySQL Workbench, which make it easier to manage databases as well as the privileges of a user. As we can see the privileges are grouped in categories.

Databases

After reviewing the users, we must now review the databases to identify unneeded databases.

mysql> show databases;

-->
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)

Older versions of MySQL server initially create a test database which is accessible by any user and should be removed along with any other database that is not being used. In the case that we need to keep the test database, we should remove access rights for all users.

We can remove access to the test database by executing the following SQL commands.

mysql> DELETE FROM mysql.db WHERE Db = 'test';
--> Query OK, 0 rows affected (0.02 sec)

mysql> FLUSH PRIVILEGES;
--> Query OK, 0 rows affected (0.00 sec)

Now, no users should be able to access that database. If we want to completely remove the database, we can do so by executing the following SQL statement.

mysql> DROP DATABASE test;
--> Query OK, 0 rows affected (0.00 sec)

Command History

Like Bash, MySQL has its own history log stored in the ~/.mysql_history file. Any commands send to the MySQL server via shell will be logged into this file. As you may have already figured out, it will likely contain information about permissions, database names, usernames and passwords which we may have used in queries.

secuser@secureserver:~# cat ~/.mysql_history

--> use\040wp;
select\040*\040FROM\040wp_users;
select\040*\040FROM\040wp_users\040WHERE\040user_pass='1hF71jf91f7919Hf271HHFbA24';
exit

We can clear this file using the following Bash command.

secuser@secureserver:~# cat /dev/null > ~/.mysql_history

THE AUTHOR

Agathoklis Prodromou
Web Systems Administrator/Developer

Akis has worked in the IT sphere for more than 13 years, developing his skills from a defensive perspective as a System Administrator and Web Developer but also from an offensive perspective as a penetration tester. He holds various professional certifications related to ethical hacking, digital forensics and incident response.