1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
#
# WL#14303: More granular privilege control of the FLUSH operations
#
# Check who has the FLUSH_xx privileges.
# Success criteria: Should be root and 4 rows
SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE LIKE 'FLUSH_%' ORDER BY 1,2,3;
GRANTEE PRIVILEGE_TYPE IS_GRANTABLE
'root'@'localhost' FLUSH_OPTIMIZER_COSTS YES
'root'@'localhost' FLUSH_STATUS YES
'root'@'localhost' FLUSH_TABLES YES
'root'@'localhost' FLUSH_USER_RESOURCES YES
# FR1: FLUSH HOSTS is deprecated
# Success criteria: deprecation warning
FLUSH HOSTS;
Warnings:
Warning 1287 'FLUSH HOSTS' is deprecated and will be removed in a future release. Please use TRUNCATE TABLE performance_schema.host_cache instead
# Success criteria: no warning
TRUNCATE TABLE performance_schema.host_cache;
# FR2: FLUSH_OPTIMIZER_COSTS should allow FLUSH OPTIMIZER_COSTS
CREATE USER wl14303@localhost;
GRANT FLUSH_OPTIMIZER_COSTS ON *.* TO wl14303@localhost;
connect FLUSH_OPTIMIZER_COSTS_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH OPTIMIZER_COSTS;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_OPTIMIZER_COSTS_con;
DROP USER wl14303@localhost;
# FR3: FLUSH_STATUS should allow FLUSH STATUS
CREATE USER wl14303@localhost;
GRANT FLUSH_STATUS ON *.* TO wl14303@localhost;
connect FLUSH_STATUS_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH STATUS;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_STATUS_con;
DROP USER wl14303@localhost;
# FR4: FLUSH_USER_RESOURCES should allow FLUSH USER_RESOURCES
CREATE USER wl14303@localhost;
GRANT FLUSH_USER_RESOURCES ON *.* TO wl14303@localhost;
connect FLUSH_USER_RESOURCES_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH USER_RESOURCES;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_USER_RESOURCES_con;
DROP USER wl14303@localhost;
# FR5: FLUSH_TABLES should allow FLUSH TABLES
CREATE USER wl14303@localhost;
GRANT FLUSH_TABLES ON *.* TO wl14303@localhost;
CREATE TABLE t1(a int);
GRANT ALL PRIVILEGES ON t1 TO wl14303@localhost;
connect FLUSH_TABLES_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH TABLES;
# Success criteria: should work
FLUSH TABLES t1;
# Success criteria: should work
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
# Success criteria: should work
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
# Success criteria: should work
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_TABLES_con;
REVOKE ALL PRIVILEGES ON t1 FROM wl14303@localhost;
DROP USER wl14303@localhost;
DROP TABLE t1;
# FR6: RELOAD privilege works
CREATE USER wl14303@localhost;
GRANT RELOAD ON *.* TO wl14303@localhost;
connect RELOAD_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH HOSTS;
Warnings:
Warning 1287 'FLUSH HOSTS' is deprecated and will be removed in a future release. Please use TRUNCATE TABLE performance_schema.host_cache instead
FLUSH OPTIMIZER_COSTS;
FLUSH STATUS;
FLUSH USER_RESOURCES;
FLUSH TABLES;
connection default;
disconnect RELOAD_con;
DROP USER wl14303@localhost;
# test combo of FLUSH options
CREATE USER wl14303@localhost;
GRANT FLUSH_STATUS,FLUSH_USER_RESOURCES ON *.* TO wl14303@localhost;
connect COMBO_con,localhost,wl14303,,;
# two new ones (allowed, forbidden): should fail
FLUSH STATUS,OPTIMIZER_COSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD or FLUSH_OPTIMIZER_COSTS privilege(s) for this operation
# two new ones (forbidden, allowed): should fail
FLUSH OPTIMIZER_COSTS,STATUS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD or FLUSH_OPTIMIZER_COSTS privilege(s) for this operation
# old and a new one: should fail
FLUSH STATUS,PRIVILEGES;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
# two new ones: should work
FLUSH STATUS,USER_RESOURCES;
connection default;
disconnect COMBO_con;
DROP USER wl14303@localhost;
# FR8: test mysqladmin
SET @saved_log_output = @@global.log_output;
SET @saved_general_log = @@global.general_log;
SET global log_output='table';
SET global general_log=on;
SET global general_log=@saved_general_log;
SET global log_output=@saved_log_output;
# Success criteria: should contain just 1 TRUNCATE TABLE rows
SELECT command_type, argument FROM mysql.general_log WHERE command_type='Query';
command_type Query
argument SET global general_log=on
command_type Query
argument TRUNCATE TABLE performance_schema.host_cache
command_type Query
argument SET global general_log=@saved_general_log
command_type Query
argument SET global log_output=@saved_log_output
TRUNCATE TABLE mysql.general_log;
# End of 8.0 tests
|
