From 8977ad6c7772cf132c0a9400a0a6dbde31c17fe7 Mon Sep 17 00:00:00 2001 From: Anel Husakovic Date: Tue, 6 Apr 2021 16:02:53 +0200 Subject: mysql_secure_installation redesign Patch includes redesign of the script and solves following MDEVs: - MDEV-22486: mysql_secure_installation cannot work without root user in the database - MDEV-25169 Secure installation with normal user fails to accept empty root password - MDEV-10112: mysql_secure_installation should use GRANT, REVOKE, etc for galera support - MDEV-19316: mysql_secure_installation should offer to rename root user Closes PR #1288 - Adding test case for the script evaluation Co-author: Daniel Black Reviewed by: daniel@mariadb.org serg@mariadb.com --- mysql-test/main/mysql_secure_installation.result | 70 ++++ mysql-test/main/mysql_secure_installation.test | 43 +++ mysql-test/mysql-test-run.pl | 11 + scripts/mysql_secure_installation.sh | 410 ++++++++++++++--------- 4 files changed, 375 insertions(+), 159 deletions(-) create mode 100644 mysql-test/main/mysql_secure_installation.result create mode 100644 mysql-test/main/mysql_secure_installation.test diff --git a/mysql-test/main/mysql_secure_installation.result b/mysql-test/main/mysql_secure_installation.result new file mode 100644 index 00000000000..9811c09c931 --- /dev/null +++ b/mysql-test/main/mysql_secure_installation.result @@ -0,0 +1,70 @@ +CREATE USER foobar@localhost IDENTIFIED BY "bar"; +GRANT ALL PRIVILEGES ON *.* TO foobar@localhost; +SELECT user FROM mysql.global_priv ORDER BY user; +user +foobar +mariadb.sys +root +root +root +root +SHOW DATABASES; +Database +information_schema +mtr +mysql +performance_schema +test + +NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB + SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! + +In order to log into MariaDB to secure it, we'll need the current +password for a privileged user. If you've just installed MariaDB, and +haven't set a privileged password yet, you should just press enter here. + +For which user do you want to specify a password (press enter for USERNAME): +Enter current password for user foobar (enter for none): +OK, successfully used password, moving on... + + +Set user: foobar password? [Y/n] New password: +Re-enter new password: +Password updated successfully! + +By default, a MariaDB installation has an anonymous user, allowing anyone +to log into MariaDB without having to have a user account created for +them. This is intended only for testing, and to make the installation +go a bit smoother. You should remove them before moving into a +production environment. + +Remove anonymous users? [Y/n] ... Success! + +By default, MariaDB comes with a database named 'test' that anyone can +access. This is also intended only for testing, and should be removed +before moving into a production environment. + + - Checking the test databases... +Remove test database and access to it? [Y/n] - Dropping test database... + ... Success! + - Removing privileges on test database... + ... Success! + +Normally, root should only be allowed to connect from 'localhost'. This +ensures that someone cannot guess at the root password from the network. + +Disallow root login remotely? [Y/n] ... Success! + +Cleaning up... + +All done! If you've completed all of the above steps, your MariaDB +installation should now be secure. + +Thanks for using MariaDB! +SELECT user FROM mysql.global_priv ORDER BY user; +user +foobar +mariadb.sys +root +# Kill the server +# restart diff --git a/mysql-test/main/mysql_secure_installation.test b/mysql-test/main/mysql_secure_installation.test new file mode 100644 index 00000000000..f15b3bc0e55 --- /dev/null +++ b/mysql-test/main/mysql_secure_installation.test @@ -0,0 +1,43 @@ +--source include/not_windows.inc + +CREATE USER foobar@localhost IDENTIFIED BY "bar"; +GRANT ALL PRIVILEGES ON *.* TO foobar@localhost; +SELECT user FROM mysql.global_priv ORDER BY user; +SHOW DATABASES; + +# Creating a temporary text file. +--write_file $MYSQLTEST_VARDIR/tmp/mariadb_secure_installation.txt +foobar +bar +Y +secret +secret +Y + + +EOF + + +--replace_result $USER USERNAME +--exec $MYSQL_SECURE_INSTALLATION -S $MASTER_MYSOCK< $MYSQLTEST_VARDIR/tmp/mariadb_secure_installation.txt + +SELECT user FROM mysql.global_priv ORDER BY user; + +--remove_file $MYSQLTEST_VARDIR/tmp/mariadb_secure_installation.txt +--let MYSQLD_DATADIR= `select @@datadir` +--source include/kill_mysqld.inc +# No need to clean anything since the datadir will be removed +--rmdir $MYSQLD_DATADIR + +perl; +use lib "lib"; +use My::Handles { suppress_init_messages => 1 }; +use My::File::Path; +my $install_db_dir = ($ENV{MTR_PARALLEL} == 1) ? + "$ENV{'MYSQLTEST_VARDIR'}/install.db" : + "$ENV{'MYSQLTEST_VARDIR'}/../install.db"; +copytree($install_db_dir, $ENV{'MYSQLD_DATADIR'}); +EOF + +--let $restart_parameters= $old_restart_parameters +--source include/start_mysqld.inc diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index e4ecc910556..325f792d9fb 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2165,6 +2165,17 @@ sub environment_setup { $ENV{'MYSQLHOTCOPY'}= $mysqlhotcopy; } + # ---------------------------------------------------- + # mysql_secure_installation + # ---------------------------------------------------- + my $mysql_secure_installation= + mtr_pl_maybe_exists("$bindir/scripts/mysql_secure_installation") || + mtr_pl_maybe_exists("$path_client_bindir/mysql_secure_installation"); + if ($mysql_secure_installation) + { + $ENV{'MYSQL_SECURE_INSTALLATION'}= $mysql_secure_installation; + } + # ---------------------------------------------------- # perror # ---------------------------------------------------- diff --git a/scripts/mysql_secure_installation.sh b/scripts/mysql_secure_installation.sh index b2a9edf4953..11054ab079a 100644 --- a/scripts/mysql_secure_installation.sh +++ b/scripts/mysql_secure_installation.sh @@ -1,6 +1,7 @@ #!/bin/sh # Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved. +# Copyright (c) 2021, MariaDB Foundation # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -21,7 +22,12 @@ output=".my.output.$$" trap "interrupt" 1 2 3 6 15 -rootpass="" +args= +user="" +password="" +host= +set_from_cli=0 +emptyuser=0 echo_n= echo_c= basedir= @@ -48,11 +54,12 @@ parse_arguments() for arg do + val=$(parse_arg "$arg") case "$arg" in - --basedir=*) basedir=`parse_arg "$arg"` ;; - --defaults-file=*) defaults_file="$arg" ;; - --defaults-extra-file=*) defaults_extra_file="$arg" ;; - --no-defaults) no_defaults="$arg" ;; + --basedir=*) basedir="$val";; + --defaults-file=*) defaults_file="$val" ;; + --defaults-extra-file=*) defaults_extra_file="$val" ;; + --no-defaults) no_defaults="$val" ;; *) if test -n "$pick_args" then @@ -61,6 +68,11 @@ parse_arguments() # XXX: This is broken; true fix requires using eval and proper # quoting of every single arg ($basedir, $ldata, etc.) #args="$args "`echo "$arg" | sed -e 's,\([^a-zA-Z0-9_.-]\),\\\\\1,g'` + case $arg in + --user=*) user="$val" set_from_cli=1;; + --password=*) password="$val";; + --host=*) host="$val";; + esac args="$args $arg" fi ;; @@ -181,8 +193,7 @@ then cannot_find_file "$mysql_command" exit 1 fi - -# Now we can get arguments from the group [client] and [client-server] +# Now we can get arguments from the group [client], [client-server] and [client-mariadb] # in the my.cfg file, then re-run to merge with command line arguments. parse_arguments `$print_defaults $defaults_file $defaults_extra_file $no_defaults client client-server client-mariadb` parse_arguments PICK-ARGS-FROM-ARGV "$@" @@ -197,8 +208,9 @@ set_echo_compat() { validate_reply () { ret=0 + local default=${2:-y} if [ -z "$1" ]; then - reply=y + reply=$default return $ret fi case $1 in @@ -215,9 +227,15 @@ prepare() { } do_query() { - echo "$1" >$command - #sed 's,^,> ,' < $command # Debugging - $mysql_command --defaults-file=$config $defaults_extra_file $no_defaults $args <$command >$output + if [ -n "$1" ] + then + echo "$1" >$command + #sed 's,^,> ,' < $command # Debugging + $mysql_command --defaults-file=$config $defaults_extra_file $no_defaults --skip-column-names --batch $args <$command >$output + else + # rely on stdin + $mysql_command --defaults-file=$config $defaults_extra_file $no_defaults --skip-column-names --batch $args >$output + fi return $? } @@ -242,15 +260,16 @@ basic_single_escape () { } # -# create a simple my.cnf file to be able to pass the root password to the mysql +# create a simple my.cnf file to be able to pass the user password to the mysql # client without putting it on the command line # make_config() { echo "# mysql_secure_installation config file" >$config echo "[mysql]" >>$config - echo "user=root" >>$config - esc_pass=`basic_single_escape "$rootpass"` + echo "user=$user" >>$config + esc_pass=`basic_single_escape "$password"` echo "password='$esc_pass'" >>$config + echo "${host:+host=$host}" >>$config #sed 's,^,> ,' < $config # Debugging if test -n "$defaults_file" @@ -260,123 +279,189 @@ make_config() { fi } -get_root_password() { - status=1 - while [ $status -eq 1 ]; do - stty -echo - echo $echo_n "Enter current password for root (enter for none): $echo_c" - read password - echo - stty echo - if [ "x$password" = "x" ]; then - emptypass=1 - else - emptypass=0 - fi - rootpass=$password - make_config - do_query "show create user root@localhost" - status=$? +get_user_and_password() { + status_priv_user=1 + while [ $status_priv_user -ne 0 ]; do + if test -z "$user"; then + echo $echo_n "For which user do you want to specify a password (press enter for $USER): $echo_c" + read user || interrupt + echo + if [ "x$user" = "x" ]; then + emptyuser=1 + user=$USER + else + emptyuser=0 + fi + fi + if [ -z "$password" ] && [ "$emptyuser" -eq 0 ]; then + stty -echo 2>/dev/null + # If the empty user it means we are connecting with unix_socket else need password + echo $echo_n "Enter current password for user $user (enter for none): $echo_c" + read password || interrupt + echo + stty echo + fi + make_config + # Only privileged user that has access to mysql DB can make changes + do_query "use mysql" + status_priv_user=$? + if test $status_priv_user -ne 0; then + echo "Only privileged user can make changes to mysql DB." + if test $set_from_cli -eq 1; then + clean_and_exit + fi + user= + password= + fi done - if grep -q unix_socket $output; then - emptypass=0 + do_query "show create user" + if grep -q unix_socket "$output"; then + unix_socket_auth=1 + else + unix_socket_auth=0 + fi + if grep -q "USING '" "$output"; then + password_set=1 + else + password_set=0 fi + read -r show_create < "$output" || interrupt echo "OK, successfully used password, moving on..." echo } -set_root_password() { - stty -echo +set_user_password() { + stty -echo 2>/dev/null echo $echo_n "New password: $echo_c" - read password1 + read password1 || interrupt echo echo $echo_n "Re-enter new password: $echo_c" - read password2 + read password || interrupt echo stty echo - if [ "$password1" != "$password2" ]; then - echo "Sorry, passwords do not match." - echo - return 1 + if [ "$password1" != "$password" ]; then + echo "Sorry, passwords do not match." + echo + return 1 fi if [ "$password1" = "" ]; then - echo "Sorry, you can't use an empty password here." - echo - return 1 + echo "Sorry, you can't use an empty password here." + echo + return 1 fi - - esc_pass=`basic_single_escape "$password1"` - do_query "UPDATE mysql.global_priv SET priv=json_set(priv, '$.plugin', 'mysql_native_password', '$.authentication_string', PASSWORD('$esc_pass')) WHERE User='root';" + esc_pass=$(basic_single_escape "$password1") + do_query "SET PASSWORD = PASSWORD('$esc_pass')" if [ $? -eq 0 ]; then - echo "Password updated successfully!" - echo "Reloading privilege tables.." - reload_privilege_tables - if [ $? -eq 1 ]; then - clean_and_exit - fi - echo - rootpass=$password1 - make_config + echo "Password updated successfully!" else - echo "Password update failed!" - clean_and_exit + echo "Password update failed!" + clean_and_exit fi + args="$args --password=$password" + make_config return 0 } remove_anonymous_users() { - do_query "DELETE FROM mysql.global_priv WHERE User='';" + do_query <