MySQL实践——MySQL权限

1. 权限概述

MySQL的mysql 系统库提供了user、db、tables_priv、columns_priv、procs_priv、proxies_priv几个表，用于存放不同权限范围的用户账号相关数据，这些表共同组成了MySQL的访问权限控制系统。

MySQL访问权限控制系统的主要功能是对从给定主机连接到MySQL服务器的用户进行身份验证，并校验该用户在该服务器中的数据库对象访问权限（如SELECT、INSERT、UPDATE和DELETE）。另外，还包括管理匿名用户访问和授予特定的MySQL权限的功能（如执行 LOAD DATA INFILE 语句和管理操作权限等）。MySQL访问权限控制系统的用户界面由几条SQL语句组成，如CREATE USER、GRANT和REVOKE。

在服务器内部，MySQL将权限信息存储在mysql系统库的权限表中。MySQL服务器在启动时将这些表的内容读入内存，后续针对用户的访问控制决策基于权限表的内存副本来实现。

MySQL访问权限控制系统可以确保只有被允许的（与用户权限匹配的）操作才能够在服务器中执行。当一个用户连接到MySQL服务器时，其认证身份由“请求连接的主机名和用户名”确定，MySQL使用主机名+用户名的方式来识别和区分“相同主机不同用户”和“不同主机相同用户”发出的请求（例如：从office.example.com连接的用户joe和从home.example.com连接的用户joe在MySQL服务器中实际上是被当作两个不同的连接者来处理的，所以可以设置不同的密码、不同的权限）。例如：

当用户使用客户端程序连接到MySQL服务器时，MySQL的访问控制分为如下两个阶段。

• 阶段1：服务器根据身份标识（主机名+用户名组成的账号名称）在MySQL 的访问权限控制表中查询相关信息，以确定需要接受或拒绝该用户的连接（没有查询到就拒绝连接）。如果查询到了用户记录，则校验用户提供的账号密码是否正确，如果密码不正确则拒绝连接。这个阶段的报错信息类似于：ERROR 1045 (28000): Access denied for user 'test_a'@'localhost'(using password: YES)。

• 阶段2：用户连接成功之后，服务器会检查用户访问请求中的每个声明，确定是否有足够的权限来执行。例如：如果尝试从数据库的表中查询数据行或从数据库中删除表，服务器将验证该用户否具有该表的SELECT权限或数据库的DROP权限，如果无对应权限，则这个阶段的报错信息类似于：ERROR 1142 (42000) at line 1: UPDATE command denied to user'test_a'@'localhost' for table 'sbtest1'。

如果某用户在连接期间发生了权限变更（自己或者其他用户修改了权限），那么该用户执行下一条语句时，该权限变更不一定会立即生效。如果未生效，则需要执行FLUSH PRIVILEGES;语句。

2. MySQL提供了哪些权限

MySQL提供的权限列表如下所示（其中，All或者All privileges代表权限列表中除Grant option权限之外的所有权限）。

tbs@localhost:[mysql]>show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

在上表所示的权限列表中，Context字段显示了该权限的使用环境（或者叫权限的作用域）。根据Context字段内容的不同，权限分为如下三类。

• 管理权限
  用于管理MySQL服务器的操作。这些权限是全局性的，授权范围不能是特定的数据库或数据库对象（只能使用*.*方式授予，不能使用db.*或db.tb方式）。
• 数据库级别权限
  授权范围可以是某数据库或某数据库中的所有对象，也可以是所有数据库（可以使用*.*代表全局对象；也可以使用db.*代表某库下的所有对象）。
• 数据库对象级别权限
  授权范围可以为数据库中的特定对象、数据库内给定类型的对象，也可以是所有数据库（*.*代表全局对象，db.*代表某库下的所有对象，db.tb代表某库下的某对象）。

2.1 权限管理

• Create user
• Event
• Process
• Proxy
• Reload
• Replication client
• Replication slave
• Show databases
• Shutdown
• Super
• Create tablespace
• Usage
• Grant option

2.2 数据库级别权限

• Create
• Create routine
• Create temporary tables
• Drop
• Lock tables
• References

2.3 数据库对象级别权限

• Alter
• Alter routine
• Create view
• Delete
• Execute
• File
• Index
• Insert
• Select
• Show view
• Trigger
• Update

2.4 按照经验划分

通常，还可以根据使用经验按照如下方式划分。

2.4.1 开发权限

• Delete
• Insert
• Select
• Update
• Alter
• Create temporary tables
• Trigger
• Create view
• Show view
• Alter routine
• Create routine
• Execute
• Index
• Event

2.4.2 管理权限——表级别（这里把带表级别的管理命令都归类为表级别）

• Create
• File
• Drop
• Lock tables

2.4.3 管理权限——服务器级别

• Grant option
• Create tablespace
• Create user
• Process
• Proxy
• Reload
• Replication client
• Replication slave
• Show databases
• Shutdown
• Super
• Usage
• All[privileges]

下面我们逐一解释每个权限的作用。

• All或All privileges：除Grant option之外，代表其他所有权限。

• Alter：该权限用于使用ALTER TABLE语句来更改表的结构（除该权限之外，使用ALTER TABLE语句还需要有Create和Insert权限，使用ALTER TABLE RENAME语句需要有旧表上的Alter和Drop权限，新表上的Create和Insert权限）。

• Alter routine：该权限用于修改或删除存储过程或存储函数。

• Create：该权限用于创建库和表。

• Create routine：该权限用于创建存储过程或存储函数。

• Create tablespace：该权限用于创建、修改、删除表空间文件和日志组文件。

• Create temporary tables：该权限用于创建临时表。使用CREATE TEMPORARY
  TABLE语句创建临时表，一旦某会话创建临时表成功后，服务器不会在该表上执行权限
  检查（因为其他会话看不见此表，创建此表的会话一旦断开，临时表就会自动删除）。
  即，创建临时表的会话可以对该临时表执行任何操作，例如DROP TABLE、INSERT、
  UPDATE、SELECT等操作。

• Create user：该权限用于使用ALTER USER、CREATE USER、DROP USER、
  RENAME USER、REVOKE ALL PRIVILEGES语句。

• Create view：该权限用于使用CREATE VIEW语句。

• Delete：该权限用于从数据库表中删除数据记录。

• Drop：该权限用于删除现有库、表、视图等对象。另外，如果在分区表上使用ALTER TABLE … DROP PARTITION语句，则必须要有表的Drop权限，执行TRUNCATETABLE也需要有Drop权限（但要注意，如果将MySQL数据库的Drop权限授予用户，则该用户可以删除存储MySQL访问权限记录的数据库mysql）。

• Event：该权限用于创建、更改、删除或查看Event Scheduler事件。

• Execute：该权限用于执行存储过程或存储函数。

• File：该权限用于执行LOAD DATA INFILE和SELECT … INTO OUTFILE语句以及LOAD_FILE()函数来读写服务器主机上的文件。具有File权限的用户可以读取服务器主机上的任何可读文件或MySQL服务器可读文件。（即，用户可读取datadir目录中的任何文件），File权限还使用户能够在MySQL服务器有写入权限的任何目录下创建新文件。所以，作为安全保护措施，服务器不会覆盖现有文件（即，在执行导出数据到文本时，如果文件名重复，则导出语句无法成功执行）。在MySQL 5.7版本中，可以使用
  secure_file_priv系统变量限制File权限的读写目录。

• Grant option：该权限用于授予或回收其他用户或自己拥有的权限。

• Index：该权限用于创建或删除索引。Index权限适用于在已存在的表上使用
  CREATE INDEX语句，如果用户具有Create权限，则可以在CREATE TABLE语句中包含
  索引定义语句。

• Insert：该权限用于向表中插入数据记录行。对于ANALYZE TABLE、OPTIMIZE
  TABLE和REPAIR TABLE表维护语句也需要Insert权限。

• Lock tables：该权限用于使用LOCK TABLES语句对表显式加锁，持有表锁的用户
  对该表有读写权限，未持有表锁的用户对该表的读写访问会被阻塞。

• Process：该权限用于显示有关在服务器上执行的线程信息（即，关于会话正在执行的语句相关状态信息）。拥有该权限的用户在使用SHOW PROCESSLIST语句或mysqladmin processlist命令查看有关线程信息时，除可以看到自己的线程信息之外还可以查看到属于其他账号的线程信息。另外，使用SHOW ENGINE语句以及查看information_schema系统库中的相当一部分表也需要该权限。

• Proxy：该权限使用户能够模仿（伪装、代理）另一个用户。

• References：在创建外键约束时，该权限需要用户具有父表的References权限。

• Reload：该权限允许用户使用FLUSH语句。拥有该权限的用户还可以使用与FLUSH操作等效的mysqladmin子命令——flush-hosts、flush-logs、flush-privileges、flushstatus、flush-tables、flush-threads、refresh和reload。其中，reload子命令会通知服务器将权限表重新加载到内存中；flush-privileges子命令的作用与reload相同；refresh子命令会通知服务器关闭并重新打开日志文件且刷新所有表。其他flush-xxx子命令也会执行类似于刷新的功能，这些子命令刷新的对象更具体。例如，只想刷新日志文件，则使用flush-logs子命令。

• Replication client：该权限用于使用SHOW MASTER STATUS、SHOW SLAVE
  STATUS和SHOW BINARY LOGS语句。

• Replication slave：该权限用于从从库服务器连接到主库服务器并请求主库的binlog
  日志。如果没有此权限，从库将无法请求主库数据库变更的binlog日志。

• Select：该权限用于从数据库表中查询数据行记录。使用SELECT语句只有实际从表中检索行记录时才需要Select权限。但某些SELECT语句不需要访问表，并且可以在没有任何数据库权限的情况下执行。例如，使用SELECT语句拼接的常量表达式：SELECT 1 + 1; SELECT PI()* 2;。另外，使用UPDATE或DELETE语句，当使用WHERE子句指定了某字段的条件值时，也需要该字段的SELECT权限；否则，你会发现可以使用UPDATE不带WHERE子句更新全表，却不能使用WHERE语句指定更新某些行记录。对基表或视图使用EXPLAIN语句，也需要用户对表或视图具有该权限。

• Show databases：该权限用于执行SHOW DATABASE语句，对于没有此权限的用户，则只能看到其具有对应访问权限的数据库列表。如果服务器使用了–skip-show database选项启动，则没有该权限的用户即使对某库有其他访问权限，也不能使用SHOW DATABASES语句查看任何数据库列表（会报错：ERROR 1227 (42000): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation）。

• Show view：该权限用于执行SHOW CREATE VIEW语句。对视图使用EXPLAIN语句也需要此权限。

• Shutdown：该权限用于执行SHUTDOWN语句、mysqladmin shutdown命令和mysql_shutdown() C API函数。

• Super：该权限用于进行如下操作和服务器行为。

  修改全局系统配置变量需要此权限。对于某些系统变量，修改会话级别的系统配置变量也需要Super权限（如果修改会话级别的系统配置变量需要Super权限，在变量的解释文档中会进行说明，例如binlog_format、sql_log_bin和sql_log_off）。
  对全局事务特征的更改（START TRANSACTION语句）。
  从库服务器用于执行启动和停止复制的语句，包括组复制。
  从库服务器用于执行CHANGE MASTER TO和CHANGE REPLICATION FILTER
语句。
  执行PURGE BINARY LOGS和BINLOG语句。
  如果视图或存储程序定义了DEFINER属性，则拥有Super权限的用户就算不是该视图或存储程序的创建者，也仍然可以执行该视图或存储程序。
  执行CREATE SERVER、ALTER SERVER和DROP SERVER语句。
  执行mysqladmin debug命令。
  用于InnoDB key自旋。
  通过执行DES_ENCRYPT()函数启用读取DES密钥文件。
  执行用户自定义函数时启用版本令牌。
  超过了最大连接数之后，具有Super权限的账户还可以执行的操作有：
     使用KILL语句或mysqladmin kill命令来终止属于其他账户的线程（注意：无论是否拥有Super权限，用户总是可以kill自己的线程）。
     即使服务器总连接数达到max_connections系统变量定义的值，服务器也会接受来自具有Super权限的用户的一个额外连接。
     即使服务器启用了read_only系统变量，具有Super权限的用户也仍然可以执行数据更新，包括显式的操作更新和隐式的操作更新（账户管理语句GRANT和REVOKE等触发的表更新）。
     具有Super权限的用户连接服务器时，服务器不执行init_connect系统变量指定的内容。
     处于脱机模式（已启用offline_mode系统变量）的服务器不会中断具有Super权限的用户的连接，且仍然接受具有Super权限的用户的新连接请求。
  如果启用了二进制日志记录功能，则用户可能还需要Super权限才能创建或更改存储的功能。

• Trigger：该权限用于触发器的操作。用户必须拥有某表的该权限才能针对该表创建、删除、执行或查看其触发器。
• Update：该权限用于执行对数据库表中的数据行更新操作。
• Usage：该权限代表用户“无任何权限”。全局级别权限，拥有该权限的用户可以登录到数据库服务器中，但在默认配置下除能够执行部分show命令之外，其他任何数据变更和数据库查询操作都无法执行。
  提示：只向用户授予其需要的权限，不要授予额外的多余的权限，特别是管理权限，例如File、Grant
  option、Alter、Shutdown、Process、Super等。