blog
MySQL vs MariaDB vs Percona Server: Security Features Comparison
Security of data is critical for any organisation. It’s an important aspect that can heavily influence the design of the database environment. When deciding upon which MySQL flavour to use, you need to take into consideration the security features available from the different server vendors. In this blog post, we’ll come up with a short comparison of the latest versions of the MySQL Community Edition from Oracle, Percona Server and MariaDB:
mysqld Ver 5.7.20-19 for Linux on x86_64 (Percona Server (GPL), Release 19, Revision 3c5d3e5d53c)
mysqld Ver 5.7.21 for Linux on x86_64 (MySQL Community Server (GPL))
mysqld Ver 10.2.12-MariaDB for Linux on x86_64 (MariaDB Server)We are going to use Centos 7 as the operating system – please keep in mind that results we present here may be slightly different on other distributions like Debian or Ubuntu. We’d also like to focus on the differences and will not cover the commonalities – Percona Server and MariaDB are flavors of MySQL, so some of the security features (e.g., how access privileges of MySQL files look like) are shared among them.
Initial Security
Users
Both Percona Server and MySQL Community Server comes with a randomly generated temporary password for the root user. You need to check the contents of MySQL’s error log to find it:
2018-01-19T13:47:45.532148Z 1 [Note] A temporary password is generated for root@localhost: palwJu7uSL,gOnce you log in, a password change is forced upon you:
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.21
Copyright (c) 2000, 2018, 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> select * from mysql.user;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.Password has to be strong enough, this is enforced by the validate_password plugin:
mysql> alter user root@localhost identified by 'password123.';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> alter user root@localhost identified by 'password123.A';
Query OK, 0 rows affected (0.00 sec)MariaDB does not generate a random root password and it provides passwordless access to the root account from (and only from) localhost.
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 12
Server version: 10.2.12-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)]> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)This is not a big issue during the initial deployment phase, as the DBA is supposed to configure and secure access to the database later on (by running mysql_secure_installation for example). The bigger problem here is that a good practice is not enforced by MariaDB. If you don’t have to setup a strong password for the root user, it could be that nobody changes it later and passwordless access will remain. Then this would become a serious security threat.
Another aspect we’d like to look at is anonymous, passwordless access. Anonymous users allow anyone to get in, it doesn’t have to be a predefined user. If such access is passwordless, it means that anyone can connect to MySQL. Typically such account has only USAGE privilege but even then it is possible to print a status (‘s’) which contains information like MySQL version, character set etc. Additionally, if ‘test’ schema is available, such user has the ability to write to that schema.
Both MySQL Community Server and Percona server do not have any anonymous users defined in MySQL:
mysql> select user, host, authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *EB965412B594F67C8EB611810EF8D406F2CF42BD |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)On the other hand, MariaDB is open for anonymous, passwordless access.
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------------------+----------+
| user | host | password |
+------+-----------------------+----------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
+------+-----------------------+----------+
6 rows in set (0.00 sec)In addition to that, the ‘test’ schema is available – which allows anonymous users to issue writes to the database.
[root@localhost ~]# mysql -umyanonymoususer
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 14
Server version: 10.2.12-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)]> use test;
Database changed
MariaDB [test]> CREATE TABLE mytab (a int);
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> INSERT INTO mytab VALUES (1), (2);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> SELECT * FROM mytab;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)This poses a serious threat, and needs to be sorted out. Else, it can be easily exploited to attempt to overload the server with writes.
Data in Transit Security
MySQL Community Server and both of its forks support the use of SSL to encrypt data in transit. This is extremely important for Wide Area Networks, but also shouldn’t be overlooked in a local network. SSL can be used both client and server-side. Regarding server-side configuration (to encrypt traffic from master to slaves, for example), it looks identical across the board. There is a difference though when it comes to client-side SSL encryption, introduced in MySQL 5.7. Prior to 5.7, one had to generate SSL keys and CA’s and define them in the configurations of both server and client. This is how MariaDB’s 10.2 SSL setup looks like. In both MySQL Community Server 5.7 and in Percona Server 5.7 (which is based on MySQL 5.7), there is no need to pre-generate keys. It is all done automatically, in the background. All you need to do is to enable SSL on your client by setting the correct ‘–ssl-mode’. For MySQL’s CLI client, this is not even needed as it enables SSL by default:
[root@localhost ~]# mysql -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.7.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> s
--------------
mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
Connection id: 6
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.21 MySQL Community Server (GPL)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 2 days 21 hours 51 min 52 sec
Threads: 1 Questions: 15 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.000
--------------On the other hand MariaDB would require additional configuration as SSL is disabled by default:
[root@localhost ~]# mysql -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 18
Server version: 10.2.12-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)]> s
--------------
mysql Ver 15.1 Distrib 10.2.12-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 18
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.2.12-MariaDB MariaDB Server
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 2 days 22 hours 26 min 58 sec
Threads: 7 Questions: 45 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 12 Queries per second avg: 0.000
--------------Data at Rest Encryption
First of all, backups – there are freely available backup tools like xtrabackup or MariaDB Backup (which is a fork of xtrabackup). These allow to create encrypted backups of all three MySQL flavors we discuss in this blog post.
All three flavours support encryption of the running database, but there are differences in what pieces of data are encrypted.
The MySQL Community Server supports encryption of InnoDB tablespaces only. Keys used for encryption are stored in files (which is not compliant with regulations – keys should be stored in a vault – something which MySQL Enterprise supports). Percona Server is based on MySQL Community Server, so it also supports encryption of InnoDB tablespaces. Recently, in Percona Server 5.7.20, support for encryption of general tablespaces (compared to only individual ones in previous versions and MySQL Community Edition) was added. Support for encryption of binary logs was also added. Percona Server comes with a keyring_vault plugin, which can be used to store keys in Hashicorp Vault server, making Percona Server 5.7.20 compliant with regulatory requirements regarding data at rest encryption.
MariaDB 10.2 has more advanced data-at-rest encryption support. In addition to tablespace and binary/relay log encryption, it has support for encrypting InnoDB redo logs. Currently, it is the more complete solution regarding data encryption.
Audit Logging
All three MySQL flavors have support for audit logging. Their scope is pretty much comparable: connect and disconnect events, queries executed, tables accessed. The logs contain information about which user participated in such event, from what host the user logged from, the time it happened, and similar info. Such events can be also logged via syslog and stored on an external log server to enable log analysis and parsing.
Data Masking, SQL Firewall
All of the discussed MySQL flavors work with some kind of tool which would allow implementing data masking, and would be able to block SQL traffic based on some rules. Data masking is a method of obfuscating some data outside of the database, but before it reaches client. An example would be credit card data which is stored in plain text in the database, but when a developer wants to query such data, she will see ‘xxxxxxxx…’ instead of numbers. The tools we are talking here are ProxySQL and MaxScale. MaxScale is a product of MariaDB Corporation, and is subscription-based. ProxySQL is a free to use database proxy. Both proxies can be used with any of the MySQL flavours.
That’s all for today folks. For further reading, check out these 10 tips for securing your MySQL and MariaDB databases.