We have left database security as a more or less untouched topic for too long and I hope to push that forward in the next weeks.
Good practice is to set a proper DB root password as well as add a less privileged account for the actual application. If we go that route we’d need to ask the user for two distinct new passwords or generate those and display to the user. We need a hint that those should be stored in the password manager of choice or written down. But as well I feel that we should prepare a detailed wiki article on how to reset the DB root password on all distros just in case people have locked themselves out. Who’s up for that?
I intend to use mysql_secure_installation command as well.
In case people choose to let the installer generate a password I’d prefer to set a semi complex password (numbers, lower case, upper case and a couple special characters) but only 8 characters in length. That way we have a chance that people actually note it down. -> Not sure if that’s a great idea, open for discussion.
I’ll probably need to setup a whole park of VMs to test the changes before we actually release this to the public. Otherwise we’ll have a massive storm of questions in the forums and installations to fix up later on I fear. This is because we have different versions of MySQL/MariaDB in the distros and some behave differently as far as I know. We have @Wayne-Workman’s awesome installer test park running but I have a feeling that we won’t cover it all with that. We need fresh install testing but even more we need testing on upgrade installations (one with empty root password and another one with a password already set). I think I can do a fair bit of that in my VM test setup I have on my working laptop using snapshots. But I am not sure I can do it for all distros. @Moderators @Testers Anyone around who’d do the tests for one or the other distro?
Please post here if you have more ideas on this or if I have left out something important!!
Issues that we might run into as setting the password has changed several times in different versions of MySQL and MariaDB:
Versions:
- Ubuntu 18.04: mysql-5.7.24, php-7.2.10 oder mariadb-10.1.34
- Arch: mariadb-10.1.37, php-7.3.0
- Fedora 29: mariadb-10.3.11, 7.2.13
- RHEL 7: mariadb-5.5.60, php-5.6.39
- CentOS 7: mariadb-5.5.60, php-5.6.39
- Debian 9: mariadb-10.1.37, php-7.0.33
Creating a DB user account seems to be just as problematic across different MySQL/MariaDB versions:
CREATE USER IF NOT EXISTS or DROP USER IF EXISTS are not available before MySQL 5.7
- To get around this people propose to user
GRANT ... TO which should create a user if it doesn’t exist - doesn’t work in MySQL 8.0 though.
- So far the best I came up with is using a full blown SQL script to check if the user exists derived from scripts I found on the web (1, 2) - see below.
- Tested the script on:
- CentOS 8/MySQL8.0.? (works)
- CentOS 7/MariaDB 5.5.64 (works after deleting anonymous account via
DROP USER ''@'localhost';)
- Debian 9/MariaDB 10.1.38 (works)
- Debian 10/MariaDB 10.3.17 (works)
- Ubuntu 18.04.3 LTS/MariaDB 10.1.41 (works)
- Ubuntu 16.04.6 LTS and 18.04.3 LTS/MySQL 5.7.27 (works)
- Ubuntu 19.10/MySQL 8.0.17 (works)
- Ubuntu 19.10/MariaDB 10.3.17 (works)
- Testing
mysqladmin -u root -p password 'Passw0rd' command:
- CentOS 8/MySQL8.0.? (works)
- CentOS 7/MariaDB 5.5.64 (works)
- Debian 9/MariaDB 10.1.38 (works)
- Debian 10/MariaDB 10.3.17 (works)
- Ubuntu 18.04.3 LTS/MariaDB 10.1.41 (works)
- Ubuntu 16.04.6 LTS and 18.04.3 LTS/MySQL 5.7.27 (works)
- Ubuntu 19.10/MySQL 8.0.17 (works)
- Ubuntu 19.10/MariaDB 10.3.17 (works)