292 lines
16 KiB
Plaintext
292 lines
16 KiB
Plaintext
#-----------------------------------------------------------------------
|
|
# 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;
|
|
#-----------------------------------------------------------------------
|