1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
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)
|
