Debian Codegraph @

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

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