MariaDB Security Features and Best Practices

1 .MariaDB security features and best practices Robert Bindar Software Developer @MariaDB Foundation Percona Live Austin, 28-30 May 2019 

2 . Motivation - Users ● Potential public shaming through data breaches ● Massive loss of business ● Angry Clients ● Expensive lawsuits and fines ● And it’s getting worse as more people start realizing the impact of data security ● Bonus points for being compliant with data protection regulations MariaDB Security Features and Best Practices 2 https://mariadb.org 

3 . Motivation - MariaDB ● One of the most popular db servers ● Part of critical infrastructure worldwide ● Its main purpose is to manage data ● Very important for our users MariaDB Security Features and Best Practices 3 https://mariadb.org 

4 .Potential threats and defense mechanisms MariaDB Security Features and Best Practices 4 https://mariadb.org 

5 . Direct DB threats Threat Prevention • Man in the middle attacks • Limit/block outside TCP connections • Spoofing to MariaDB • Memory corruption exploits • Secure your DNS infrastructure • MariaDB should accept connections only from the application host • Use bind_address • Use TLS/SSL • Keep your OS updated • Keep your MariaDB Server updated MariaDB Security Features and Best Practices 5 https://mariadb.org 

6 . Application threats Threat Prevention • DOS attacks • Your MariaDB server should ideally • Data leaks/corruption run on a dedicated machine • SQL injection • Avoid running the application on the DB machine • Keep the DB machine as clean as possible • Have a strong permissions system • Application code security practices MariaDB Security Features and Best Practices 6 https://mariadb.org 

7 . Limiting Human Errors Threat Prevention • Genuine human mistakes • Limit sudo access on the MariaDB • Bad intentions server machine • Limit ssh access • Avoid running mysqld as root • Use specific hostnames instead of wildcards • Use secure_file_priv • Robust defaults MariaDB Security Features and Best Practices 7 https://mariadb.org 

8 .Secure Installations with mysql_secure_installation MariaDB Security Features and Best Practices 8 https://mariadb.org 

9 . mysql_secure_installation It won’t provide bullet-proof security for your deployment. This script just presents a basic set of recommended settings to get started. MariaDB Security Features and Best Practices 9 https://mariadb.org 

10 . mysql_secure_installation ● Set/Change root accounts passwords ● Delete root accounts accessible from outside ● Remove anonymous user accounts ● Remove test database ● FLUSH PRIVILEGES on the house! MariaDB Security Features and Best Practices 10 https://mariadb.org 

11 .Data Encryption MariaDB Security Features and Best Practices 11 https://mariadb.org 

12 . #define In-Transit Data: Data transmitted between clients and the MariaDB server, between server instances in replication or data transmitted within the Galera cluster. Defaults to unencrypted! At-Rest Data: Some of the data residing in persistent storage: tables, tablespaces, binary logs. Supported with InnoDB and XtraDB, partially with Aria. MariaDB Security Features and Best Practices 12 https://mariadb.org 

13 . Encryption Libraries in MariaDB MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'version_ssl_library'; +---------------------+----------------------------+ | Variable_name | Value | +---------------------+----------------------------+ | version_ssl_library | OpenSSL 1.1.0g 2 Nov 2017 | +---------------------+----------------------------+ ● MariaDB uses TLS ● static linking with yaSSL - server + client ● dynamic linking with OpenSSL - server + client ● dynamic linking with GnuTLS or Schannel - client ● have_ssl will tell you if TLS is supported/enabled MariaDB Security Features and Best Practices 13 https://mariadb.org 

14 . Server <-> Clients data encryption with TLS [mariadb] ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem ssl_key = /etc/my.cnf.d/certificates/server-key.pem ssl_ca = /etc/my.cnf.d/certificates/ca.pem ● Defaults to not encrypted ● have_ssl == YES means TLS is enabled ● FLUSH SSL reloads TLS context from 10.4 ● Two-way TLS is required if REQUIRE X509, REQUIRE SUBJECT, REQUIRE ISSUER are used for an account ● TLS can be required for specific accounts from untrusted hosts MariaDB Security Features and Best Practices 14 https://mariadb.org 

15 . Secure Connections in Replication MariaDB [(none)]> CHANGE MASTER TO MASTER_SSL_CA = '/path/to/ca/ca.pem', MASTER_SSL_VERIFY_SERVER_CERT=1; ● Defaults to not encrypted ● Enable TLS on all server instances ● Stop slaves and execute CHANGE MASTER ● Two-way TLS can also be enabled with CHANGE MASTER MariaDB Security Features and Best Practices 15 https://mariadb.org 

16 . Encryption for Galera Cluster [mariadb] … cert,key,ca wsrep_provider_options="socket.ssl_cert=/path/server-cert.pem; socket.ssl_key=/path/server-key.pem; socket.ssl_ca=/path/ca.pem" ● Defaults to not encrypted ● Enable TLS on each server node ● Add wsrep ssl options to cnf along the server ssl options ● Traffic is encrypted within the cluster and with external client connections ● Backup utilities also support encryption MariaDB Security Features and Best Practices 16 https://mariadb.org 

17 . At-Rest Data Encryption ● Encrypting some of the data on disk ● Overhead is estimated at 3-5% ● Supported fully with InnoDB and XtraDB SEs ● Aria support for ROW_FORMAT=PAGE tables ● You need to install an encryption management plugin ● Only helpful if the attacker is not an authorized MariaDB user MariaDB Security Features and Best Practices 17 https://mariadb.org 

18 .Account Management best practices MariaDB Security Features and Best Practices 18 https://mariadb.org 

19 . Password Validation Plugins Simple Password Cracklib Password Check Check • .so shipped with MariaDB - easy install • Not shipped by default with MariaDB • Minimum length • Checks password against a dictionary • Mixed case • Uses the CrackLib db • Alphanumeric checks • Can be used with PAM as of 10.4 • Special chars • Can be used with PAM as of 10.4 MariaDB Security Features and Best Practices 19 https://mariadb.org 

20 . Unix Socket Authentication MariaDB [(none)]> CREATE USER username@hostname IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.00 sec) ● Use OS credentials when connecting to MariaDB ● Enabled by default in 10.4.3 ● https://mariadb.org/authentication-in-mariadb-10-4/ MariaDB Security Features and Best Practices 20 https://mariadb.org 

21 . Account Locking ● Mark an account as locked and deny any subsequent connection requests for that account ● Minimum privilege package = no client connection at all ● Integrated solution for refusing client connections MariaDB Security Features and Best Practices 21 https://mariadb.org 

22 . Account Locking MariaDB [(none)]> CREATE USER user@localhost ACCOUNT LOCK; Query OK, 0 rows affected (0.00 sec) ● Creates a user account that is locked MariaDB Security Features and Best Practices 22 https://mariadb.org 

23 . Account Locking MariaDB [(none)]> SHOW CREATE USER user@localhost; +---------------------------------------------+ | CREATE USER for user@localhost | +---------------------------------------------+ | CREATE USER 'user'@'localhost' ACCOUNT LOCK | +---------------------------------------------+ 1 row in set (0.000 sec) ● SHOW CREATE USER displays the locking status of an account MariaDB Security Features and Best Practices 23 https://mariadb.org 

24 . Account Locking MariaDB [(none)]> ALTER USER user@localhost ACCOUNT UNLOCK; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> ALTER USER user@localhost ACCOUNT LOCK; Query OK, 0 rows affected (0.00 sec) ● Altering an existing account to lock/unlock MariaDB Security Features and Best Practices 24 https://mariadb.org 

25 . Account Locking bindar@computer:~/MariaDB/server$ ./client/mysql -uuser ERROR 4151 (HY000): Access denied, this account is locked ● Attempting a connection using a locked account returns ER_ACCOUNT_HAS_BEEN_LOCKED MariaDB Security Features and Best Practices 25 https://mariadb.org 

26 . Account Locking Whether an account is locked or not is checked during the authentication phase (including COM_CHANGE_USER). Locking an account does not affect existing connections. MariaDB Security Features and Best Practices 26 https://mariadb.org 

27 . Expiration of User Passwords ● A new connection with an expired password is either denied or only allowed to execute SET PASSWORD ● Supports expiring passwords with immediate effect, per-account automatic expiration as well as global policies for automatic expiration ● Compliance with latest security standards ● Fully compatible with MySQL 5.7 datadirs MariaDB Security Features and Best Practices 27 https://mariadb.org 

28 . Password Expiration MariaDB [(none)]> CREATE USER user@localhost PASSWORD EXPIRE; Query OK, 0 rows affected (0.00 sec) ● Creates a new account and expire the password with immediate effect MariaDB Security Features and Best Practices 28 https://mariadb.org 

29 . Password Expiration Disconnect Mode: In this mode, any new connections for accounts with expired passwords are refused. Sandbox Mode: A new connection for an account with the password expired is only allowed to execute SET PASSWORD to change the account password, attempts to execute any other statements are rejected. MariaDB Security Features and Best Practices 29 https://mariadb.org