Oleksandr Byelkin added a comment - 2020-07-06 14:01   -- 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='';   delimiter |;   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 |   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 # 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)

Tingyao Nian added a comment - 2022-06-13 16:44 Hi Oleksandr Byelkin, I agree with Daniel and also have some other questions about this related commit : In scripts/mysql_system_tables.sql line 38 : 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' ); If I understand correctly it translates to: Create mariadb.sys if user does not exist AND: The mysql.user view doesn’t exist OR The mysql.user view exists and the definer is mariadb.sys However this does not cover the case where the the mysql.user view exists but the definer is someone else . As a result, for example, if an older 10.4 release has root as user.view definer, and the root is renamed to something else, then it will hit error when running mariadb-upgrade because the definer of user.view does not exist. A later commit from Monty does not fix this either in my opinion: c4ebb2b I hope I'm not missing anything. If so, my question is why not create mariadb.sys and make it the definer of the sys.user view regardless? I think it's more straightforward and less chance to go wrong, unless there is a good reason not to. Please let me know your thought. Thank you!

Oleksandr Byelkin added a comment - 2022-06-28 10:54 Tingynia we specially do not mess with advanced users configuration to do not break it on each and every update. There are advanced user which removed root and created some other user, they definitely knew what they have done and it is better do not touch such config.

Tingyao Nian added a comment - 2022-07-05 22:19 Hi Oleksandr Byelkin . Thanks for the info. Just to confirm that I understand correctly: So "DON'T create maraidb.sys if mysql.user EXISTS but definer is SOMEONE ELSE" (second scenario in below table) seems to be a deliberate choice to avoid impacting advanced users. But the risk of a not-so-advanced user removing the "root" user, without correcting the definer of "mysql.user" is a side effect and is acceptable? This is very possible if the user is using an older version where mariadb.sys isn't introduced and "root" is the default definer of "mysql.user". And this will certainly re-produce the resolved issue https://jira.mariadb.org/browse/MDEV-19650 Truth table based on the code: mysql.user view exists and the definer is mariadb.sys create mariadb.sys yes yes yes yes no no no yes not possible no no yes