#-----------------------------------------------------------------------
# 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_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_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;
#-----------------------------------------------------------------------