SESSION - 1: (Don't close this mysql session) [root@testbox ~]# /home/mysql57/mysql_5731/bin/mysql -u root -S /tmp/mysql-5731.sock -p'************' -A mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> \s -------------- /home/mysql57/mysql_5731/bin/mysql Ver 14.14 Distrib 5.7.31, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 3 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.31-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql-5731.sock Uptime: 2 min 13 sec Threads: 1 Questions: 9 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.067 -------------- mysql> mysql> mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY,PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%'; +------------------------------------------+----------------+---------------+--------------------+---------------------+-----------------------+------------------------+---------------+ | PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | +------------------------------------------+----------------+---------------+--------------------+---------------------+-----------------------+------------------------+---------------+ | CONNECTION_CONTROL | 1.0 | ACTIVE | AUDIT | 4.1 | connection_control.so | 1.7 | Oracle Inc | | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | 1.0 | ACTIVE | INFORMATION SCHEMA | 50731.0 | connection_control.so | 1.7 | Oracle Inc | +------------------------------------------+----------------+---------------+--------------------+---------------------+-----------------------+------------------------+---------------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+----------+------------------+ 1 row in set (0.00 sec) mysql> mysql> show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 0 | +------------------------------------+-------+ 1 row in set (0.00 sec) Empty set (0.00 sec) mysql> SESSION - 2: [root@testbox ~]# /home/mysql57/mysql_5731/bin/mysql -u root -S /tmp/mysql-5731.sock -p'' mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@testbox ~]# . . . . repeat multiple times.. SESSION - 1: mysql> show grants; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 6 | +------------------------------------+-------+ 1 row in set (0.00 sec) +--------------------+-----------------+ | USERHOST | FAILED_ATTEMPTS | +--------------------+-----------------+ | 'root'@'localhost' | 5 | +--------------------+-----------------+ 1 row in set (0.00 sec) mysql> create user vidya identified by '************'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, FILE, SHUTDOWN ON *.* TO 'vidya'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> mysql> show grants for vidya; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for vidya@% | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SESSION - 2: [root@testbox ~]# /home/mysql57/mysql_5731/bin/mysql -u vidya -S /tmp/mysql-5731.sock -p'************' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(), current_user(); +-----------------+----------------+ | user() | current_user() | +-----------------+----------------+ | vidya@localhost | vidya@% | +-----------------+----------------+ 1 row in set (0.00 sec) mysql> show grants; +--------------------------------------------------------------+ | Grants for vidya@% | +--------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 6 | +------------------------------------+-------+ 1 row in set (0.00 sec) +--------------------+-----------------+ | USERHOST | FAILED_ATTEMPTS | +--------------------+-----------------+ | 'root'@'localhost' | 5 | +--------------------+-----------------+ 1 row in set (0.00 sec) mysql> \q Bye SESSION - 1: mysql> grant super on *.* to vidya; Query OK, 0 rows affected (0.00 sec) mysql> show grants for vidya; +--------------------------------------------------------------+ | Grants for vidya@% | +--------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +--------------------------------------------------------------+ 1 row in set (0.01 sec) SESSION - 2: [root@testbox ~]# /home/mysql57/mysql_5731/bin/mysql -u vidya -S /tmp/mysql-5731.sock -p'************' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(), current_user(); +-----------------+----------------+ | user() | current_user() | +-----------------+----------------+ | vidya@localhost | vidya@% | +-----------------+----------------+ 1 row in set (0.00 sec) mysql> show grants; +--------------------------------------------------------------+ | Grants for vidya@% | +--------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 6 | +------------------------------------+-------+ 1 row in set (0.00 sec) +--------------------+-----------------+ | USERHOST | FAILED_ATTEMPTS | +--------------------+-----------------+ | 'root'@'localhost' | 5 | +--------------------+-----------------+ 1 row in set (0.00 sec) mysql> \q Bye [root@testbox ~]# SESSION - 1: mysql> revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* from vidya; Query OK, 0 rows affected (0.00 sec) mysql> show grants for vidya; +-----------------------------------------------------+ | Grants for vidya@% | +-----------------------------------------------------+ | GRANT SUPER ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +-----------------------------------------------------+ 1 row in set (0.00 sec) SESSION - 2: [root@testbox ~]# /home/mysql57/mysql_5731/bin/mysql -u vidya -S /tmp/mysql-5731.sock -p'************' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show grants; +-----------------------------------------------------+ | Grants for vidya@% | +-----------------------------------------------------+ | GRANT SUPER ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 6 | +------------------------------------+-------+ 1 row in set (0.00 sec) +--------------------+-----------------+ | USERHOST | FAILED_ATTEMPTS | +--------------------+-----------------+ | 'root'@'localhost' | 5 | +--------------------+-----------------+ 1 row in set (0.00 sec) mysql> \q Bye [root@testbox ~]# SESSION - 1: mysql> show grants for vidya; +-----------------------------------------------------+ | Grants for vidya@% | +-----------------------------------------------------+ | GRANT SUPER ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'vidya'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> mysql> show grants for vidya; +--------------------------------------------------------------+ | Grants for vidya@% | +--------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> revoke super on *.* from vidya; Query OK, 0 rows affected (0.00 sec) mysql> show grants for vidya; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for vidya@% | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) SESSION - 2: [root@testbox ~]# /home/mysql57/mysql_5731/bin/mysql -u vidya -S /tmp/mysql-5731.sock -p'************' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> select user(), current_user(); +-----------------+----------------+ | user() | current_user() | +-----------------+----------------+ | vidya@localhost | vidya@% | +-----------------+----------------+ 1 row in set (0.00 sec) mysql> mysql> show grants; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for vidya@% | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'vidya'@'%' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show global status like '%connection_control%'; select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 6 | +------------------------------------+-------+ 1 row in set (0.00 sec) Empty set (0.00 sec) mysql> \q Bye [root@testbox ~]#