Debian Codegraph @

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

raw
1 # Tests for PERFORMANCE_SCHEMA
2
3 --source include/not_embedded.inc
4 --source include/have_perfschema.inc
5
6 show grants;
7
8 create user 'pfs_user_1'@localhost;
9 create user 'pfs_user_2'@localhost;
10 create user 'pfs_user_3'@localhost;
11 grant SELECT,INSERT,UPDATE,DELETE,DROP,CREATE on test.* to 'pfs_user_1'@localhost;
12 grant SELECT,INSERT,UPDATE,DELETE,DROP,CREATE on test.* to 'pfs_user_2'@localhost;
13 grant SELECT,INSERT,UPDATE,DELETE,DROP,CREATE on test.* to 'pfs_user_3'@localhost;
14 grant ALL on *.* to 'pfs_user_1'@localhost with GRANT OPTION;
15
16 # Test denied privileges on performance_schema.*
17
18 --error ER_DBACCESS_DENIED_ERROR
19 grant ALL on performance_schema.* to 'pfs_user_2'@localhost
20 with GRANT OPTION;
21
22 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
23 grant CREATE on performance_schema.* to 'pfs_user_2'@localhost;
24
25 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
26 grant DROP on performance_schema.* to 'pfs_user_2'@localhost;
27
28 --error ER_DBACCESS_DENIED_ERROR
29 grant REFERENCES on performance_schema.* to 'pfs_user_2'@localhost;
30
31 --error ER_DBACCESS_DENIED_ERROR
32 grant INDEX on performance_schema.* to 'pfs_user_2'@localhost;
33
34 --error ER_DBACCESS_DENIED_ERROR
35 grant ALTER on performance_schema.* to 'pfs_user_2'@localhost;
36
37 --error ER_DBACCESS_DENIED_ERROR
38 grant CREATE TEMPORARY TABLES on performance_schema.* to 'pfs_user_2'@localhost;
39
40 --error ER_DBACCESS_DENIED_ERROR
41 grant EXECUTE on performance_schema.* to 'pfs_user_2'@localhost;
42
43 --error ER_DBACCESS_DENIED_ERROR
44 grant CREATE VIEW on performance_schema.* to 'pfs_user_2'@localhost;
45
46 --error ER_DBACCESS_DENIED_ERROR
47 grant SHOW VIEW on performance_schema.* to 'pfs_user_2'@localhost;
48
49 --error ER_DBACCESS_DENIED_ERROR
50 grant CREATE ROUTINE on performance_schema.* to 'pfs_user_2'@localhost;
51
52 --error ER_DBACCESS_DENIED_ERROR
53 grant ALTER ROUTINE on performance_schema.* to 'pfs_user_2'@localhost;
54
55 --error ER_DBACCESS_DENIED_ERROR
56 grant EVENT on performance_schema.* to 'pfs_user_2'@localhost;
57
58 --error ER_DBACCESS_DENIED_ERROR
59 grant TRIGGER on performance_schema.* to 'pfs_user_2'@localhost;
60
61 # Test allowed privileges on performance_schema.*
62
63 grant SELECT on performance_schema.* to 'pfs_user_2'@localhost;
64 grant INSERT on performance_schema.* to 'pfs_user_2'@localhost;
65 grant UPDATE on performance_schema.* to 'pfs_user_2'@localhost;
66 grant DELETE on performance_schema.* to 'pfs_user_2'@localhost;
67 grant LOCK TABLES on performance_schema.* to 'pfs_user_2'@localhost;
68
69 # Test denied privileges on specific performance_schema tables.
70 # setup_instrument : example of PFS_updatable_acl
71 # events_waits_current : example of PFS_truncatable_acl
72 # file_instances : example of PFS_readonly_acl
73
74 --error ER_DBACCESS_DENIED_ERROR
75 grant ALL on performance_schema.setup_instruments to 'pfs_user_3'@localhost
76 with GRANT OPTION;
77
78 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
79 grant CREATE on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
80
81 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
82 grant DROP on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
83
84 --error ER_DBACCESS_DENIED_ERROR
85 grant REFERENCES on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
86
87 --error ER_DBACCESS_DENIED_ERROR
88 grant INDEX on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
89
90 --error ER_DBACCESS_DENIED_ERROR
91 grant ALTER on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
92
93 --error ER_DBACCESS_DENIED_ERROR
94 grant CREATE VIEW on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
95
96 --error ER_DBACCESS_DENIED_ERROR
97 grant SHOW VIEW on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
98
99 --error ER_DBACCESS_DENIED_ERROR
100 grant TRIGGER on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
101
102 --error ER_TABLEACCESS_DENIED_ERROR
103 grant INSERT on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
104
105 --error ER_TABLEACCESS_DENIED_ERROR
106 grant DELETE on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
107
108 grant SELECT on performance_schema.setup_instruments to 'pfs_user_3'@localhost
109 with GRANT OPTION;
110
111 grant UPDATE on performance_schema.setup_instruments to 'pfs_user_3'@localhost
112 with GRANT OPTION;
113
114 --error ER_DBACCESS_DENIED_ERROR
115 grant ALL on performance_schema.events_waits_current to 'pfs_user_3'@localhost
116 with GRANT OPTION;
117
118 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
119 grant CREATE on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
120
121 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
122 grant DROP on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
123
124 --error ER_DBACCESS_DENIED_ERROR
125 grant REFERENCES on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
126
127 --error ER_DBACCESS_DENIED_ERROR
128 grant INDEX on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
129
130 --error ER_DBACCESS_DENIED_ERROR
131 grant ALTER on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
132
133 --error ER_DBACCESS_DENIED_ERROR
134 grant CREATE VIEW on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
135
136 --error ER_DBACCESS_DENIED_ERROR
137 grant SHOW VIEW on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
138
139 --error ER_DBACCESS_DENIED_ERROR
140 grant TRIGGER on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
141
142 --error ER_TABLEACCESS_DENIED_ERROR
143 grant INSERT on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
144
145 --error ER_TABLEACCESS_DENIED_ERROR
146 grant UPDATE on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
147
148 --error ER_TABLEACCESS_DENIED_ERROR
149 grant DELETE on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
150
151 grant SELECT on performance_schema.events_waits_current to 'pfs_user_3'@localhost
152 with GRANT OPTION;
153
154 --error ER_DBACCESS_DENIED_ERROR
155 grant ALL on performance_schema.file_instances to 'pfs_user_3'@localhost
156 with GRANT OPTION;
157
158 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
159 grant CREATE on performance_schema.file_instances to 'pfs_user_3'@localhost;
160
161 # will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
162 grant DROP on performance_schema.file_instances to 'pfs_user_3'@localhost;
163
164 --error ER_DBACCESS_DENIED_ERROR
165 grant REFERENCES on performance_schema.file_instances to 'pfs_user_3'@localhost;
166
167 --error ER_DBACCESS_DENIED_ERROR
168 grant INDEX on performance_schema.file_instances to 'pfs_user_3'@localhost;
169
170 --error ER_DBACCESS_DENIED_ERROR
171 grant ALTER on performance_schema.file_instances to 'pfs_user_3'@localhost;
172
173 --error ER_DBACCESS_DENIED_ERROR
174 grant CREATE VIEW on performance_schema.file_instances to 'pfs_user_3'@localhost;
175
176 --error ER_DBACCESS_DENIED_ERROR
177 grant SHOW VIEW on performance_schema.file_instances to 'pfs_user_3'@localhost;
178
179 --error ER_DBACCESS_DENIED_ERROR
180 grant TRIGGER on performance_schema.file_instances to 'pfs_user_3'@localhost;
181
182 --error ER_TABLEACCESS_DENIED_ERROR
183 grant INSERT on performance_schema.file_instances to 'pfs_user_3'@localhost;
184
185 --error ER_TABLEACCESS_DENIED_ERROR
186 grant UPDATE on performance_schema.file_instances to 'pfs_user_3'@localhost;
187
188 --error ER_TABLEACCESS_DENIED_ERROR
189 grant DELETE on performance_schema.file_instances to 'pfs_user_3'@localhost;
190
191 grant SELECT on performance_schema.file_instances to 'pfs_user_3'@localhost
192 with GRANT OPTION;
193
194 # See bug#45354 LOCK TABLES is not a TABLE privilege
195 grant LOCK TABLES on performance_schema.* to 'pfs_user_3'@localhost
196 with GRANT OPTION;
197
198 flush privileges;
199
200 --source ../include/privilege.inc
201
202 connect (con1, localhost, pfs_user_1, , );
203
204 --source ../include/privilege.inc
205
206 --disconnect con1
207
208 connect (con2, localhost, pfs_user_2, , );
209
210 --source ../include/privilege.inc
211
212 --disconnect con2
213
214 connect (con3, localhost, pfs_user_3, , );
215
216 --source ../include/privilege.inc
217
218 --disconnect con3
219
220 --connection default
221
222 revoke all privileges, grant option from 'pfs_user_1'@localhost;
223 revoke all privileges, grant option from 'pfs_user_2'@localhost;
224 revoke all privileges, grant option from 'pfs_user_3'@localhost;
225 drop user 'pfs_user_1'@localhost;
226 drop user 'pfs_user_2'@localhost;
227 drop user 'pfs_user_3'@localhost;
228 flush privileges;
229
230 --echo # Test cases from WL#4818
231 --echo # Setup user
232
233 CREATE user pfs_user_4;
234 --connect (pfs_user_4, localhost, pfs_user_4, ,"*NO-ONE*")
235
236 --echo #
237 --echo # WL#4818, NFS4: Normal user does not have access to view data
238 --echo # without grants
239 --echo #
240
241 --connection pfs_user_4
242 --echo # Select as pfs_user_4 should fail without grant
243
244 --error ER_TABLEACCESS_DENIED_ERROR
245 SELECT event_id FROM performance_schema.events_waits_history;
246
247 --error ER_TABLEACCESS_DENIED_ERROR
248 SELECT event_id FROM performance_schema.events_waits_history_long;
249
250 --error ER_TABLEACCESS_DENIED_ERROR
251 SELECT event_id FROM performance_schema.events_waits_current;
252
253 --error ER_TABLEACCESS_DENIED_ERROR
254 SELECT event_name FROM performance_schema.events_waits_summary_by_instance;
255
256 --error ER_TABLEACCESS_DENIED_ERROR
257 SELECT event_name FROM performance_schema.file_summary_by_instance;
258
259 --echo #
260 --echo # WL#4818, NFS3: Normal user does not have access to change what is
261 --echo # instrumented without grants
262 --echo #
263
264 --connection pfs_user_4
265 --echo # User pfs_user_4 should not be allowed to tweak instrumentation without
266 --echo # explicit grant
267
268 --error ER_TABLEACCESS_DENIED_ERROR
269 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
270
271 --error ER_TABLEACCESS_DENIED_ERROR
272 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
273 WHERE name LIKE 'wait/synch/mutex/%'
274 OR name LIKE 'wait/synch/rwlock/%';
275
276 --error ER_TABLEACCESS_DENIED_ERROR
277 UPDATE performance_schema.setup_consumers SET enabled = 'YES';
278
279 --error ER_TABLEACCESS_DENIED_ERROR
280 UPDATE performance_schema.setup_timers SET timer_name = 'TICK';
281
282 --error ER_TABLEACCESS_DENIED_ERROR
283 TRUNCATE TABLE performance_schema.events_waits_history_long;
284
285 --error ER_TABLEACCESS_DENIED_ERROR
286 TRUNCATE TABLE performance_schema.events_waits_history;
287
288 --error ER_TABLEACCESS_DENIED_ERROR
289 TRUNCATE TABLE performance_schema.events_waits_current;
290
291 --echo #
292 --echo # WL#4814, NFS1: Can use grants to give normal user access
293 --echo # to turn on and off instrumentation
294 --echo #
295
296 --connection default
297 --echo # Grant access to change tables with the root account
298
299 GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4;
300 GRANT UPDATE, SELECT ON performance_schema.setup_timers TO pfs_user_4;
301 GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4;
302 GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4;
303 GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4;
304 GRANT DROP ON performance_schema.events_waits_history_long TO pfs_user_4;
305
306 --connection pfs_user_4
307 --echo # User pfs_user_4 should now be allowed to tweak instrumentation
308
309 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
310
311 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
312 WHERE name LIKE 'wait/synch/mutex/%'
313 OR name LIKE 'wait/synch/rwlock/%';
314
315 UPDATE performance_schema.setup_consumers SET enabled = 'YES';
316
317 # We do not touch "wait", to avoid restoring it at the end of the test,
318 # as its default value initialized at server startup is ambiguous:
319 # it can be CYCLE or NANOSECOND depending on platform
320
321 UPDATE performance_schema.setup_timers SET timer_name = 'TICK' WHERE name <> "wait";
322
323 TRUNCATE TABLE performance_schema.events_waits_history_long;
324 TRUNCATE TABLE performance_schema.events_waits_history;
325 TRUNCATE TABLE performance_schema.events_waits_current;
326
327 --echo # Clean up
328 --disconnect pfs_user_4
329 --source include/wait_until_disconnected.inc
330 --connection default
331 REVOKE ALL PRIVILEGES, GRANT OPTION FROM pfs_user_4;
332 DROP USER pfs_user_4;
333 flush privileges;
334 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
335 UPDATE performance_schema.setup_consumers SET enabled = 'YES';
336
337 # Restore the default values for the timers that we changed.
338 # Note, we did not touch "wait", see above.
339 UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name="idle";
340 UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage";
341 UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement";
342
343 --echo #
344 --echo # WL#2284: Increase the length of a user name
345 --echo #
346
347 CREATE USER 'user_name_len_22_01234'@localhost;
348
349 --error ER_DBACCESS_DENIED_ERROR
350 GRANT ALL ON performance_schema.* TO 'user_name_len_22_01234'@localhost with GRANT OPTION;
351
352 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user_name_len_22_01234'@localhost;
353 DROP USER 'user_name_len_22_01234'@localhost;