MDEV-27124: Update definer of Add/DropGeometryColumn procedures from 'root' to 'mariadb.sys'

From 10.4.13, the `mariadb.sys` user was created to replace `root` definers.
 - In commit 0253ea7f2208354b187ffcfa2f3128878597cc11, definer of
   Add/DropGeometryColumn procedures was changed to `mariadb.sys`, in
   `scripts/maria_add_gis_sp.sql.in`.
   However, maria_add_gis_sp.sql only applies to new databases created by
   installation script. Databases upgraded from old versions will miss this
   change.
 - In addition, according to commit
   0d6d801e5886208b2632247d88da106a543e1032(MDEV-23102), in some scenarios
   when root user is replaced it will skip creating `mariadb.sys` user.

This commit is to update the definer from `root` to `mariadb.sys` during
upgrade. It only makes the change if the original definers are root.

Doesn't choose to execute `maria_add_gis_sp.sql` in upgrade script to
recreate the procedures is because of considering the scenarios of
MDEV-23102 that `root` user is replaced and `mariadb.sys` is not created.

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services, Inc.

3 files changed, 194 insertions, 0 deletions

diff --git a/mysql-test/main/upgrade_geometrycolumn_procedure_definer.result b/mysql-test/main/upgrade_geometrycolumn_procedure_definer.result
new file mode 100644
index 00000000000..58cd91f706d
--- /dev/null
+++ b/mysql-test/main/upgrade_geometrycolumn_procedure_definer.result
@@ -0,0 +1,123 @@
+#
+# The definer of Add/DropGeometryColumn procedures should be updated to mariadb.sys during upgrade
+#
+use mysql;
+create table save_proc like proc;
+insert into save_proc select * from proc;
+set @save_sql_mode= @@sql_mode;
+#
+# If the definer is root before the upgrade:
+# Drop the procedures if exists and recreate with root definer
+#
+DROP PROCEDURE IF EXISTS AddGeometryColumn;
+DROP PROCEDURE IF EXISTS DropGeometryColumn;
+CREATE DEFINER=`root`@`localhost` PROCEDURE AddGeometryColumn(catalog varchar(64), t_schema varchar(64),
+t_name varchar(64), geometry_column varchar(64), t_srid int) SQL SECURITY INVOKER
+begin
+set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end |
+CREATE DEFINER=`root`@`localhost` PROCEDURE DropGeometryColumn(catalog varchar(64), t_schema varchar(64),
+t_name varchar(64), geometry_column varchar(64)) SQL SECURITY INVOKER
+begin
+set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end |
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost';
+count(*)
+1
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost';
+count(*)
+1
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+0
+#
+# Run mysql_upgrade
+#
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.global_priv                                  OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.save_proc                                    OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.transaction_registry
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+
+Repairing tables
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.transaction_registry
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+mysql.user                                         OK
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+#
+# check new definers of Add/DropGeometryColumn
+#
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+1
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+1
+#
+# restore environment
+#
+delete from proc;
+rename table proc to bad_proc;
+rename table save_proc to proc;
+drop table bad_proc;
+flush privileges;
diff --git a/mysql-test/main/upgrade_geometrycolumn_procedure_definer.test b/mysql-test/main/upgrade_geometrycolumn_procedure_definer.test
new file mode 100644
index 00000000000..5111fcdf4ea
--- /dev/null
+++ b/mysql-test/main/upgrade_geometrycolumn_procedure_definer.test
@@ -0,0 +1,66 @@
+--echo #
+--echo # The definer of Add/DropGeometryColumn procedures should be updated to mariadb.sys during upgrade
+--echo #
+
+--source include/mysql_upgrade_preparation.inc
+
+use mysql;
+
+create table save_proc like proc;
+insert into save_proc select * from proc;
+set @save_sql_mode= @@sql_mode;
+
+--echo #
+--echo # If the definer is root before the upgrade:
+--echo # Drop the procedures if exists and recreate with root definer
+--echo #
+DROP PROCEDURE IF EXISTS AddGeometryColumn;
+DROP PROCEDURE IF EXISTS DropGeometryColumn;
+
+DELIMITER |;
+
+CREATE DEFINER=`root`@`localhost` PROCEDURE AddGeometryColumn(catalog varchar(64), t_schema varchar(64),
+   t_name varchar(64), geometry_column varchar(64), t_srid int) SQL SECURITY INVOKER
+begin
+  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end |
+
+CREATE DEFINER=`root`@`localhost` PROCEDURE DropGeometryColumn(catalog varchar(64), t_schema varchar(64),
+   t_name varchar(64), geometry_column varchar(64)) SQL SECURITY INVOKER
+begin
+  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end |
+
+DELIMITER ;|
+
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost';
+
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--echo #
+--echo # Run mysql_upgrade
+--echo #
+--exec $MYSQL_UPGRADE  2>&1
+--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+--echo #
+--echo # check new definers of Add/DropGeometryColumn
+--echo #
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost';
+
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+
+--echo #
+--echo # restore environment
+--echo #
+delete from proc;
+rename table proc to bad_proc;
+rename table save_proc to proc;
+drop table bad_proc;
+flush privileges;
+
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index ec3d532ec11..fdfcd99b91b 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -510,6 +510,11 @@ ALTER TABLE proc MODIFY comment
 ALTER TABLE proc ADD aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL
                      AFTER body_utf8;
 
+# Update definer of Add/DropGeometryColumn procedures to 'mariadb.sys'
+# To consider the scenarios in MDEV-23102, only update the definer when it's 'root'
+UPDATE proc SET Definer = 'mariadb.sys@localhost' WHERE Definer = 'root@localhost' AND Name = 'AddGeometryColumn';
+UPDATE proc SET Definer = 'mariadb.sys@localhost' WHERE Definer = 'root@localhost' AND Name = 'DropGeometryColumn';
+
 #
 # EVENT privilege
 #