MySQL权限系统(一).The MySQL Access Privilege System 概述

 

纯属个人阅读,如有翻译错误,请指出

Preface

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.

 

 

    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.

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

 

 

Example

 

 

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

Create a
test account.

  • You cannot explicitly specify that a given user should be denied
    access. That is, you cannot explicitly match a user and then refuse
    the connection.

  • You cannot specify that a user has privileges to create or drop
    tables in a database but not to create or drop the database itself.

  • A password applies globally to an account. You cannot associate a
    password with a specific object such as a database, table, or
    routine.

    在些事情你用MySQL的权限系统是处理不到的:

    •您不能明确指定应拒绝给定用户访问。 也就是说,您不能明确的匹配用户,然后拒绝连接。

    •您不能指定用户具有在数据库中创建或删除表的权限,但不能指定创建或删除数据库本身的权限。

    •全局适用于帐户的密码。 您不能将密码与特定对象(如数据库,表或例程)关联。

 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)

 

 

The user interface to the MySQL privilege system consists of SQL
statements such
as CREATE USERGRANT,
and REVOKE.
See Section 14.7.1, “Account Management
Statements”
.

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

Internally, the server stores privilege information in the grant tables
of the mysql database (that is, in the database named mysql). The
MySQL server reads the contents of these tables into memory when it
starts and bases access-control decisions on the in-memory copies of the
grant tables.

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

 

 

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

The MySQL privilege system ensures that all users may perform only the
operations permitted to them. As a user, when you connect to a MySQL
server, your identity is determined by the host
from which you connect
 and the user name you
specify
. When you issue requests after connecting, the system grants
privileges according to your identity and what
you want to do
.

 

MySQL特权系统确保所有用户只能执行允许的操作。 作为用户,当您连接到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)

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:

 

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语句。 例如:

Connect to
database with the account again.

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

 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.

Stage 1: The server accepts or rejects the
connection based on your identity and whether you can verify your
identity by supplying the correct password.

 

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.

Revoke the
all privileges of the account.

当您运行连接到服务器的客户端程序时,MySQL访问控制涉及两个阶段:

阶段1:服务器根据您的身份接受或拒绝连接,以及是否可以通过提供正确的密码验证您的身份。

阶段2:假设您可以连接,服务器将检查您发出的每个语句,以确定您是否有足够的权限执行它。 例如,如果尝试从数据库中的表中选择行或从数据库中删除表,则服务器将验证您是否具有表的SELECT特权或数据库的DROP特权。
 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)

 

 

For a more detailed description of what happens during each stage,
see Section 7.2.4, “Access Control, Stage 1: Connection
Verification”
,
and Section 7.2.5, “Access Control, Stage 2: Request
Verification”
.

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

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

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

 

 

For general security-related advice, see Section 7.1, “General Security
Issues”
.
For help in diagnosing privilege-related problems, see Section 7.2.7,
“Troubleshooting Problems Connecting to
MySQL”
.