Ten Tips on How to Achieve MySQL and MariaDB Security

Security of data is a top priority these days. Sometimes it’s enforced by external regulations like PCI-DSS or HIPAA, sometimes it’s because you care about your customers’ data and your reputation. There are numerous aspects of security that you need to keep in mind – network access, operating system security, grants, encryption and so on. In this blog post, we’ll give you 10 tips on what to look at when securing your MySQL or MariaDB setup.

1. Remove Users Without Password

MySQL used to come with a set of pre-created users, some of which can connect to the database without a password or, even worse, anonymous users. This has changed in MySQL 5.7 which, by default, comes only with a root account that uses the password you choose at installation time. Still, there are MySQL installations which were upgraded from previous versions and these installations keep the legacy users. Also, MariaDB 10.2 on Centos 7 comes with anonymous users:

MariaDB [(none)]> select user, host, password from mysql.user where user like '';
+------+-----------------------+----------+
| user | host                  | password |
+------+-----------------------+----------+
|      | localhost             |          |
|      | localhost.localdomain |          |
+------+-----------------------+----------+
2 rows in set (0.00 sec)

As you can see, those are limited only to access from localhost but regardless, you do not want to have users like that. While their privileges are limited, they still can run some commands which may show more information about the database – for example, the version may help identify further vectors of attack.

[root@localhost ~]# mysql -uanonymous_user
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 19
Server version: 10.2.11-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> SHOW GRANTSG
*************************** 1. row ***************************
Grants for @localhost: GRANT USAGE ON *.* TO ''@'localhost'
1 row in set (0.00 sec)
MariaDB [(none)]> s
--------------
mysql  Ver 15.1 Distrib 10.2.11-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:        19
Current database:
Current user:        anonymous_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.2.11-MariaDB MariaDB Server
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:            12 min 14 sec
Threads: 7  Questions: 36  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.049
--------------

Please note that users with very simple passwords are almost as insecure as users without any password. Passwords like “password” or “qwerty” are not really helpful.

2. Tight Remote Access

First of all, remote access for superusers – this is taken care of by default when installing the latest MySQL (5.7) or MariaDB (10.2) – only local access is available. Still, it’s pretty common to see superusers being available for various reasons. The most common one, probably because the database is managed by humans who want to make their job easier, so they’d add remote access to their databases. This is not a good approach as remote access makes it easier to exploit potential (or verified) security vulnerabilities in MySQL – you don’t need to get a connection to the host first.

Another step – make sure that every user can connect to MySQL only from specific hosts. You can always define several entries for the same user (myuser@host1, myuser@host2), this should help to reduce a need for wildcards (myuser@’%’).

3. Remove Test Database

The test database, by default, is available to every user, especially to the anonymous users. Such users can create tables and write to them. This can potentially become a problem on its own – any writes would add some overhead and reduce database performance. Currently, after the default instalation, only MariaDB 10.2 on Centos 7 is affected by this – Oracle MySQL 5.7 and Percona Server 5.7 do not have the ‘test’ schema available.

[root@localhost ~]# mysql -uanonymous_user
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 13
Server version: 10.2.11-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> SHOW GRANTSG
*************************** 1. row ***************************
Grants for @localhost: GRANT USAGE ON *.* TO ''@'localhost'
1 row in set (0.00 sec)
MariaDB [(none)]> USE test;
Database changed
MariaDB [test]> CREATE TABLE testtable (a INT);
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> INSERT INTO testtable VALUES (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [test]> SELECT * FROM testtable;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Of course, it may still happen that your MySQL 5.7 has been upgraded from previous versions in which the ‘test’ schema was not removed – you should take care of this and check if you have it created.

4. Obfuscate Access to MySQL

It is well known that MySQL runs on port 3306, and its superuser is called ‘root’. To make things harder, it is quite simple to change this. To some extent, this is an example of security through obscurity but it may at least stop automated attempts to get access to the ‘root’ user. To change port, you need to edit my.cnf and set ‘port’ variable to some other value. As for users – after MySQL is installed, you should create a new superuser (GRANT ALL … WITH GRANT OPTION) and then remove existing ‘root@’ accounts.

5. Network Security

Ideally, MySQL would be not available through the network and all connections would be handled locally, through the Unix socket. In some setups, this is possible – in that case you can add the ‘skip-networking’ variable in my.cnf. This will prevent MySQL from using any TCP/IP communication, only Unix socket would be available on Linux (Named pipes and shared memory on Windows hosts).

Most of the time though, such tight security is not feasible. In that case you need to find another solution. First, you can use your firewall to allow traffic only from specific hosts to the MySQL server. For instance, application hosts (although they should be ok with reaching MySQL through proxies), the proxy layer, and maybe a management server. Other hosts in your network probably do not need direct access to the MySQL server. This will limit possibilities of attack on your database, in case some hosts in your network would be compromised.

If you happen to use proxies which allow regular expression matching for queries, you can use them to analyze the SQL traffic and block suspicious queries. Most likely your application hosts shouldn’t run “DELETE * FROM your_table;” on a regular basis. If it is needed to remove some data, it can be executed by hand, locally, on the MySQL instance. You can create such rules using something like ProxySQL: block, rewrite, redirect such queries. MaxScale also gives you an option to block queries based on regular expressions.

6. Audit Plugins

If you are interested in collecting data on who executed what and when, there are several audit plugins available for MySQL. If you use MySQL Enterprise, you can use MySQL Enterprise Audit which is an extension to MySQL Enterprise. Percona and MariaDB also have their own version of audit plugins. Lastly, McAfee plugin for MySQL can also be used with different versions of MySQL. Generally speaking, those plugins collect more or less the same data – connect and disconnect events, queries executed, tables accessed. All of this contains information about which user participated in such event, from what host it logged from, when did it happen and so on. The output can be XML or JSON, so it’s much easier to parse it than parsing general log contents (even though the data is rather similar). Such output can also be sent to syslog and, further, some sort of log server for processing and analysis.

7. Disable LOAD DATA LOCAL INFILE

If both server and client has the ability to run LOAD DATA LOCAL INFILE, a client will be able to load data from a local file to a remote MySQL server. This, potentially, can help to read files the client has access to – for example, on an application server, one could access any file that the HTTP server has access to. To avoid it, you need to set local-infile=0 in the my.cnf

8. File Privileges

You have to keep in mind that MySQL security also depends on the operating system setup. MySQL stores data in the form of files. The MySQL server writes plenty of information to logs. Sometimes this information contains data – slow query log, general log or binary log, for example. You need to make sure that this information is safe and accesible only to users who have to access it. Typically it means that only the root and the user under whose rights MySQL is running, should have access to all MySQL-related files. Most of the time it’s a dedicated user called ‘mysql’. You should check MySQL configuration files and all the logs generated by MySQL and verify that they are not readable by other users.

9. SSL and Encryption of Data in Transit

Preventing people from accessing configuration and log files is one thing. The other issue is to make sure data is securely transferred over the network. With an exception of setups where all the clients are local and use Unix socket to access MySQL, in majority of cases, data which forms a result set for a query, leaves the server and is transferred to the client over the network. Data can also be transferred between MySQL servers, for example via standard MySQLreplication or within a Galera cluster. Network traffic can be sniffed, and through those means, your data would be exposed.

To prevent this from happening, it is possible to use SSL to encrypt traffic, both server and client-side. You can create an SSL connection between a client and a MySQL server. You can also create an SSL connection between your master and your slaves, or between the nodes of a Galera cluster. This will ensure that all data that is transferred is safe and cannot be sniffed by an attacker who gained access to your network.

The MySQL documentation covers in detail how to setup SSL encryption. If you find it too cumbersome, ClusterControl can help you deploy a secure environment for MySQL replication or Galera cluster in a couple of clicks:

10. Encryption of Data at Rest

Securing data in transit using SSL encryption only partially solves the problem. You need to take care also of data at rest – all the data that is stored in the database. Data at rest encryption can also be a requirement for security regulations like HIPAA or PCI DSS. Such encryption can be implemented on multiple levels – you can encrypt the whole disk on which the files are stored. You can encrypt only the MySQL database through functionality available in the latest versions of MySQL or MariaDB. Encryption can also be implemented in the application, so that it encrypts the data before storing it in the database. Every option has its pros and cons: disk encryption can help only when disks are physically stolen, but the files would not be encrypted on a running database server. MySQL database encryption solves this issue, but it cannot prevent access to data when the root account is compromised. Application level encryption is the most flexible and secure, but then you lose the power of SQL – it’s pretty hard to use encrypted columns in WHERE or JOIN clauses.

All flavors of MySQL provide some sort of data at rest encryption. Oracle’s MySQL uses Transparent Data Encryption to encrypt InnoDB tablespaces. This is available in the commercial MySQL Enterprise offering. It provides an option to encrypt InnoDB tablespaces, other files which also store data in some form (for example, binary logs, general log, slow query log) are not encrypted. This allows the toolchain (MySQL Enterprise Backup but also xtrabackup, mysqldump, mysqlbinlog) to work correctly with such setup.

Starting from MySQL 5.7.11, the community version of MySQL also got support for InnoDB tablespace encryption. The main difference compared to the enterprise offering is the way the keys are stored – keys are not located in a secure vault, which is required for regulatory compliance. This means that starting from Percona Server 5.7.11, it is also possible to encrypt InnoDB tablespace. In the recently published Percona Server 5.7.20, support for encrypting binary logs has been added. It is also possible to integrate with Hashicorp Vault server via a keyring_vault plugin, matching (and even extending – binary log encryption) the features available in Oracle’s MySQL Enterprise edition.

MariaDB added support for data encryption in 10.1.3 – it is a separate, enhanced implementation. It gives you the possibility to not only encrypt InnoDB tablespaces, but also InnoDB log files. As a result, data is more secure but some of the tools won’t work in such configuration. Xtrabackup will not work with encrypted redo logs – MariaDB created a fork, MariaDB Backup, which adds support for MariaDB encryption. There are also issues with mysqlbinlog.

No matter which MySQL flavor you use, as long as it is a recent version, you would have options to implement data at rest encryption via the database server, making sure that your data is additionally secured.

Securing MySQL or MariaDB is not trivial, but we hope these 10 tips will help you along the way.

Summary

In today’s landscape, data security is a top priority for every database administrator. Whether your motivation is compliance with regulatory requirements or protecting your customers and your business’ reputation, these ten tips for securing your MySQL and MariaDB databases will help you further secure your infrastructure and give you peace of mind.

There are numerous measures to consider when ensuring your database infrastructure is secure. In this post, we’ve covered the fundamentals such as data encryption, network access controls, user authentication and privileges, operating system security, and more.

Database management automation software, like ClusterControl, can be a great tool to aid in your overall database security efforts. If you’re looking for a deeper dive into each step you must take to secure your MySQL databases, be sure to check out Part 1 and Part 2 of our series on How to Secure MySQL. To stay informed on other database management best practices, follow us on Twitter, LinkedIn, and subscribe to our newsletter for updates.