mysql_secure_installation redesignbb-10.4-anel-mysql-secureinstall

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 <daniel@mariadb.org>
Reviewed by:
daniel@mariadb.org
serg@mariadb.com

3 files changed, 124 insertions, 0 deletions

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
@@ -2166,6 +2166,17 @@ sub environment_setup {
   }
 
   # ----------------------------------------------------
+  # 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
   # ----------------------------------------------------
   my $exe_perror= mtr_exe_exists("$bindir/extra$opt_vs_config/perror",