1
# Copyright (C) 2009 Sun Microsystems, Inc
3
# This program is free software; you can redistribute it and/or modify
4
# it under the terms of the GNU General Public License as published by
5
# the Free Software Foundation; version 2 of the License.
7
# This program is distributed in the hope that it will be useful,
8
# but WITHOUT ANY WARRANTY; without even the implied warranty of
9
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10
# GNU General Public License for more details.
12
# You should have received a copy of the GNU General Public License
13
# along with this program; if not, write to the Free Software
14
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
16
# Tests for PERFORMANCE_SCHEMA
18
--source include/not_embedded.inc
19
--source include/have_perfschema.inc
23
grant ALL on *.* to 'pfs_user_1'@localhost with GRANT OPTION;
25
# Test denied privileges on performance_schema.*
27
--error ER_DBACCESS_DENIED_ERROR
28
grant ALL on performance_schema.* to 'pfs_user_2'@localhost
31
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
32
grant CREATE on performance_schema.* to 'pfs_user_2'@localhost;
34
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
35
grant DROP on performance_schema.* to 'pfs_user_2'@localhost;
37
--error ER_DBACCESS_DENIED_ERROR
38
grant REFERENCES on performance_schema.* to 'pfs_user_2'@localhost;
40
--error ER_DBACCESS_DENIED_ERROR
41
grant INDEX on performance_schema.* to 'pfs_user_2'@localhost;
43
--error ER_DBACCESS_DENIED_ERROR
44
grant ALTER on performance_schema.* to 'pfs_user_2'@localhost;
46
--error ER_DBACCESS_DENIED_ERROR
47
grant CREATE TEMPORARY TABLES on performance_schema.* to 'pfs_user_2'@localhost;
49
--error ER_DBACCESS_DENIED_ERROR
50
grant EXECUTE on performance_schema.* to 'pfs_user_2'@localhost;
52
--error ER_DBACCESS_DENIED_ERROR
53
grant CREATE VIEW on performance_schema.* to 'pfs_user_2'@localhost;
55
--error ER_DBACCESS_DENIED_ERROR
56
grant SHOW VIEW on performance_schema.* to 'pfs_user_2'@localhost;
58
--error ER_DBACCESS_DENIED_ERROR
59
grant CREATE ROUTINE on performance_schema.* to 'pfs_user_2'@localhost;
61
--error ER_DBACCESS_DENIED_ERROR
62
grant ALTER ROUTINE on performance_schema.* to 'pfs_user_2'@localhost;
64
--error ER_DBACCESS_DENIED_ERROR
65
grant EVENT on performance_schema.* to 'pfs_user_2'@localhost;
67
--error ER_DBACCESS_DENIED_ERROR
68
grant TRIGGER on performance_schema.* to 'pfs_user_2'@localhost;
70
# Test allowed privileges on performance_schema.*
72
grant SELECT on performance_schema.* to 'pfs_user_2'@localhost;
73
grant INSERT on performance_schema.* to 'pfs_user_2'@localhost;
74
grant UPDATE on performance_schema.* to 'pfs_user_2'@localhost;
75
grant DELETE on performance_schema.* to 'pfs_user_2'@localhost;
76
grant LOCK TABLES on performance_schema.* to 'pfs_user_2'@localhost;
78
# Test denied privileges on specific performance_schema tables.
79
# setup_instrument : example of PFS_updatable_acl
80
# events_waits_current : example of PFS_truncatable_acl
81
# file_instances : example of PFS_readonly_acl
83
--error ER_DBACCESS_DENIED_ERROR
84
grant ALL on performance_schema.setup_instruments to 'pfs_user_3'@localhost
87
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
88
grant CREATE on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
90
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
91
grant DROP on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
93
--error ER_DBACCESS_DENIED_ERROR
94
grant REFERENCES on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
96
--error ER_DBACCESS_DENIED_ERROR
97
grant INDEX on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
99
--error ER_DBACCESS_DENIED_ERROR
100
grant ALTER on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
102
--error ER_DBACCESS_DENIED_ERROR
103
grant CREATE VIEW on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
105
--error ER_DBACCESS_DENIED_ERROR
106
grant SHOW VIEW on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
108
--error ER_DBACCESS_DENIED_ERROR
109
grant TRIGGER on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
111
--error ER_TABLEACCESS_DENIED_ERROR
112
grant INSERT on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
114
--error ER_TABLEACCESS_DENIED_ERROR
115
grant DELETE on performance_schema.setup_instruments to 'pfs_user_3'@localhost;
117
grant SELECT on performance_schema.setup_instruments to 'pfs_user_3'@localhost
120
grant UPDATE on performance_schema.setup_instruments to 'pfs_user_3'@localhost
123
--error ER_DBACCESS_DENIED_ERROR
124
grant ALL on performance_schema.events_waits_current to 'pfs_user_3'@localhost
127
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
128
grant CREATE on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
130
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
131
grant DROP on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
133
--error ER_DBACCESS_DENIED_ERROR
134
grant REFERENCES on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
136
--error ER_DBACCESS_DENIED_ERROR
137
grant INDEX on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
139
--error ER_DBACCESS_DENIED_ERROR
140
grant ALTER on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
142
--error ER_DBACCESS_DENIED_ERROR
143
grant CREATE VIEW on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
145
--error ER_DBACCESS_DENIED_ERROR
146
grant SHOW VIEW on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
148
--error ER_DBACCESS_DENIED_ERROR
149
grant TRIGGER on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
151
--error ER_TABLEACCESS_DENIED_ERROR
152
grant INSERT on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
154
--error ER_TABLEACCESS_DENIED_ERROR
155
grant UPDATE on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
157
--error ER_TABLEACCESS_DENIED_ERROR
158
grant DELETE on performance_schema.events_waits_current to 'pfs_user_3'@localhost;
160
grant SELECT on performance_schema.events_waits_current to 'pfs_user_3'@localhost
163
--error ER_DBACCESS_DENIED_ERROR
164
grant ALL on performance_schema.file_instances to 'pfs_user_3'@localhost
167
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
168
grant CREATE on performance_schema.file_instances to 'pfs_user_3'@localhost;
170
# will be ER_DBACCESS_DENIED_ERROR once .FRM are removed
171
grant DROP on performance_schema.file_instances to 'pfs_user_3'@localhost;
173
--error ER_DBACCESS_DENIED_ERROR
174
grant REFERENCES on performance_schema.file_instances to 'pfs_user_3'@localhost;
176
--error ER_DBACCESS_DENIED_ERROR
177
grant INDEX on performance_schema.file_instances to 'pfs_user_3'@localhost;
179
--error ER_DBACCESS_DENIED_ERROR
180
grant ALTER on performance_schema.file_instances to 'pfs_user_3'@localhost;
182
--error ER_DBACCESS_DENIED_ERROR
183
grant CREATE VIEW on performance_schema.file_instances to 'pfs_user_3'@localhost;
185
--error ER_DBACCESS_DENIED_ERROR
186
grant SHOW VIEW on performance_schema.file_instances to 'pfs_user_3'@localhost;
188
--error ER_DBACCESS_DENIED_ERROR
189
grant TRIGGER on performance_schema.file_instances to 'pfs_user_3'@localhost;
191
--error ER_TABLEACCESS_DENIED_ERROR
192
grant INSERT on performance_schema.file_instances to 'pfs_user_3'@localhost;
194
--error ER_TABLEACCESS_DENIED_ERROR
195
grant UPDATE on performance_schema.file_instances to 'pfs_user_3'@localhost;
197
--error ER_TABLEACCESS_DENIED_ERROR
198
grant DELETE on performance_schema.file_instances to 'pfs_user_3'@localhost;
200
grant SELECT on performance_schema.file_instances to 'pfs_user_3'@localhost
203
# See bug#45354 LOCK TABLES is not a TABLE privilege
204
grant LOCK TABLES on performance_schema.* to 'pfs_user_3'@localhost
209
--source ../include/privilege.inc
211
connect (con1, localhost, pfs_user_1, , );
213
--source ../include/privilege.inc
217
connect (con2, localhost, pfs_user_2, , );
219
--source ../include/privilege.inc
223
connect (con3, localhost, pfs_user_3, , );
225
--source ../include/privilege.inc
231
revoke all privileges, grant option from 'pfs_user_1'@localhost;
232
revoke all privileges, grant option from 'pfs_user_2'@localhost;
233
revoke all privileges, grant option from 'pfs_user_3'@localhost;
234
drop user 'pfs_user_1'@localhost;
235
drop user 'pfs_user_2'@localhost;
236
drop user 'pfs_user_3'@localhost;
239
--echo # Test cases from WL#4818
242
CREATE user pfs_user_4;
243
--connect (pfs_user_4, localhost, pfs_user_4, , )
246
--echo # WL#4818, NFS4: Normal user does not have access to view data
247
--echo # without grants
250
--connection pfs_user_4
251
--echo # Select as pfs_user_4 should fail without grant
253
--error ER_TABLEACCESS_DENIED_ERROR
254
SELECT event_id FROM performance_schema.events_waits_history;
256
--error ER_TABLEACCESS_DENIED_ERROR
257
SELECT event_id FROM performance_schema.events_waits_history_long;
259
--error ER_TABLEACCESS_DENIED_ERROR
260
SELECT event_id FROM performance_schema.events_waits_current;
262
--error ER_TABLEACCESS_DENIED_ERROR
263
SELECT event_name FROM performance_schema.events_waits_summary_by_instance;
265
--error ER_TABLEACCESS_DENIED_ERROR
266
SELECT event_name FROM performance_schema.file_summary_by_instance;
269
--echo # WL#4818, NFS3: Normal user does not have access to change what is
270
--echo # instrumented without grants
273
--connection pfs_user_4
274
--echo # User pfs_user_4 should not be allowed to tweak instrumentation without
275
--echo # explicit grant
277
--error ER_TABLEACCESS_DENIED_ERROR
278
UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
280
--error ER_TABLEACCESS_DENIED_ERROR
281
UPDATE performance_schema.setup_instruments SET enabled = 'YES'
282
WHERE name LIKE 'wait/synch/mutex/%'
283
OR name LIKE 'wait/synch/rwlock/%';
285
--error ER_TABLEACCESS_DENIED_ERROR
286
UPDATE performance_schema.setup_consumers SET enabled = 'YES';
288
--error ER_TABLEACCESS_DENIED_ERROR
289
UPDATE performance_schema.setup_timers SET timer_name = 'TICK';
291
--error ER_TABLEACCESS_DENIED_ERROR
292
TRUNCATE TABLE performance_schema.events_waits_history_long;
294
--error ER_TABLEACCESS_DENIED_ERROR
295
TRUNCATE TABLE performance_schema.events_waits_history;
297
--error ER_TABLEACCESS_DENIED_ERROR
298
TRUNCATE TABLE performance_schema.events_waits_current;
301
--echo # WL#4814, NFS1: Can use grants to give normal user access
302
--echo # to turn on and off instrumentation
306
--echo # Grant access to change tables with the root account
308
GRANT UPDATE ON performance_schema.setup_consumers TO pfs_user_4;
309
GRANT UPDATE ON performance_schema.setup_timers TO pfs_user_4;
310
GRANT UPDATE, SELECT ON performance_schema.setup_instruments TO pfs_user_4;
311
GRANT DROP ON performance_schema.events_waits_current TO pfs_user_4;
312
GRANT DROP ON performance_schema.events_waits_history TO pfs_user_4;
313
GRANT DROP ON performance_schema.events_waits_history_long TO pfs_user_4;
315
--connection pfs_user_4
316
--echo # User pfs_user_4 should now be allowed to tweak instrumentation
318
UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
320
UPDATE performance_schema.setup_instruments SET enabled = 'YES'
321
WHERE name LIKE 'wait/synch/mutex/%'
322
OR name LIKE 'wait/synch/rwlock/%';
324
UPDATE performance_schema.setup_consumers SET enabled = 'YES';
326
UPDATE performance_schema.setup_timers SET timer_name = 'TICK';
328
TRUNCATE TABLE performance_schema.events_waits_history_long;
329
TRUNCATE TABLE performance_schema.events_waits_history;
330
TRUNCATE TABLE performance_schema.events_waits_current;
333
--disconnect pfs_user_4
334
--source include/wait_until_disconnected.inc
336
REVOKE ALL PRIVILEGES, GRANT OPTION FROM pfs_user_4;
337
DROP USER pfs_user_4;
339
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
340
UPDATE performance_schema.setup_consumers SET enabled = 'YES';
341
UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE';