In Part 1 of this series, we looked at installing a 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 the 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 the MySQL server to which address its network socket will listen. 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.

Warning – Setting the bind-address to 0.0.0.0 (IPv4), or :: (IPv4/IPv6) is insecure since it will make the server accept TCP/IP connections on all interfaces.

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

Note – The MySQL service needs to be restarted for the above change to take effect.

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.

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 to which they have access.

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

Note — The /etc/passwd file is commonly used in SQL injection and LFI (Local File Inclusion) exploitation examples since it is readable by all users on the system and it contains a list of the local user accounts along with information for each account.

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

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.

Avoid specifying the secure_file_priv parameter without a value as it will have no effect.

The directory specified in the secure_file_priv parameter must exist (MySQL server will not create it).

The MySQL data directory, any subdirectory within the data directory or any directory that is accessible by all users (e.g. /tmp), should not be specified in the secure_file_priv parameter.

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 the 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 with MySQL 5.7.4, automatic password expiration is available in MySQL. This means that we can define the number of days for which a password is valid. The default global value of default_password_lifetime is 360, which means that a password must be changed approximately once a year.

Warning – Web applications that connect to the database will no longer be able to connect to the database after their user’s password expires. Follow the instructions below to overcome this issue.

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.

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.

Warning – Beware when editing AppArmor’s profiles (or creating new ones) because its permissions may cause applications to malfunction. Ideally, test any changes in a staging/pre-production environment first.

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 also create an Anonymous account which has no password.

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

Use a different database user for each web applications connecting to the database. If one application gets compromised and the attacker has access to the database, they will not be able to access other databases.

Remember that an attacker can use an anonymous account to flood the database with a huge amount of records, consequently exhausting disk space which will eventually result in a denial of service (DoS) attack.

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.

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.

Note — With WordPress as well as other CMSs and web applications, some third-party plugins, or core updates, may need to create or alter tables, in which case, additional permissions will be required (at least temporarily). It is recommended that all privileges are granted during the installation or a core update. Additional privileges granted should then be revoked once the update is complete.

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.

Privilege Description

SELECT

Allows a user to select data from a table.

INSERT

Allows a user to insert data into a table.

UPDATE

Allows a user to update the data of a table.

DELETE

Allows a user to delete the data of a table.

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

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 into categories.

Databases

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

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.

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.

Note — If we decide to remove a database, we must first delete access to it. If we only drop the database, then the users will still have access to that database if it's recreated in the future.

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 sent 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.