MDEV-23102 10.4 create mariadb.sys user on each update even is the user is not needed

Check if we really need the mariadb.sys user

6 files changed, 862 insertions, 4 deletions

diff --git a/mysql-test/main/upgrade_MDEV-19650.test b/mysql-test/main/upgrade_MDEV-19650.test
index 89cbfc8c7aa..a2f9fee705f 100644
--- a/mysql-test/main/upgrade_MDEV-19650.test
+++ b/mysql-test/main/upgrade_MDEV-19650.test
@@ -70,11 +70,11 @@ CREATE DEFINER='root'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user A
 
 SET sql_mode='';
 
-delimiter |;
-
 DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn;
 DROP PROCEDURE IF EXISTS mysql.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
diff --git a/mysql-test/main/upgrade_MDEV-23102-1.result b/mysql-test/main/upgrade_MDEV-23102-1.result
new file mode 100644
index 00000000000..8caa49180ee
--- /dev/null
+++ b/mysql-test/main/upgrade_MDEV-23102-1.result
@@ -0,0 +1,286 @@
+#
+# MDEV-23102: 10.4 create mariadb.sys user on each update even
+# is the user is not needed
+#
+# part 1: reassigning mysql.user and gis procedures to other user
+call mtr.add_suppression("Cannot load from mysql.proc. The table is probably corrupted");
+create database mysqltest1;
+use mysqltest1;
+create table save_global_priv as select * from mysql.global_priv;
+create table save_tables_priv as select * from mysql.tables_priv;
+create table save_proxies_priv as select * from mysql.proxies_priv;
+create table mysql.save_proc like mysql.proc;
+insert into mysql.save_proc select * from mysql.proc;
+set @save_sql_mode= @@sql_mode;
+use mysql;
+# make changed definition of gis procedures and user view
+create user superuser@localhost;
+grant all privileges on mysql.* to superuser@localhost;
+drop view user;
+CREATE DEFINER='superuser'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT
+Host,
+User,
+IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password,
+IF(JSON_VALUE(Priv, '$.access') &         1, 'Y', 'N') AS Select_priv,
+IF(JSON_VALUE(Priv, '$.access') &         2, 'Y', 'N') AS Insert_priv,
+IF(JSON_VALUE(Priv, '$.access') &         4, 'Y', 'N') AS Update_priv,
+IF(JSON_VALUE(Priv, '$.access') &         8, 'Y', 'N') AS Delete_priv,
+IF(JSON_VALUE(Priv, '$.access') &        16, 'Y', 'N') AS Create_priv,
+IF(JSON_VALUE(Priv, '$.access') &        32, 'Y', 'N') AS Drop_priv,
+IF(JSON_VALUE(Priv, '$.access') &        64, 'Y', 'N') AS Reload_priv,
+IF(JSON_VALUE(Priv, '$.access') &       128, 'Y', 'N') AS Shutdown_priv,
+IF(JSON_VALUE(Priv, '$.access') &       256, 'Y', 'N') AS Process_priv,
+IF(JSON_VALUE(Priv, '$.access') &       512, 'Y', 'N') AS File_priv,
+IF(JSON_VALUE(Priv, '$.access') &      1024, 'Y', 'N') AS Grant_priv,
+IF(JSON_VALUE(Priv, '$.access') &      2048, 'Y', 'N') AS References_priv,
+IF(JSON_VALUE(Priv, '$.access') &      4096, 'Y', 'N') AS Index_priv,
+IF(JSON_VALUE(Priv, '$.access') &      8192, 'Y', 'N') AS Alter_priv,
+IF(JSON_VALUE(Priv, '$.access') &     16384, 'Y', 'N') AS Show_db_priv,
+IF(JSON_VALUE(Priv, '$.access') &     32768, 'Y', 'N') AS Super_priv,
+IF(JSON_VALUE(Priv, '$.access') &     65536, 'Y', 'N') AS Create_tmp_table_priv,
+IF(JSON_VALUE(Priv, '$.access') &    131072, 'Y', 'N') AS Lock_tables_priv,
+IF(JSON_VALUE(Priv, '$.access') &    262144, 'Y', 'N') AS Execute_priv,
+IF(JSON_VALUE(Priv, '$.access') &    524288, 'Y', 'N') AS Repl_slave_priv,
+IF(JSON_VALUE(Priv, '$.access') &   1048576, 'Y', 'N') AS Repl_client_priv,
+IF(JSON_VALUE(Priv, '$.access') &   2097152, 'Y', 'N') AS Create_view_priv,
+IF(JSON_VALUE(Priv, '$.access') &   4194304, 'Y', 'N') AS Show_view_priv,
+IF(JSON_VALUE(Priv, '$.access') &   8388608, 'Y', 'N') AS Create_routine_priv,
+IF(JSON_VALUE(Priv, '$.access') &  16777216, 'Y', 'N') AS Alter_routine_priv,
+IF(JSON_VALUE(Priv, '$.access') &  33554432, 'Y', 'N') AS Create_user_priv,
+IF(JSON_VALUE(Priv, '$.access') &  67108864, 'Y', 'N') AS Event_priv,
+IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv,
+IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv,
+IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv,
+ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type,
+IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher,
+IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer,
+IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections,
+IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin,
+IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string,
+'N' AS password_expired,
+ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role,
+IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time
+FROM global_priv;
+SET sql_mode='';
+DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn;
+DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn;
+CREATE DEFINER=`superuser`@`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=`superuser`@`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 |
+set @@sql_mode= @save_sql_mode;
+drop user 'mariadb.sys'@'localhost';
+# check changed definitions mysql_upgrade
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+count(*)
+1
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'superuser@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 = 'superuser@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(*)
+0
+SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
+SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
+# 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
+mysqltest1
+mysqltest1.save_global_priv                        OK
+mysqltest1.save_proxies_priv                       OK
+mysqltest1.save_tables_priv                        OK
+performance_schema
+test
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+# check new definitions mysql_upgrade
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+count(*)
+1
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'superuser@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 = 'superuser@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(*)
+0
+select count(*) from global_priv where user='mariadb.sys' and host='localhost';
+count(*)
+0
+# above should be 0 (no mariadb.sys@localhost user)
+# restore environment
+DROP USER 'superuser'@'localhost';
+DROP VIEW mysql.user;
+DROP PROCEDURE AddGeometryColumn;
+DROP PROCEDURE DropGeometryColumn;
+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
+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
+mysqltest1
+mysqltest1.save_global_priv                        OK
+mysqltest1.save_proxies_priv                       OK
+mysqltest1.save_tables_priv                        OK
+performance_schema
+test
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+delete from global_priv;
+delete from tables_priv;
+delete from proxies_priv;
+delete from proc;
+insert into mysql.global_priv select * from mysqltest1.save_global_priv;
+insert into mysql.tables_priv select * from mysqltest1.save_tables_priv;
+insert into mysql.proxies_priv select * from mysqltest1.save_proxies_priv;
+rename table proc to bad_proc;
+rename table save_proc to proc;
+drop table bad_proc;
+flush privileges;
+disconnect default;
+connect  default,localhost,root,,;
+connection default;
+drop database mysqltest1;
+# End of 10.4 tests (but better do not add other tests here)
diff --git a/mysql-test/main/upgrade_MDEV-23102-1.test b/mysql-test/main/upgrade_MDEV-23102-1.test
new file mode 100644
index 00000000000..8491ddfffe4
--- /dev/null
+++ b/mysql-test/main/upgrade_MDEV-23102-1.test
@@ -0,0 +1,163 @@
+--echo #
+--echo # MDEV-23102: 10.4 create mariadb.sys user on each update even
+--echo # is the user is not needed
+--echo #
+--echo # part 1: reassigning mysql.user and gis procedures to other user
+
+--source include/mysql_upgrade_preparation.inc
+
+call mtr.add_suppression("Cannot load from mysql.proc. The table is probably corrupted");
+create database mysqltest1;
+use mysqltest1;
+create table save_global_priv as select * from mysql.global_priv;
+create table save_tables_priv as select * from mysql.tables_priv;
+create table save_proxies_priv as select * from mysql.proxies_priv;
+create table mysql.save_proc like mysql.proc;
+insert into mysql.save_proc select * from mysql.proc;
+set @save_sql_mode= @@sql_mode;
+
+use mysql;
+
+--echo # make changed definition of gis procedures and user view
+
+create user superuser@localhost;
+grant all privileges on mysql.* to superuser@localhost;
+
+drop view user;
+
+CREATE DEFINER='superuser'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT
+  Host,
+  User,
+  IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password,
+  IF(JSON_VALUE(Priv, '$.access') &         1, 'Y', 'N') AS Select_priv,
+  IF(JSON_VALUE(Priv, '$.access') &         2, 'Y', 'N') AS Insert_priv,
+  IF(JSON_VALUE(Priv, '$.access') &         4, 'Y', 'N') AS Update_priv,
+  IF(JSON_VALUE(Priv, '$.access') &         8, 'Y', 'N') AS Delete_priv,
+  IF(JSON_VALUE(Priv, '$.access') &        16, 'Y', 'N') AS Create_priv,
+  IF(JSON_VALUE(Priv, '$.access') &        32, 'Y', 'N') AS Drop_priv,
+  IF(JSON_VALUE(Priv, '$.access') &        64, 'Y', 'N') AS Reload_priv,
+  IF(JSON_VALUE(Priv, '$.access') &       128, 'Y', 'N') AS Shutdown_priv,
+  IF(JSON_VALUE(Priv, '$.access') &       256, 'Y', 'N') AS Process_priv,
+  IF(JSON_VALUE(Priv, '$.access') &       512, 'Y', 'N') AS File_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      1024, 'Y', 'N') AS Grant_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      2048, 'Y', 'N') AS References_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      4096, 'Y', 'N') AS Index_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      8192, 'Y', 'N') AS Alter_priv,
+  IF(JSON_VALUE(Priv, '$.access') &     16384, 'Y', 'N') AS Show_db_priv,
+  IF(JSON_VALUE(Priv, '$.access') &     32768, 'Y', 'N') AS Super_priv,
+  IF(JSON_VALUE(Priv, '$.access') &     65536, 'Y', 'N') AS Create_tmp_table_priv,
+  IF(JSON_VALUE(Priv, '$.access') &    131072, 'Y', 'N') AS Lock_tables_priv,
+  IF(JSON_VALUE(Priv, '$.access') &    262144, 'Y', 'N') AS Execute_priv,
+  IF(JSON_VALUE(Priv, '$.access') &    524288, 'Y', 'N') AS Repl_slave_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   1048576, 'Y', 'N') AS Repl_client_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   2097152, 'Y', 'N') AS Create_view_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   4194304, 'Y', 'N') AS Show_view_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   8388608, 'Y', 'N') AS Create_routine_priv,
+  IF(JSON_VALUE(Priv, '$.access') &  16777216, 'Y', 'N') AS Alter_routine_priv,
+  IF(JSON_VALUE(Priv, '$.access') &  33554432, 'Y', 'N') AS Create_user_priv,
+  IF(JSON_VALUE(Priv, '$.access') &  67108864, 'Y', 'N') AS Event_priv,
+  IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv,
+  IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv,
+  IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv,
+  ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type,
+  IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher,
+  IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer,
+  IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections,
+  IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin,
+  IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string,
+  'N' AS password_expired,
+  ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role,
+  IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time
+  FROM global_priv;
+
+
+SET sql_mode='';
+
+DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn;
+DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn;
+
+delimiter |;
+
+CREATE DEFINER=`superuser`@`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=`superuser`@`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 ;|
+
+set @@sql_mode= @save_sql_mode;
+
+drop user 'mariadb.sys'@'localhost';
+
+--echo # check changed definitions mysql_upgrade
+
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'superuser@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 = 'superuser@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+
+SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--echo # Run mysql_upgrade
+--exec $MYSQL_UPGRADE  2>&1
+--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+--echo # check new definitions mysql_upgrade
+
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'superuser@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 = 'superuser@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
+
+select count(*) from global_priv where user='mariadb.sys' and host='localhost';
+--echo # above should be 0 (no mariadb.sys@localhost user)
+
+--echo # restore environment
+
+DROP USER 'superuser'@'localhost';
+DROP VIEW mysql.user;
+DROP PROCEDURE AddGeometryColumn;
+DROP PROCEDURE DropGeometryColumn;
+--exec $MYSQL_UPGRADE  2>&1
+--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+delete from global_priv;
+delete from tables_priv;
+delete from proxies_priv;
+delete from proc;
+insert into mysql.global_priv select * from mysqltest1.save_global_priv;
+insert into mysql.tables_priv select * from mysqltest1.save_tables_priv;
+insert into mysql.proxies_priv select * from mysqltest1.save_proxies_priv;
+rename table proc to bad_proc;
+rename table save_proc to proc;
+drop table bad_proc;
+flush privileges;
+
+disconnect default;
+connect  default,localhost,root,,;
+connection default;
+
+drop database mysqltest1;
+
+--echo # End of 10.4 tests (but better do not add other tests here)
diff --git a/mysql-test/main/upgrade_MDEV-23102-2.result b/mysql-test/main/upgrade_MDEV-23102-2.result
new file mode 100644
index 00000000000..4702fae812f
--- /dev/null
+++ b/mysql-test/main/upgrade_MDEV-23102-2.result
@@ -0,0 +1,262 @@
+#
+# MDEV-23102: 10.4 create mariadb.sys user on each update even
+# is the user is not needed
+#
+# part 2: reassigning mysql.user and deleting gis procedures
+call mtr.add_suppression("Cannot load from mysql.proc. The table is probably corrupted");
+create database mysqltest1;
+use mysqltest1;
+create table save_global_priv as select * from mysql.global_priv;
+create table save_tables_priv as select * from mysql.tables_priv;
+create table save_proxies_priv as select * from mysql.proxies_priv;
+create table mysql.save_proc like mysql.proc;
+insert into mysql.save_proc select * from mysql.proc;
+set @save_sql_mode= @@sql_mode;
+use mysql;
+# make changed definition of gis procedures and user view
+create user superuser@localhost;
+grant all privileges on mysql.* to superuser@localhost;
+drop view user;
+CREATE DEFINER='superuser'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT
+Host,
+User,
+IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password,
+IF(JSON_VALUE(Priv, '$.access') &         1, 'Y', 'N') AS Select_priv,
+IF(JSON_VALUE(Priv, '$.access') &         2, 'Y', 'N') AS Insert_priv,
+IF(JSON_VALUE(Priv, '$.access') &         4, 'Y', 'N') AS Update_priv,
+IF(JSON_VALUE(Priv, '$.access') &         8, 'Y', 'N') AS Delete_priv,
+IF(JSON_VALUE(Priv, '$.access') &        16, 'Y', 'N') AS Create_priv,
+IF(JSON_VALUE(Priv, '$.access') &        32, 'Y', 'N') AS Drop_priv,
+IF(JSON_VALUE(Priv, '$.access') &        64, 'Y', 'N') AS Reload_priv,
+IF(JSON_VALUE(Priv, '$.access') &       128, 'Y', 'N') AS Shutdown_priv,
+IF(JSON_VALUE(Priv, '$.access') &       256, 'Y', 'N') AS Process_priv,
+IF(JSON_VALUE(Priv, '$.access') &       512, 'Y', 'N') AS File_priv,
+IF(JSON_VALUE(Priv, '$.access') &      1024, 'Y', 'N') AS Grant_priv,
+IF(JSON_VALUE(Priv, '$.access') &      2048, 'Y', 'N') AS References_priv,
+IF(JSON_VALUE(Priv, '$.access') &      4096, 'Y', 'N') AS Index_priv,
+IF(JSON_VALUE(Priv, '$.access') &      8192, 'Y', 'N') AS Alter_priv,
+IF(JSON_VALUE(Priv, '$.access') &     16384, 'Y', 'N') AS Show_db_priv,
+IF(JSON_VALUE(Priv, '$.access') &     32768, 'Y', 'N') AS Super_priv,
+IF(JSON_VALUE(Priv, '$.access') &     65536, 'Y', 'N') AS Create_tmp_table_priv,
+IF(JSON_VALUE(Priv, '$.access') &    131072, 'Y', 'N') AS Lock_tables_priv,
+IF(JSON_VALUE(Priv, '$.access') &    262144, 'Y', 'N') AS Execute_priv,
+IF(JSON_VALUE(Priv, '$.access') &    524288, 'Y', 'N') AS Repl_slave_priv,
+IF(JSON_VALUE(Priv, '$.access') &   1048576, 'Y', 'N') AS Repl_client_priv,
+IF(JSON_VALUE(Priv, '$.access') &   2097152, 'Y', 'N') AS Create_view_priv,
+IF(JSON_VALUE(Priv, '$.access') &   4194304, 'Y', 'N') AS Show_view_priv,
+IF(JSON_VALUE(Priv, '$.access') &   8388608, 'Y', 'N') AS Create_routine_priv,
+IF(JSON_VALUE(Priv, '$.access') &  16777216, 'Y', 'N') AS Alter_routine_priv,
+IF(JSON_VALUE(Priv, '$.access') &  33554432, 'Y', 'N') AS Create_user_priv,
+IF(JSON_VALUE(Priv, '$.access') &  67108864, 'Y', 'N') AS Event_priv,
+IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv,
+IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv,
+IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv,
+ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type,
+IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher,
+IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer,
+IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections,
+IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin,
+IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string,
+'N' AS password_expired,
+ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role,
+IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role,
+CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time
+FROM global_priv;
+DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn;
+DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn;
+drop user 'mariadb.sys'@'localhost';
+# check changed definitions mysql_upgrade
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+count(*)
+1
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn';
+count(*)
+0
+SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
+SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
+# 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
+mysqltest1
+mysqltest1.save_global_priv                        OK
+mysqltest1.save_proxies_priv                       OK
+mysqltest1.save_tables_priv                        OK
+performance_schema
+test
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+# check new definitions mysql_upgrade
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+count(*)
+1
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn';
+count(*)
+0
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn';
+count(*)
+0
+select count(*) from global_priv where user='mariadb.sys' and host='localhost';
+count(*)
+0
+# above should be 0 (no mariadb.sys@localhost user)
+# restore environment
+DROP USER 'superuser'@'localhost';
+DROP VIEW mysql.user;
+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
+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
+mysqltest1
+mysqltest1.save_global_priv                        OK
+mysqltest1.save_proxies_priv                       OK
+mysqltest1.save_tables_priv                        OK
+performance_schema
+test
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+delete from global_priv;
+delete from tables_priv;
+delete from proxies_priv;
+delete from proc;
+insert into mysql.global_priv select * from mysqltest1.save_global_priv;
+insert into mysql.tables_priv select * from mysqltest1.save_tables_priv;
+insert into mysql.proxies_priv select * from mysqltest1.save_proxies_priv;
+rename table proc to bad_proc;
+rename table save_proc to proc;
+drop table bad_proc;
+flush privileges;
+disconnect default;
+connect  default,localhost,root,,;
+connection default;
+drop database mysqltest1;
+# End of 10.4 tests (but better do not add other tests here)
diff --git a/mysql-test/main/upgrade_MDEV-23102-2.test b/mysql-test/main/upgrade_MDEV-23102-2.test
new file mode 100644
index 00000000000..5ce7682fea6
--- /dev/null
+++ b/mysql-test/main/upgrade_MDEV-23102-2.test
@@ -0,0 +1,141 @@
+--echo #
+--echo # MDEV-23102: 10.4 create mariadb.sys user on each update even
+--echo # is the user is not needed
+--echo #
+--echo # part 2: reassigning mysql.user and deleting gis procedures
+
+--source include/mysql_upgrade_preparation.inc
+
+call mtr.add_suppression("Cannot load from mysql.proc. The table is probably corrupted");
+create database mysqltest1;
+use mysqltest1;
+create table save_global_priv as select * from mysql.global_priv;
+create table save_tables_priv as select * from mysql.tables_priv;
+create table save_proxies_priv as select * from mysql.proxies_priv;
+create table mysql.save_proc like mysql.proc;
+insert into mysql.save_proc select * from mysql.proc;
+set @save_sql_mode= @@sql_mode;
+
+use mysql;
+
+--echo # make changed definition of gis procedures and user view
+
+create user superuser@localhost;
+grant all privileges on mysql.* to superuser@localhost;
+
+drop view user;
+
+CREATE DEFINER='superuser'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT
+  Host,
+  User,
+  IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password,
+  IF(JSON_VALUE(Priv, '$.access') &         1, 'Y', 'N') AS Select_priv,
+  IF(JSON_VALUE(Priv, '$.access') &         2, 'Y', 'N') AS Insert_priv,
+  IF(JSON_VALUE(Priv, '$.access') &         4, 'Y', 'N') AS Update_priv,
+  IF(JSON_VALUE(Priv, '$.access') &         8, 'Y', 'N') AS Delete_priv,
+  IF(JSON_VALUE(Priv, '$.access') &        16, 'Y', 'N') AS Create_priv,
+  IF(JSON_VALUE(Priv, '$.access') &        32, 'Y', 'N') AS Drop_priv,
+  IF(JSON_VALUE(Priv, '$.access') &        64, 'Y', 'N') AS Reload_priv,
+  IF(JSON_VALUE(Priv, '$.access') &       128, 'Y', 'N') AS Shutdown_priv,
+  IF(JSON_VALUE(Priv, '$.access') &       256, 'Y', 'N') AS Process_priv,
+  IF(JSON_VALUE(Priv, '$.access') &       512, 'Y', 'N') AS File_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      1024, 'Y', 'N') AS Grant_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      2048, 'Y', 'N') AS References_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      4096, 'Y', 'N') AS Index_priv,
+  IF(JSON_VALUE(Priv, '$.access') &      8192, 'Y', 'N') AS Alter_priv,
+  IF(JSON_VALUE(Priv, '$.access') &     16384, 'Y', 'N') AS Show_db_priv,
+  IF(JSON_VALUE(Priv, '$.access') &     32768, 'Y', 'N') AS Super_priv,
+  IF(JSON_VALUE(Priv, '$.access') &     65536, 'Y', 'N') AS Create_tmp_table_priv,
+  IF(JSON_VALUE(Priv, '$.access') &    131072, 'Y', 'N') AS Lock_tables_priv,
+  IF(JSON_VALUE(Priv, '$.access') &    262144, 'Y', 'N') AS Execute_priv,
+  IF(JSON_VALUE(Priv, '$.access') &    524288, 'Y', 'N') AS Repl_slave_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   1048576, 'Y', 'N') AS Repl_client_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   2097152, 'Y', 'N') AS Create_view_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   4194304, 'Y', 'N') AS Show_view_priv,
+  IF(JSON_VALUE(Priv, '$.access') &   8388608, 'Y', 'N') AS Create_routine_priv,
+  IF(JSON_VALUE(Priv, '$.access') &  16777216, 'Y', 'N') AS Alter_routine_priv,
+  IF(JSON_VALUE(Priv, '$.access') &  33554432, 'Y', 'N') AS Create_user_priv,
+  IF(JSON_VALUE(Priv, '$.access') &  67108864, 'Y', 'N') AS Event_priv,
+  IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv,
+  IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv,
+  IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv,
+  ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type,
+  IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher,
+  IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer,
+  IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections,
+  IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin,
+  IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string,
+  'N' AS password_expired,
+  ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role,
+  IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role,
+  CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time
+  FROM global_priv;
+
+
+DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn;
+DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn;
+
+
+drop user 'mariadb.sys'@'localhost';
+
+--echo # check changed definitions mysql_upgrade
+
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn';
+
+SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--echo # Run mysql_upgrade
+--exec $MYSQL_UPGRADE  2>&1
+--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+--echo # check new definitions mysql_upgrade
+
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def'
+and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'superuser@localhost';
+SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn';
+SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn';
+
+
+select count(*) from global_priv where user='mariadb.sys' and host='localhost';
+--echo # above should be 0 (no mariadb.sys@localhost user)
+
+--echo # restore environment
+
+DROP USER 'superuser'@'localhost';
+DROP VIEW mysql.user;
+--exec $MYSQL_UPGRADE  2>&1
+--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+delete from global_priv;
+delete from tables_priv;
+delete from proxies_priv;
+delete from proc;
+insert into mysql.global_priv select * from mysqltest1.save_global_priv;
+insert into mysql.tables_priv select * from mysqltest1.save_tables_priv;
+insert into mysql.proxies_priv select * from mysqltest1.save_proxies_priv;
+rename table proc to bad_proc;
+rename table save_proc to proc;
+drop table bad_proc;
+flush privileges;
+
+disconnect default;
+connect  default,localhost,root,,;
+connection default;
+
+drop database mysqltest1;
+
+--echo # End of 10.4 tests (but better do not add other tests here)
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
index 6e1377660e9..f81aa290100 100644
--- a/scripts/mysql_system_tables.sql
+++ b/scripts/mysql_system_tables.sql
@@ -37,9 +37,15 @@ CREATE TABLE IF NOT EXISTS global_priv (Host char(60) binary DEFAULT '', User ch
 
 set @had_sys_user= 0 <> (select count(*) from mysql.global_priv where Host="localhost" and User="mariadb.sys");
 
+set @exists_user_view= EXISTS (SELECT * FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user');
+
+set @exists_user_view_by_root= EXISTS (SELECT * FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost');
+
+set @need_sys_user_creation= (NOT @had_sys_user) AND (( NOT @exists_user_view) OR @exists_user_view_by_root);
+
 CREATE TEMPORARY TABLE tmp_user_sys LIKE global_priv;
 INSERT INTO tmp_user_sys (Host,User,Priv) VALUES ('localhost','mariadb.sys','{"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}');
-INSERT INTO global_priv SELECT * FROM tmp_user_sys WHERE NOT @had_sys_user;
+INSERT INTO global_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_user_creation;
 DROP TABLE tmp_user_sys;
 
 
@@ -111,7 +117,7 @@ CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NUL
 
 CREATE TEMPORARY TABLE tmp_user_sys LIKE tables_priv;
 INSERT INTO tmp_user_sys (Host,Db,User,Table_name,Grantor,Timestamp,Table_priv) VALUES ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0','Select,Update,Delete');
-INSERT INTO tables_priv SELECT * FROM tmp_user_sys WHERE NOT @had_sys_user;
+INSERT INTO tables_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_user_creation;
 DROP TABLE tmp_user_sys;
 
 CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin   comment='Column privileges';