DB-유

Securing a Fresh MySQL Installation with mysql_secure_installation

· mysql, security, linux

I once logged into a freshly built MySQL box and found an anonymous user and a world-writable test database sitting there, three weeks after the server went live. Nobody had run the cleanup script. Since then it is the first thing I do after mysqld starts for the first time.

A stock install can leave you with a root account with no password, anonymous users that can log in from localhost, that test database, and sometimes root accepting remote connections. Fine on a laptop, not fine on a server on the network. The script is mysql_secure_installation, and I use it on CentOS 7 and Ubuntu 16.04 with MySQL 5.6/5.7 or MariaDB 10.x. You need root or sudo, and on 5.7 from the Oracle repository you also need the temporary root password.

Find the initial root password

On Ubuntu 16.04 the mysql-server package asks you to set a root password during apt-get install, so you already have it. On CentOS 7 with the MySQL 5.7 community repository, the server generates a random password on first start and writes it to the error log:

sudo systemctl start mysqld
sudo grep 'temporary password' /var/log/mysqld.log

You will see a line similar to A temporary password is generated for root@localhost: k;Xj8rG&f0tQ. Copy it, because the script needs it in the next step. With MariaDB on CentOS 7 the root account has an empty password after installation, so just press Enter when asked.

Run the script

sudo mysql_secure_installation

The script asks a series of yes/no questions. On MySQL 5.7 the sequence is:

  1. Enter the current root password (the temporary one from the log).
  2. Whether to enable the validate_password plugin. I say yes on production servers because I don't trust myself to pick a good password at 2 a.m.; it enforces a minimum length and character mix for every password set afterwards.
  3. Set a new root password. With validate_password at the default MEDIUM level you need at least 8 characters with upper, lower, numeric and special characters.
  4. Remove anonymous users: yes.
  5. Disallow root login remotely: yes. Application accounts should connect remotely, not root.
  6. Remove the test database and access to it: yes.
  7. Reload privilege tables: yes.

On MariaDB the questions are the same minus the validate_password prompt. If you want to script this on many servers, note that MariaDB's version of the script also accepts input on stdin, which is handy for Ansible or a kickstart post-install section.

Doing it by hand instead

If you can't use the interactive script (or don't like it), a few statements get the same result. Log in and run:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3w-Str0ng-P@ss';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
FLUSH PRIVILEGES;

On MySQL 5.6 and MariaDB use SET PASSWORD FOR 'root'@'localhost' = PASSWORD('...') instead of ALTER USER, which does not exist there.

Keep the password out of shell history

I never type the root password on the command line, because it ends up in history. A protected client file does the job:

sudo tee /root/.my.cnf > /dev/null <<'EOF'
[client]
user=root
password=N3w-Str0ng-P@ss
EOF
sudo chmod 600 /root/.my.cnf

Now sudo mysql logs in without a prompt, and cron jobs running as root can use mysqldump without embedding the password in the script.

Did it work?

Check that only the expected accounts remain and that none of them has an empty password:

SELECT User, Host, plugin, authentication_string = '' AS empty_pw
FROM mysql.user;
SHOW DATABASES;

You should see root@localhost, mysql.sys@localhost (MySQL 5.7 only) and possibly debian-sys-maint@localhost on Ubuntu. The test database must be gone. Also confirm that the server is not listening on all interfaces unless you need it to:

sudo ss -tlnp | grep 3306

If it shows *:3306 and you only need local access, set bind-address = 127.0.0.1 under [mysqld] in /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu) and restart the service.

Things that bit me

  • ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) right after installation on CentOS 7: the temporary password expired or you copied it with a trailing character. Restart with --skip-grant-tables only as a last resort; usually rerunning grep 'temporary password' and pasting carefully fixes it.
  • ERROR 1819 (HY000): Your password does not satisfy the current policy requirements: the validate_password plugin rejected the password. Either pick a stronger one or lower the policy with SET GLOBAL validate_password_policy=LOW; for this session.
  • On Ubuntu 16.04, sudo mysql works without a password even after setting one. That is expected: the root account uses the auth_socket plugin, which trusts the OS user. Applications connecting over TCP still need a password.
  • mysql_secure_installation hangs on the first prompt: the server is not running. Check systemctl status mysqld (CentOS) or systemctl status mysql (Ubuntu).

It's ten minutes of work. It won't replace a firewall rule on port 3306 or per-application accounts, but do it before the first schema is loaded.