From 44b85406b8b46977ef291daee697ef0c399b9560 Mon Sep 17 00:00:00 2001 From: Robert Bindar Date: Fri, 5 Mar 2021 11:11:13 +0200 Subject: MDEV-24363 (followup refactor) avoid listing mysql.user view definition in tests as it wastes lots of time when they fail for minor fixes like adding/changing a new column in the view, also it's less code --- mysql-test/main/upgrade_MDEV-23102-2.test | 52 ++----------------------------- 1 file changed, 2 insertions(+), 50 deletions(-) (limited to 'mysql-test/main/upgrade_MDEV-23102-2.test') diff --git a/mysql-test/main/upgrade_MDEV-23102-2.test b/mysql-test/main/upgrade_MDEV-23102-2.test index 1491f7d7bd2..27ee75ea58c 100644 --- a/mysql-test/main/upgrade_MDEV-23102-2.test +++ b/mysql-test/main/upgrade_MDEV-23102-2.test @@ -23,57 +23,9 @@ use mysql; 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, - 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; +let $viewdef = `select view_definition from information_schema.views where table_name='user' and table_schema='mysql'`; +--eval create or replace definer=superuser@localhost sql security definer view user as $viewdef; DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn; DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn; -- cgit v1.2.1