MySQL用户权限控制一例

作者:计算机专家

**Connect to database with the account third times.**

 

 

 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.
 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.

    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.

Connect to database with the account again.

 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)

Create a test account.

Revoke the all privileges of the account.

 

**Create another precise account which name is equal to the one above and with an intact ip address.**

Example

 

 

**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)
 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)

 

 

 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)

Preface

 

 

本文由杏彩发布,转载请注明来源

关键词: