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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
# Tests for PERFORMANCE_SCHEMA
#
# Check that
# - a regular user can not update setup_ tables under --read-only
# - a user with SUPER privileges can
--enable_connect_log
use performance_schema;
set @start_read_only= @@global.read_only;
set @start_super_read_only= @@global.super_read_only;
create user pfsuser@localhost;
grant SELECT, UPDATE on performance_schema.* to pfsuser@localhost;
flush privileges;
connect (con1, localhost, pfsuser, , test);
connection default;
set global read_only=0;
connection con1;
select @@global.read_only;
show grants;
--disable_result_log
select * from performance_schema.setup_instruments;
update performance_schema.setup_instruments set enabled='NO';
update performance_schema.setup_instruments set enabled='YES';
--enable_result_log
connection default;
set global read_only=1;
connection con1;
select @@global.read_only;
show grants;
--disable_result_log
--echo # Update on perf_schema is allowed in read_only mode.
select * from performance_schema.setup_instruments;
update performance_schema.setup_instruments set enabled='NO';
update performance_schema.setup_instruments set enabled='YES';
--enable_result_log
connection default;
grant super on *.* to pfsuser@localhost;
flush privileges;
disconnect con1;
connect (con1, localhost, pfsuser, , test);
select @@global.read_only;
show grants;
--disable_result_log
select * from performance_schema.setup_instruments;
update performance_schema.setup_instruments set enabled='NO';
update performance_schema.setup_instruments set enabled='YES';
--enable_result_log
connection default;
set global super_read_only=1;
connection con1;
select @@global.super_read_only;
show grants;
--disable_result_log
select * from performance_schema.setup_instruments;
--echo # Update is allowed in super_read_only on perf schema for
--echo # super user.
update performance_schema.setup_instruments set enabled='NO';
update performance_schema.setup_instruments set enabled='YES';
--enable_result_log
--echo #
--echo # Bug#31080309 - REGRESSION FOR BUG 81009 FIXED IN 5.7.17
--echo # Bug23103937(BUG81009) - PS_TRUNCATE_ALL_TABLES() DOES NOT WORK IN
--echo # SUPER_READ_ONLY MODE.
--echo #
connection default;
--echo # Reset read_only and super_read_only mode.
set global read_only=0;
set global super_read_only=0;
--echo # Grant truncate table and execute stored procedure privileges to pfsuser.
grant DROP on performance_schema.* to pfsuser@localhost;
grant EXECUTE on procedure sys.ps_truncate_all_tables to pfsuser@localhost;
--echo #-----------------------------------------------------------------------
--echo # Test case to verify truncate operation on performance schema table by
--echo # super user with super_read_only=ON.
--echo #-----------------------------------------------------------------------
--echo # Set super_read_only mode.
set global super_read_only=1;
connection con1;
--echo # truncate operation is allowed in super_read_only mode on performance schema
--echo # tables for super user.
--echo # Without fix following statements fail because of super read only mode.
--disable_result_log
truncate table performance_schema.events_statements_summary_by_digest;
call sys.ps_truncate_all_tables(0);
--enable_result_log
--echo #-----------------------------------------------------------------------
--echo # Test case to verify truncate operation on performance schema table by
--echo # super user with read_only=ON.
--echo #-----------------------------------------------------------------------
connection default;
--echo # Reset super_read_only mode.
set global super_read_only=0;
--echo # Set read_only mode.
set global read_only=1;
connection con1;
--echo # truncate operation is allowed in read_only mode on performance schema
--echo # tables for super user.
--echo # Without fix following statements fail because of super read only mode.
--disable_result_log
truncate table performance_schema.events_statements_summary_by_digest;
call sys.ps_truncate_all_tables(0);
--enable_result_log
--echo #-----------------------------------------------------------------------
--echo # Test case to verify truncate operation on performance schema table by
--echo # non-super user with super_read_only=ON.
--echo #-----------------------------------------------------------------------
connection default;
set global super_read_only=0;
--echo # Revoke SUPER privilege from pfsuser.
revoke SUPER on *.* from pfsuser@localhost;
--echo # Reset super_read_only mode.
set global super_read_only=1;
connection con1;
--echo # truncate operation is allowed in super_read_only mode on performance schema
--echo # tables for non-super user.
--disable_result_log
truncate table performance_schema.events_statements_summary_by_digest;
call sys.ps_truncate_all_tables(0);
--enable_result_log
--echo #-----------------------------------------------------------------------
--echo # Test case to verify truncate operation on performance schema table by
--echo # non-super user with read_only=ON.
--echo #-----------------------------------------------------------------------
connection default;
--echo # Reset super_read_only mode.
set global super_read_only=0;
--echo # Set read_only mode.
set global read_only=1;
connection con1;
--echo # truncate operation is allowed in read_only mode on performance schema
--echo # tables for non-super user.
--disable_result_log
truncate table performance_schema.events_statements_summary_by_digest;
call sys.ps_truncate_all_tables(0);
--enable_result_log
--echo ########################################################################
disconnect con1;
--source include/wait_until_disconnected.inc
connection default;
set global read_only= @start_read_only;
set global super_read_only= @start_super_read_only;
drop user pfsuser@localhost;
flush privileges;
--disable_connect_log
|
