1# Tests for PERFORMANCE_SCHEMA
2#
3# Check that
4# - a regular user can not update setup_ tables under --read-only
5# - a user with SUPER privileges can
6
7--source include/not_embedded.inc
8--source include/have_perfschema.inc
9--enable_connect_log
10
11use performance_schema;
12
13set @start_read_only= @@global.read_only;
14set @start_super_read_only= @@global.super_read_only;
15
16set @orig_sql_mode= @@sql_mode;
17set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
18grant SELECT, UPDATE on performance_schema.* to pfsuser@localhost;
19set sql_mode= @orig_sql_mode;
20flush privileges;
21
22connect (con1, localhost, pfsuser, , test);
23
24connection default;
25
26set global read_only=0;
27
28connection con1;
29
30select @@global.read_only;
31show grants;
32--disable_result_log
33select * from performance_schema.setup_instruments;
34update performance_schema.setup_instruments set enabled='NO';
35update performance_schema.setup_instruments set enabled='YES';
36--enable_result_log
37
38connection default;
39
40set global read_only=1;
41
42connection con1;
43
44select @@global.read_only;
45show grants;
46--disable_result_log
47--echo # Update on perf_schema is allowed in read_only mode.
48select * from performance_schema.setup_instruments;
49update performance_schema.setup_instruments set enabled='NO';
50update performance_schema.setup_instruments set enabled='YES';
51--enable_result_log
52
53connection default;
54
55grant super on *.* to pfsuser@localhost;
56flush privileges;
57
58disconnect con1;
59connect (con1, localhost, pfsuser, , test);
60
61select @@global.read_only;
62show grants;
63--disable_result_log
64select * from performance_schema.setup_instruments;
65update performance_schema.setup_instruments set enabled='NO';
66update performance_schema.setup_instruments set enabled='YES';
67--enable_result_log
68
69connection default;
70
71set global super_read_only=1;
72
73connection con1;
74
75select @@global.super_read_only;
76show grants;
77--disable_result_log
78select * from performance_schema.setup_instruments;
79--echo # Update is allowed in super_read_only on perf schema for
80--echo # super user.
81update performance_schema.setup_instruments set enabled='NO';
82update performance_schema.setup_instruments set enabled='YES';
83--enable_result_log
84
85--echo #
86--echo # Bug#31080309 - REGRESSION FOR BUG 81009 FIXED IN 5.7.17
87--echo #   Bug23103937(BUG81009) - PS_TRUNCATE_ALL_TABLES() DOES NOT WORK IN
88--echo #                           SUPER_READ_ONLY MODE.
89--echo #
90
91connection default;
92--echo # Reset read_only and super_read_only mode.
93set global read_only=0;
94set global super_read_only=0;
95--echo # Grant truncate table and execute stored procedure privileges to pfsuser.
96grant DROP on performance_schema.* to pfsuser@localhost;
97grant EXECUTE on procedure sys.ps_truncate_all_tables to pfsuser@localhost;
98
99--echo #-----------------------------------------------------------------------
100--echo # Test case to verify truncate operation on performance schema table by
101--echo # super user with super_read_only=ON.
102--echo #-----------------------------------------------------------------------
103--echo # Set super_read_only mode.
104set global super_read_only=1;
105
106connection con1;
107--echo # truncate operation is allowed in super_read_only mode on performance schema
108--echo # tables for super user.
109--echo # Without fix following statements fail because of super read only mode.
110--disable_result_log
111truncate table performance_schema.events_statements_summary_by_digest;
112call sys.ps_truncate_all_tables(0);
113--enable_result_log
114
115--echo #-----------------------------------------------------------------------
116--echo # Test case to verify truncate operation on performance schema table by
117--echo # super user with read_only=ON.
118--echo #-----------------------------------------------------------------------
119connection default;
120--echo # Reset super_read_only mode.
121set global super_read_only=0;
122--echo # Set read_only mode.
123set global read_only=1;
124
125connection con1;
126--echo # truncate operation is allowed in read_only mode on performance schema
127--echo # tables for super user.
128--echo # Without fix following statements fail because of super read only mode.
129--disable_result_log
130truncate table performance_schema.events_statements_summary_by_digest;
131call sys.ps_truncate_all_tables(0);
132--enable_result_log
133
134--echo #-----------------------------------------------------------------------
135--echo # Test case to verify truncate operation on performance schema table by
136--echo # non-super user with super_read_only=ON.
137--echo #-----------------------------------------------------------------------
138connection default;
139set global super_read_only=0;
140--echo # Revoke SUPER privilege from pfsuser.
141revoke SUPER on *.* from pfsuser@localhost;
142--echo # Reset super_read_only mode.
143set global super_read_only=1;
144
145connection con1;
146--echo # truncate operation is allowed in super_read_only mode on performance schema
147--echo # tables for non-super user.
148--disable_result_log
149truncate table performance_schema.events_statements_summary_by_digest;
150call sys.ps_truncate_all_tables(0);
151--enable_result_log
152
153--echo #-----------------------------------------------------------------------
154--echo # Test case to verify truncate operation on performance schema table by
155--echo # non-super user with read_only=ON.
156--echo #-----------------------------------------------------------------------
157connection default;
158--echo # Reset super_read_only mode.
159set global super_read_only=0;
160--echo # Set read_only mode.
161set global read_only=1;
162
163connection con1;
164--echo # truncate operation is allowed in read_only mode on performance schema
165--echo # tables for non-super user.
166--disable_result_log
167truncate table performance_schema.events_statements_summary_by_digest;
168call sys.ps_truncate_all_tables(0);
169--enable_result_log
170--echo ########################################################################
171
172disconnect con1;
173--source include/wait_until_disconnected.inc
174
175connection default;
176
177set global read_only= @start_read_only;
178set global super_read_only= @start_super_read_only;
179
180drop user pfsuser@localhost;
181flush privileges;
182
183--disable_connect_log
184