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.