Preface
I supposed we are encountering a situation that there's an anonymous user has connected in our MySQL database with an account which has large privileges.The user is doing some query operations with bad performance.Which may subsequently lead to a high load of our database server.How to solve this issue efficiently and immediately?There's a little trick we can use below.
Example
Create a test account.
1 (root@localhost mysql3306.sock)[(none)]>create user aaron8219@'192.168.1.%' identified by 'zlm'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 (root@localhost mysql3306.sock)[(none)]>select user,host from mysql.user; 5 +---------------+-------------+ 6 | user | host | 7 +---------------+-------------+ 8 | rpl_mgr | % | 9 | aaron8219 | 192.168.1.% | 10 | repl | 192.168.1.% | 11 | replica | 192.168.1.% | 12 | zlm | 192.168.1.% | 13 | mysql.session | localhost | 14 | mysql.sys | localhost | 15 | root | localhost | 16 +---------------+-------------+ 17 8 rows in set (0.00 sec) 18 19 (root@localhost mysql3306.sock)[(none)]>grant all privileges on *.* to aaron8219@'192.168.1.%'; //Grant the supreme privileges to the user. 20 Query OK, 0 rows affected (0.00 sec) 21 22 (root@localhost mysql3306.sock)[(none)]>show grants for aaron8219@'192.168.1.%'; 23 +----------------------------------------------------------+ 24 | Grants for aaron8219@192.168.1.% | 25 +----------------------------------------------------------+ 26 | GRANT ALL PRIVILEGES ON *.* TO 'aaron8219'@'192.168.1.%' | 27 +----------------------------------------------------------+ 28 1 row in set (0.00 sec)
Connect to database with the new account.
1 [root@zlm2 09:25:29 ~] 2 #mysql -uaaron8219 -pzlm -h192.168.1.101 3 mysql: [Warning] Using a password on the command line interface can be insecure. 4 Welcome to the MySQL monitor. Commands end with ; or g. 5 Your MySQL connection id is 4 6 Server version: 5.7.21-log MySQL Community Server (GPL) 7 8 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 9 10 Oracle is a registered trademark of Oracle Corporation and/or its 11 affiliates. Other names may be trademarks of their respective 12 owners. 13 14 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 15 16 (aaron8219@192.168.1.101 3306)[(none)]>show databases; //The user "aaron8219" can see all the databases in the current MySQL instance. 17 +--------------------+ 18 | Database | 19 +--------------------+ 20 | information_schema | 21 | mysql | 22 | performance_schema | 23 | sys | 24 | sysbench | 25 | zlm | 26 +--------------------+ 27 6 rows in set (0.01 sec) 28 29 (aaron8219@192.168.1.101 3306)[(none)]>create database aaron8219; 30 Query OK, 1 row affected (0.00 sec) 31 32 (aaron8219@192.168.1.101 3306)[(none)]>use aaron8219; 33 Database changed 34 (aaron8219@192.168.1.101 3306)[aaron8219]>create table t1( 35 -> id int, 36 -> name char(10) 37 -> ) engine=innodb; 38 Query OK, 0 rows affected (0.02 sec)
Create another precise account which name is equal to the one above and with an intact ip address.
1 (root@localhost mysql3306.sock)[(none)]>create user aaron8219@'192.168.1.101' identified by 'zlm'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 (root@localhost mysql3306.sock)[(none)]>select user,host from mysql.user; 5 +---------------+---------------+ 6 | user | host | 7 +---------------+---------------+ 8 | rpl_mgr | % | 9 | aaron8219 | 192.168.1.% | 10 | repl | 192.168.1.% | 11 | replica | 192.168.1.% | 12 | zlm | 192.168.1.% | 13 | aaron8219 | 192.168.1.101 | 14 | mysql.session | localhost | 15 | mysql.sys | localhost | 16 | root | localhost | 17 +---------------+---------------+ 18 9 rows in set (0.00 sec) 19 20 (root@localhost mysql3306.sock)[(none)]>grant all privileges on aaron8219.* to aaron8219@'192.168.1.101'; //Grant the privileges only on "aaron8219" database. 21 Query OK, 0 rows affected (0.00 sec) 22 23 (root@localhost mysql3306.sock)[(none)]>show grants for aaron8219@'192.168.1.101'; 24 +----------------------------------------------------------------------+ 25 | Grants for aaron8219@192.168.1.101 | 26 +----------------------------------------------------------------------+ 27 | GRANT USAGE ON *.* TO 'aaron8219'@'192.168.1.101' | 28 | GRANT ALL PRIVILEGES ON `aaron8219`.* TO 'aaron8219'@'192.168.1.101' | 29 +----------------------------------------------------------------------+ 30 2 rows in set (0.00 sec)
Connect to database with the account again.
1 [root@zlm2 09:32:57 ~] 2 #mysql -uaaron8219 -pzlm -h192.168.1.101 3 mysql: [Warning] Using a password on the command line interface can be insecure. 4 Welcome to the MySQL monitor. Commands end with ; or g. 5 Your MySQL connection id is 5 6 Server version: 5.7.21-log MySQL Community Server (GPL) 7 8 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 9 10 Oracle is a registered trademark of Oracle Corporation and/or its 11 affiliates. Other names may be trademarks of their respective 12 owners. 13 14 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 15 16 (aaron8219@192.168.1.101 3306)[(none)]>show databases; //Only the "aaron8219" database can be list. 17 +--------------------+ 18 | Database | 19 +--------------------+ 20 | information_schema | 21 | aaron8219 | 22 +--------------------+ 23 2 rows in set (0.00 sec) 24 25 (aaron8219@192.168.1.101 3306)[(none)]>show grants for aaron8219@'192.168.1.101'; 26 +----------------------------------------------------------------------+ 27 | Grants for aaron8219@192.168.1.101 | 28 +----------------------------------------------------------------------+ 29 | GRANT USAGE ON *.* TO 'aaron8219'@'192.168.1.101' | 30 | GRANT ALL PRIVILEGES ON `aaron8219`.* TO 'aaron8219'@'192.168.1.101' | 31 +----------------------------------------------------------------------+ 32 2 rows in set (0.00 sec) 33 34 (aaron8219@192.168.1.101 3306)[(none)]>use aaron8219; 35 Reading table information for completion of table and column names 36 You can turn off this feature to get a quicker startup with -A 37 38 Database changed 39 (aaron8219@192.168.1.101 3306)[aaron8219]>show tables; 40 +---------------------+ 41 | Tables_in_aaron8219 | 42 +---------------------+ 43 | t1 | 44 +---------------------+ 45 1 row in set (0.00 sec) 46 47 (aaron8219@192.168.1.101 3306)[aaron8219]>insert into t1 values(1,'abc'); 48 Query OK, 1 row affected (0.00 sec) 49 50 (aaron8219@192.168.1.101 3306)[aaron8219]>select * from t1; 51 +------+------+ 52 | id | name | 53 +------+------+ 54 | 1 | abc | 55 +------+------+ 56 1 row in set (0.00 sec) 57 58 //Eventrually,the privileges of account aaron8219@'192.168.1.%' has been restricted merely on database "aaron8219". 59 //Further more,we can revoke all the privileges on it either.
Revoke the all privileges of the account.
1 (root@localhost mysql3306.sock)[(none)]>revoke all privileges on aaron8219.* from aaron8219@'192.168.1.101'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 (root@localhost mysql3306.sock)[(none)]>show grants for aaron8219@'192.168.1.101'; 5 +---------------------------------------------------+ 6 | Grants for aaron8219@192.168.1.101 | 7 +---------------------------------------------------+ 8 | GRANT USAGE ON *.* TO 'aaron8219'@'192.168.1.101' | 9 +---------------------------------------------------+ 10 1 row in set (0.00 sec)
Connect to database with the account third times.
1 [root@zlm2 10:18:20 ~] 2 #mysql -uaaron8219 -pzlm -h192.168.1.101 3 mysql: [Warning] Using a password on the command line interface can be insecure. 4 Welcome to the MySQL monitor. Commands end with ; or g. 5 Your MySQL connection id is 8 6 Server version: 5.7.21-log MySQL Community Server (GPL) 7 8 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 9 10 Oracle is a registered trademark of Oracle Corporation and/or its 11 affiliates. Other names may be trademarks of their respective 12 owners. 13 14 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 15 16 (aaron8219@192.168.1.101 3306)[(none)]>show databases; 17 +--------------------+ 18 | Database | 19 +--------------------+ 20 | information_schema | 21 +--------------------+ 22 1 row in set (0.00 sec) 23 24 (aaron8219@192.168.1.101 3306)[(none)]>create database test; 25 ERROR 1044 (42000): Access denied for user 'aaron8219'@'192.168.1.101' to database 'test' 26 27 //This time,the account of aaron8219 login with ip "192.168.1.101" can do nothing in the target instance.