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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
|
#-----------------------------------------------------------------------
# Setup
# Install connection_control plugin
INSTALL PLUGIN connection_control SONAME 'CONNECTION_CONTROL_LIB';
INSTALL PLUGIN connection_control_failed_login_attempts SONAME 'CONNECTION_CONTROL_LIB';
CREATE USER no_privs@localhost IDENTIFIED BY 'abcd';
#-----------------------------------------------------------------------
# Case 1 : connection_control_failed_connections_threshold
SHOW GRANTS;
Grants for root@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
SET @saved_value = @@global.connection_control_failed_connections_threshold;
SELECT @saved_value;
@saved_value
3
SET @@global.connection_control_failed_connections_threshold = @saved_value;
# 1.1 : Setting connection_control_failed_connections_threshold to valid
# value
SET @@global.connection_control_failed_connections_threshold = 20;
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
20
SET @@global.connection_control_failed_connections_threshold = 2000;
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
2000
SET @@global.connection_control_failed_connections_threshold = 2147483647;
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
2147483647
SET @@global.connection_control_failed_connections_threshold = DEFAULT;
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
# 1.2 : Setting connection_control_failed_connections_threshold to
# invalid value
SET @@global.connection_control_failed_connections_threshold = NULL;
ERROR 42000: Incorrect argument type to variable 'connection_control_failed_connections_threshold'
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
SET @@global.connection_control_failed_connections_threshold = `SELECT * FROM mysql.user`;
ERROR 42000: Incorrect argument type to variable 'connection_control_failed_connections_threshold'
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
SET @@global.connection_control_failed_connections_threshold = -20;
ERROR 42000: Variable 'connection_control_failed_connections_threshold' can't be set to the value of '-20'
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
SET @@global.connection_control_failed_connections_threshold = 9223372036854775808;
ERROR 42000: Variable 'connection_control_failed_connections_threshold' can't be set to the value of '9223372036854775808'
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
SET @@global.connection_control_failed_connections_threshold = -9223372036854775808;
ERROR 42000: Variable 'connection_control_failed_connections_threshold' can't be set to the value of '-9223372036854775808'
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
# Switch to conn_no_privs
# 1.3 : Use no_privs@localhost to set
# connection_control_failed_connections_threshold to valid value
SET @@global.connection_control_failed_connections_threshold = 2147483647;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
SET @@global.connection_control_failed_connections_threshold = DEFAULT;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
# 1.4 : Use no_privs@localhost to set
# connection_control_failed_connections_threshold to invalid value
SET @@global.connection_control_failed_connections_threshold = NULL;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
SET @@global.connection_control_failed_connections_threshold = 9223372036854775808;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
SET @@global.connection_control_failed_connections_threshold = @saved_value;
SELECT @@global.connection_control_failed_connections_threshold;
@@global.connection_control_failed_connections_threshold
3
#-----------------------------------------------------------------------
# Case 2 : connection_control_min_connection_delay
SET @saved_value= @@global.connection_control_min_connection_delay;
SELECT @saved_value;
@saved_value
1000
# 2.1 : Setting connection_control_min_connection_delay to valid
# value
SET @@global.connection_control_min_connection_delay = 20000;
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
20000
SET @@global.connection_control_min_connection_delay = 2000;
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
2000
SET @@global.connection_control_min_connection_delay = 2147483647;
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
2147483647
SET @@global.connection_control_min_connection_delay = DEFAULT;
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
# 2.2 : Setting connection_control_min_connection_delay to
# invalid value
SET @@global.connection_control_min_connection_delay = NULL;
ERROR 42000: Incorrect argument type to variable 'connection_control_min_connection_delay'
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET @@global.connection_control_min_connection_delay = `SELECT * FROM mysql.user`;
ERROR 42000: Incorrect argument type to variable 'connection_control_min_connection_delay'
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET @@global.connection_control_min_connection_delay = -20;
ERROR 42000: Variable 'connection_control_min_connection_delay' can't be set to the value of '-20'
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET @@global.connection_control_min_connection_delay = 9223372036854775808;
ERROR 42000: Variable 'connection_control_min_connection_delay' can't be set to the value of '9223372036854775808'
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET @@global.connection_control_min_connection_delay = -9223372036854775808;
ERROR 42000: Variable 'connection_control_min_connection_delay' can't be set to the value of '-9223372036854775808'
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET@@global.connection_control_min_connection_delay = 20;
ERROR 42000: Variable 'connection_control_min_connection_delay' can't be set to the value of '20'
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
# Switch to conn_no_privs
# 2.3 : Use no_privs@localhost to set
# connection_control_min_connection_delay to valid value
SET @@global.connection_control_min_connection_delay = 2147483647;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET @@global.connection_control_min_connection_delay = DEFAULT;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
# 2.4 : Use no_privs@localhost to set
# connection_control_min_connection_delay to invalid value
SET @@global.connection_control_min_connection_delay = NULL;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET @@global.connection_control_min_connection_delay = 9223372036854775808;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
# Switch to default connection
# 2.5 : Setting connection_control_min_connection_delay to a value
# greater than connection_control_max_connection_delay
SET @saved_max_delay= @@global.connection_control_max_connection_delay;
SET @@global.connection_control_max_connection_delay= 10000;
SET @@global.connection_control_min_connection_delay= 11000;
ERROR 42000: Variable 'connection_control_min_connection_delay' can't be set to the value of '11000'
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
SET @@global.connection_control_max_connection_delay= @saved_max_delay;
SET @@global.connection_control_min_connection_delay = @saved_value;
SELECT @@global.connection_control_min_connection_delay;
@@global.connection_control_min_connection_delay
1000
#-----------------------------------------------------------------------
# Case 3 : connection_control_max_connection_delay
SET @saved_value= @@global.connection_control_max_connection_delay;
SELECT @saved_value;
@saved_value
2147483647
# 3.1 : Setting connection_control_max_connection_delay to valid
# value
SET @@global.connection_control_max_connection_delay = 20000;
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
20000
SET @@global.connection_control_max_connection_delay = 2000;
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2000
SET @@global.connection_control_max_connection_delay = 2147483647;
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = DEFAULT;
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
# 3.2 : Setting connection_control_max_connection_delay to
# invalid value
SET @@global.connection_control_max_connection_delay = NULL;
ERROR 42000: Incorrect argument type to variable 'connection_control_max_connection_delay'
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = `SELECT * FROM mysql.user`;
ERROR 42000: Incorrect argument type to variable 'connection_control_max_connection_delay'
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = -20;
ERROR 42000: Variable 'connection_control_max_connection_delay' can't be set to the value of '-20'
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = 9223372036854775808;
ERROR 42000: Variable 'connection_control_max_connection_delay' can't be set to the value of '9223372036854775808'
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = -9223372036854775808;
ERROR 42000: Variable 'connection_control_max_connection_delay' can't be set to the value of '-9223372036854775808'
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = 20;
ERROR 42000: Variable 'connection_control_max_connection_delay' can't be set to the value of '20'
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
# Switch to conn_no_privs
# 3.3 : Use no_privs@localhost to set
# connection_control_max_connection_delay to valid value
SET @@global.connection_control_max_connection_delay = 2147483647;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = DEFAULT;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
# 3.4 : Use no_privs@localhost to set
# connection_control_max_connection_delay to invalid value
SET @@global.connection_control_max_connection_delay = NULL;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_max_connection_delay = 9223372036854775808;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
# Switch to default connection
# 3.5 : Setting connection_control_min_connection_delay to a value
# greater than connection_control_max_connection_delay
SET @saved_min_delay= @@global.connection_control_min_connection_delay;
SET @@global.connection_control_min_connection_delay= 11000;
SET @@global.connection_control_max_connection_delay= 10000;
ERROR 42000: Variable 'connection_control_max_connection_delay' can't be set to the value of '10000'
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
SET @@global.connection_control_min_connection_delay= @saved_min_delay;
SET @@global.connection_control_max_connection_delay = @saved_value;
SELECT @@global.connection_control_max_connection_delay;
@@global.connection_control_max_connection_delay
2147483647
#-----------------------------------------------------------------------
# Cleanup
DROP USER no_privs@localhost;
# Uninstall connection_control plugin
UNINSTALL PLUGIN connection_control;
UNINSTALL PLUGIN connection_control_failed_login_attempts;
#-----------------------------------------------------------------------
|
