MySQL用户权限控制一例

 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)

 

Revoke the all privileges of the account.

MySQL considers both your host name and user name in identifying you because there is no reason to assume that a given user name belongs to the same person on all hosts. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com. MySQL handles this by enabling you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by joe from office.example.com, and a different set of privileges for connections by joe from home.example.com. To see what privileges a given account has, use the SHOW GRANTS statement. For example:

**Connect to database with the new account.**

MySQL特权系统的主要功能是对从给定主机连接的用户进行身份验证,并将该用户与数据库的特权(如SELECT,INSERT,UPDATE和DELETE)相关联。 其他功能包括允许匿名用户和授予MySQL特定功能
(如LOAD DATA INFILE和管理操作)的权限。

Example

如果您的权限在您连接时更改(由您自己或其他人),那么这些更改不会立即对您发出的下一条语句生效。 有关服务器重新加载授予表的条件的详细信息,请参见第7.2.6节“权限更改生效时”。

 

If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. For details about the conditions under which the server reloads the grant tables, see Section 7.2.6, “When Privilege Changes Take Effect”.

    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.

The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECTINSERT,UPDATE, and DELETE. Additional functionality includes the ability to have anonymous users and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

 

 

 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.

There are some things that you cannot do with the MySQL privilege system:

 

MySQL access control involves two stages when you run a client program that connects to the server:

 

在内部,服务器将权限信息存储在mysql数据库(即名为mysql的数据库)的授权表中。 MySQL服务器在启动时将这些表的内容读取到内存中,并基于对授权表的内存中副本的访问控制决策。
 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.**

 

Preface

Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

 

SHOW GRANTS FOR 'joe'@'office.example.com';
SHOW GRANTS FOR 'joe'@'home.example.com';

MySQL在识别您时会考虑您的主机名和用户名,因为没有理由假定给定的用户名属于所有主机上的同一个人。 例如,从office.example.com连接的用户joe不需要是与
从home.example.com连接的用户joe相同的人员。 MySQL通过使您能够区分恰好相同名称的不同主机上的用户来处理这个问题:您可以通过joe从office.example.com
授予一组连接权限,并通过joe从home获取一组不同的特权 .example.com。 要查看给定帐户具有的特权,请使用SHOW GRANTS语句。 例如:

本文由皇家赌场手机版发布于首页,转载请注明出处:MySQL用户权限控制一例

相关阅读