Debian Codegraph @

mysql-test/suite/perfschema/t/read_only.test

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