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
|
# ==== Purpose ====
#
# SLAVE MONITOR privilege is required to execute following commands.
# SHOW SLAVE STATUS
# SHOW RELAYLOG EVENTS
#
# ==== Implementation ====
#
# Step1: GRANT ALL privileges for a new user 'user1' and then REVOKE
# SLAVE MONITOR and SUPER privileges.
# Step2: Execute SHOW SLAVE STAUTS/SHOW RELAYLOG EVENTS commands and expect
# ER_SPECIFIC_ACCESS_DENIED_ERROR. This also verifies that REPLICATION
# SLAVE ADMIN privilege is not required for these two commands.
# Step3: GRANT SLAVE MONITOR privilege and observe that both commands are
# allowd to execute.
# Step4: GRANT SUPER privilege and observe that only SHOW SLAVE STATUS command
# is allowed.
#
# ==== References ====
#
# MDEV-23610: Slave user can't run "SHOW SLAVE STATUS" anymore after upgrade
# to 10.5, mysql_upgrade should take of that
# MDEV-23918: admin privlege required to view contents of relay logs in 10.5
#
--source include/not_embedded.inc
CREATE USER user1@localhost IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO user1@localhost;
REVOKE SLAVE MONITOR, SUPER ON *.* FROM user1@localhost;
FLUSH PRIVILEGES;
--connect(con1,localhost,user1,,)
--connection con1
SHOW GRANTS;
--echo #
--echo # Verify that having REPLICATION SLAVE ADMIN doesn't allow SHOW SLAVE STATUS
--echo # Expected error: Access denied; you need (at least one of) the SUPER, SLAVE
--echo # MONITOR privilege(s) for this operation
--echo #
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW SLAVE STATUS;
--echo #
--echo # Verify that having REPLICATION SLAVE ADMIN doesn't allow SHOW RELAYLOG EVENTS
--echo # Expected error: Access denied; you need (at least one of) the REPLICA MONITOR
--echo # privilege(s) for this operation
--echo #
--disable_ps_protocol
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW RELAYLOG EVENTS;
--enable_ps_protocol
--disconnect con1
--echo #
--echo # SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS are allowed with SLAVE MONITOR privilege
--echo #
--connection default
GRANT SLAVE MONITOR ON *.* TO user1@localhost;
FLUSH PRIVILEGES;
--connect(con1,localhost,user1,,)
--connection con1
SHOW GRANTS;
--disable_result_log
SHOW SLAVE STATUS;
--disable_ps_protocol
SHOW RELAYLOG EVENTS;
--enable_ps_protocol
--enable_result_log
--disconnect con1
--connection default
DROP USER user1@localhost;
--echo #
--echo # SHOW SLAVE STATUS command is allowed with SUPER privilege
--echo #
CREATE USER user1@localhost IDENTIFIED BY '';
GRANT SUPER ON *.* TO user1@localhost;
--connect(con1,localhost,user1,,)
--disable_result_log
SHOW SLAVE STATUS;
--enable_result_log
--echo #
--echo # SHOW RELAYLOG EVENTS is not allowed with SUPER privilege, it requires SLAVE MONITOR
--echo #
--disable_ps_protocol
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SHOW RELAYLOG EVENTS;
--enable_ps_protocol
--disconnect con1
--connection default
DROP USER user1@localhost;
--echo #
--echo # MDEV-25030 Upgrade to 10.5.9 breaks root's ability to grant
--echo #
insert mysql.global_priv values ('bar', 'foo7', '{"access":274877906943,"version_id":100507,"plugin":"mysql_native_password","authentication_string":""}'),
('bar', 'foo8', '{"access":274877906943,"version_id":100508,"plugin":"mysql_native_password","authentication_string":""}'),
('bar', 'foo9', '{"access":274877906943,"version_id":100509,"plugin":"mysql_native_password","authentication_string":""}');
flush privileges;
show grants for foo7@bar;
show grants for foo8@bar;
show grants for foo9@bar;
drop user foo7@bar, foo8@bar, foo9@bar;
--echo #
--echo # End of 10.5 tests
--echo #
|
