访问权限控制系统|全方位认识 mysql 系统库

骑猪看日落 2021-11-17 00:14 317阅读 0赞

在上一期《权限系统表|全方位认识 mysql 系统库》中，我们针对mysql 系统库中的权限表做了一个简单的认识，本期我们将在上一期的基础上详细介绍MySQL 的访问权限控制系统，下面请跟随我们一起开始 mysql 系统库的系统学习之旅吧。

### **1、访问权限控制系统概述** ###

什么是访问权限控制系统？

*  MySQL的mysql 系统库提供了user、db、tables\_priv、columns\_priv、procs\_priv、proxies\_priv几张表用于存放不同权限范围的用户帐号相关的数据，这些表共同组成了MySQL 的访问权限系统。
 *  MySQL权限系统的主要功能是对从给定主机连接到MySQL Server的用户进行身份验证，并校验该用户在该Server中的数据库对象访问的权限（如SELECT，INSERT，UPDATE和DELETE），另外，还包括管理匿名用户访问和授予特定的MySQL权限的功能（如执行 LOAD DATA INFILE 语句和管理操作权限等）。

MySQL访问权限系统的用户界面由几个SQL语句组成：如CREATE USER、GRANT和REVOKE。

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

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

root@localhost : mysql 01:03:04> show grants for test_a@'localhost';
    +---------------------------------------------+
    | Grants for test_a@localhost                 |
    +---------------------------------------------+
    | GRANT SELECT ON *.* TO 'test_a'@'localhost' |
    +---------------------------------------------+
    1 row in set (0.00 sec)
    
    root@localhost : mysql 01:03:22> show grants for test_a@'%';
    +---------------------------------------------+
    | Grants for test_a@%                         |
    +---------------------------------------------+
    | GRANT SELECT, INSERT ON *.* TO 'test_a'@'%' |
    +---------------------------------------------+
    1 row in set (0.00 sec)

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

*  阶段1：Server根据身份标识(主机名+用户名组成的帐号名)在MySQL 的访问权限控制表中查询相关信息，以确定需要接受或拒绝该用户的连接(没有查询到就拒绝连接)，如果查询到了用户记录，则校验用户提供的帐号密码是否正确，如果密码不正确则拒绝连接，这一阶段报错信息类似为：ERROR 1045 (28000): Access denied for user 'test\_a'@'localhost' (using password: YES)
 *  阶段2：用户连接成功之后，Server会检查用户访问请求中的每个声明来确定是否有足够的权限来执行。例如：如果尝试从数据库的表中查询数据行或从数据库中删除表，Server将验证该用户否具有该表的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权限之外的所有权限）：

以上权限列表中，Context字段显示了该权限的一个使用环境(或者说是权限的作用域)，根据Context的不同，分为如下三类：

*  管理权限：用于管理MySQL Server的操作。这些权限是全局性的，不能授予给特定的数据库或数据库对象（只能使用*.*方式授予） 。
    
    \* Create user   
    \* Event   
    \* Process   
    \* Proxy   
    \* Reload   
    \* Replication client   
    \* Replication slave   
    \* Show databases   
    \* Shutdown   
    \* Super   
    \* Create tablespace   
    \* Usage   
    \* Grant option
 *  数据库级别权限：用于授予某数据库及其数据库中的所有对象的权限。这些权限可以被授予特定的数据库，也可以授予所有数据库(可以使用*.*，代表全局对象，也可以使用db.\*，代表某库下的所有对象) 。
    
    \* Create   
    \* Create routine   
    \* Create temporary tables   
    \* Drop   
    \* Lock tables   
    \* References
 *  数据库对象级别权限：可为数据库中的特定对象、数据库内给定类型的对象授予权限，也可以授予所有数据库(*.*，代表全局对象，db.\*代表某库下的所有对象，db.tb代表某库下某对象) 。
    
    \* Alter   
    \* Alter routine   
    \* Create view   
    \* Delete   
    \* Execute   
    \* File   
    \* Index   
    \* Insert   
    \* Select   
    \* Show view   
    \* Trigger   
    \* Update

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

*  开发权限
    
    \* DELETE   
    \* INSERT   
    \* SELECT   
    \* UPDATE   
    \* ALTER   
    \* CREATE TEMPORARY TABLES   
    \* TRIGGER   
    \* CREATE VIEW   
    \* SHOW VIEW   
    \* ALTER ROUTINE   
    \* CREATE ROUTINE   
    \* EXECUTE   
    \* INDEX   
    \* EVENT
 *  管理权限-表级别(这里把带表级别的管理命令都归类为表级别)   
    \* CREATE   
    \* FILE   
    \* DROP   
    \* LOCK TABLES
 *  管理权限-server级别   
    \* 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语句创建临时表，一旦某会话创建临时表成功后，Server不会在该表上执行进一步的权限检查。即，创建该临时表的会话可以该临时表执行任何操作，例如：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权限，要执行TRUNCATE TABLE也需要DROP权限(但要注意，如果将MySQL数据库的DROP权限授予给用户，则该用户可以删除存储MySQL访问权记录的数据库mysql）。
 *  EVENT：该选项用于创建、更改、删除或查看Event Scheduler事件。
 *  EXECUTE：该权限用于执行存储过过程或函数。
 *  FILE：该权限用于执行LOAD DATA INFILE和SELECT ... INTO OUTFILE语句以及LOAD\_FILE（）函数来读取和写入Server主机上的文件。具有FILE权限的用户可以读取Server主机上任何可读或MySQL Server可读的文件。（即，用户可读取datadir目录中的任何文件），FILE权限还使用户能够在MySQL Server有写入权限的任何目录下创建新文件。所以，作为安全保护措施，Server不会覆盖现有文件(即执行导出数据到文本时，如果文件名重叠则导出语句无法成执行)。在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：该权限用于显示有关在Server内执行的线程信息（即关于会话正在执行的语句相关状态信息）。拥有该权限的用户在使用SHOW PROCESSLIST语句或mysqladmin processlist命令查看有关线程信息时除了自己的线程信息之外还可以查看到属于其他帐号的线程信息。另外，使用SHOW ENGINE语句以及查看information\_schema 数据字典库中的相当一部分表也需要该权限。
 *  PROXY：该权限使用户能够模仿另一个用户。
 *  REFERENCES：该权限在创建外键约束时，需要用户具有父表的REFERENCES权限。
 *  RELOAD：该权限允许用户使用FLUSH语句。拥有该权限的用户还可以使用与FLUSH操作等效的mysqladmin子命令：flush-hosts，flush-logs，flush-privileges，flush-status，flush-tables，flush-threads，refresh和reload  
    \* mysqladmin的reload子命令会通知Server将权限表重新加载到内存中。flush-privileges作用与reload子命令作用相同。refresh子命令会通知Server关闭并重新打开日志文件并刷新所有表。类似地，其他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语句，若没有此权限的帐户，则只能看到他们具有对应访问权限的数据库列表，如果Server使用了--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：该权限用于如下这些操作和Server行为：   
    \* 修改全局系统配置变量需要此权限。对于某些系统变量，修改会话级别的系统配置变量也需要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的帐户还可以的操作 。  
    \* 1)、使用KILL语句或mysqladmin kill命令来终止属于其他帐户的线程。（注意：无论是否拥有SUPER权限，用户总是可以kill自己的线程）   
    \* 2)、即使Server总连接数达到max\_connections系统变量定义的值，Server 也会接受来自具有SUPER权限的用户一个额外的连接 。  
    \* 3)、即使Server启用了read\_only系统变量，具有SUPER权限的用户仍然可以执行数据更新。另外，还有帐户管理语句GRANT和REVOKE等 。  
    \* 4)、SUPER客户端连接Server时，Server不执行init\_connect系统变量指定的内容 。  
    \* 5)、处于脱机模式（已启用offline\_mode系统变量）的Server不会中断具有SUPER权限用户的连接，且仍然接收具有SUPER权限用户的新连接请求 。  
    \* 如果启用了二进制日志记录功能，则用户可能还需要SUPER权限才能创建或更改存储的功能。
 *  TRIGGER：该权限用于触发器的操作。您必须拥有某表的该权限才能针对该表创建、删除、执行或查看该表的触发器。
 *  UPDATE：该权限用于执行对数据库表中的数据行更新操作。
 *  USAGE：该权限代表用户“无任何权限”。全局级别权限，拥有该权限的用户可以登录到数据库Server中，但默认配置下除了能够执行部分show命令之外，其他任何数据变更和数据库查询的操作都无法执行。
 *  向用户只授予用户需要的权限，不要授予额外的多余的，特别是管理权限，如下：   
    \* FILE：该权限用于将任何文件读入数据库表中，MySQL Server可以在Server主机上读取任何文件。包括Server数据目录中所有可读文件。然后可以使用SELECT访问该导入数据的表，将其读取表中的内容返回给客户端 。  
    \* GRANT OPTION：该权限用于执行将权限授予其他用户 。  
    \* ALTER：该权限用于修改表定义，重命名表等操作 。  
    \* SHUTDOWN：该权限用于终止Server服务器，如果被滥用可被用于关闭Server来达到拒绝服务的目的 。  
    \* PROCESS：该权限可用于查看当前正在执行的语句的纯文本，包括设置或更改密码的语句文本 。  
    \* SUPER：该权限可用于终止其他用户会话或更改服务器的运行方式。详见上述SUPER解释项。

**3、 帐号命名规则**

MySQL的帐户由用户名和主机名两部分组成(例如：user\_name@host\_name)。采用这种方式Server就可以区分相同用户来自不同主机的连接，本小节将介绍如何编写有效的帐户名称(包括特殊值和通配符规则)，对于使用SQL语句CREATE USER、GRANT和SET PASSWORD来操作用户的，都遵循以下规则：

*  帐户名称构成语法：'user\_name'@'host\_name'
 *  仅由用户名组成的帐户名相当于'user\_name'@'％'。例如：'me'相当于'me'@'％'
 *  如果用户名称和主机名称的字符串是合法的非引用标识符(即，不包含sql中的关键字或命令字)，则不需要使用反撇进行引用。如果用户名或主机名的字符串中包含特殊字符(如空格或者- 符号)或者通配符(如：点号或者%号)，则需要使用单引号或者双引号引起来，例如：'test-user'@'%.com'(注意:一旦使用引号,注意'me@localhost'和'me'@'localhost'的含义是不同的，'me@localhost' 实际上在使用的时候，MySQL是解析为'me@localhost'@'%' ，而不是'me'@'localhost'），如果用户名或主机名不包含引用字符或特殊字符等，可不需要使用分撇和引号，但为了规范起见，建议至少对主机名和用户名使用引号，例如：'me'@'localhost'
 *  对CURRENT\_USER关键字和CURRENT\_USER()函数在查询语句中的效果相同，例如：select current\_user; 和 select current\_user(); 两个语句的查询结果相同，都是返回当前连接的帐号名。

MySQL 中的帐号名在mysql系统字典库中的权限表user 表中存储时，会将user\_name和host\_name分开存储在user和host两列中：

*  user表中存储的帐号信息中，每个帐号包含一行记录。user和host列存储帐号对应的用户名和主机名，其他列存储了帐号对应的权限和帐号的其他属性信息。
 *  其他权限表保存着帐户对实例中的库级别，表级别，列级别等权限信息。这些表与user表一样，也使用user和host列来分别存储帐号对应用户名和主机名。这些表保存着不同权限作用域的权限信息等(例如：db、columns\_priv、procs\_priv、proxies\_priv、tables\_priv，但这些表中并不保存密码信息)。
 *  为了进行帐号的访问检查，用户名严格区分大小写，但主机名不区分大小写。

关于帐号用户名和主机名中某些特殊值或通配符约定，如下：

\* 主机名或IP地址值中都允许使用通配符％和\_。这些与LIKE运算符中的通配符含义相同。例如，“％”表示匹配任意主机名，而“％.mysql.com”表示匹配mysql.com域中的任何主机，'192.51.100.％'表示匹配C类私有网络192.51.100中的任意主机，由于主机名允许使用IP+通配符值（例如：“192.51.100.％”匹配192.51.100子网上的任意主机），为了阻止有人通过192.51.100.somewhere.com 格式的主机名字符串构造来尝试扫描存活主机，MySQL不会在以数字和点开头的主机名上执行匹配动作。例如：如果主机名部分为1.2.example.com，则直接被MySQL忽略， IP地址只能使用通配符组合，而不能与主机名进行组合，否则也会被忽略 。  
\* 对于指定为IPv4地址的主机名，可以结合子网掩码来控制子网IP数量(注意：子网掩码不使用IPV6)，格式：host\_ip/netmask。例如：CREATE USER 'david'@'192.51.100.0/255.255.255.0';，表示用户名为david，主机名为192.51.100.0子网下的任意主机，满足此条件的客户端主机IP地址范围从192.51.100.0到192.51.100.255。

*  默认情况下，user表中保存着一些匿名帐号，所以，默认情况下MySQL允许匿名帐号连接(即，user\_name为空的帐号，但使用匿名帐号需要使用引号，如：''@ localhost')。
 *  帐户名称中的host\_name部分可以使用多种形式，并且允许使用通配符，如下： 
    
    \* 主机名字符串可以是域名或操作系统主机名（需要DNS解析服务），也可以是IP地址（IPv4或IPv6）。对于域名，'localhost'表示本地主机，“127.0.0.1”表示IPv4的环回接口，':: 1'表示IPv6的环回接口 。
 *  对于MySQL帐号名的主机名部分为IP时，子网掩码ABC网络都支持，例如：
 *  192.0.0.0/255.0.0.0：掩码8位，表示192 A类网络上的任何主机。
 *  192.51.100.0/255.255.0.0：掩码16位，表示192.51 B类网络上的任何主机。
 *  192.51.100.0/255.255.255.0：掩码24位，表示192.51.100 C类网络上的任何主机。
 *  192.51.100.1：不带掩码，表示仅匹配具有此特定IP地址的主机。

MySQL Server使用DNS解析时，需要注意以下问题：

*  假设本地网络上的主机具有host1.example.com的完全限定名称（DNS地址）。如果DNS将此主机解析为host1.example.com返回，则在MySQL帐号名的主机名部分也需要使用host1.example.com，如果DNS解析仅返回为host1，则在MySQL帐号名的主机名部分也需要使用host1，否则会被拒绝连接。
 *  如果DNS返回的是IP地址192.51.100.2，那么它将优先进行IP地址的精确匹配，然后匹配对应网络的通配符，但不匹配非法的IP地址(例如：192.51.100.2)或子网(例如：192.51.100.％）。

**4、MySQL 帐号访问控制两阶段**

4.1. 第一阶段(帐号和密码认证)

当您尝试连接MySQL Server时，Server根据如下条件来决定是否需要接受或拒绝连接：

*  您的身份信息（帐号名，由user\_name@host\_name格式组成）以及密码信息是否可以验证通过。
 *  你的帐户是否处于锁定状态。

当MySQL Server接收到一个新的连接请求时，Server首先检查用户凭证(帐号+密码)，然后检查帐户的锁定状态。任意一个步骤检查失败则拒绝连接发访问。如果两个步骤都通过检查，则进入第2阶段并等待执行请求。

*  MySQL Server 使用user表中的Host、User、authentication\_string三个列存储的用户凭证信息来执行凭证检查。用户的锁定状态记录在user表的account\_locked列中。如下：

root@localhost : (none) 12:43:38> select     host,user,authentication_string,account_locked from mysql.user;
    +-----------+---------------+----------------------------------------    ---+----------------+
    | host      | user          | authentication_string                         | account_locked |
    +-----------+---------------+----------------------------------------    ---+----------------+
    | localhost | root          |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
    | localhost | mysql.session |     *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
    | localhost | mysql.sys     |     *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
    | %         | admin         |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
    | %         | repl          |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
    | %         | qbench        |     *1966B10B87AA6A1F8E1215A1C81DDD5FBBA6B0D0 | N              |
    | %         | program       |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
    +-----------+---------------+----------------------------------------    ---+----------------+
    7 rows in set (0.00 sec)
    
    # 帐户锁定状态可以通过ALTER USER语句进行更改
    ALTER USER [IF EXISTS]
        user [auth_option] [, user [auth_option]] ...
        [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
        [WITH resource_option [resource_option] ...]
        [password_option | lock_option] ...
    ......
    lock_option: {
        ACCOUNT LOCK
      | ACCOUNT UNLOCK
    }

上文中提到过，用户的身份信息基于两部分组成（user\_name和host\_name），对于身份信息的两个组成部分，有如下认证规则：

*  如果用户名列值不为空，则用户尝试连接时就必须传入用户名字符串，且必须完全匹配，如果用户名列值为空，则为空的列值在进行认证时，可以匹配任何用户名（包括用户名称为空和不为空的，为空的用户名被称为匿名用户）。在帐号访问控制的第一阶段匹配到匿名用户的，在第二阶段认证仍然会使用匿名用户。
 *  如果密码信息列authentication\_string列为空，则意味着用户尝试连接Server时不需要输入密码(注意：密码信息列与帐号名称列不同，密码信息列为空时，只能匹配空串的密码，不能匹配任意密码)。如果Server使用了认证插件对客户端进行身份验证，则插件实现的身份验证方法中可能会，也可能不会使用authentication\_string密码信息列中的密码字符串。甚至可能还会使用外部密码认证服务器对MySQL Server进行身份验证。
 *  user表中非空的authentication\_string列值表示加密过的密码字符串(hash加密)。MySQL在authentication\_string列中不存储明文格式的密码（使用帐户认证插件实现的密码散列方法加密）。在连接认证过程中使用加密的密码来检查密码是否正确。从MySQL的角度来看，加密的密码才是真正的密码，所以，非授权情况下，不要随意让别人知道你的密码信息，特别是对mysql库的访问权限。

下表列举了一些user\_name和host\_name常用的组合：

*  'fred'@'h1.example.net'：表示使用fred用户从h1.example.net主机连接。
 *  ''@'h1.example.net'：表示任何用户从h1.example.net主机连接。
 *  'fred'@'％'：表示fredon过户从任何主机连接。
 *  ''@'％'：表示任何用户从任何主机连接。
 *  'fred'%'％.example.net'：表示fred用户从example.net域中的任何主机连接。
 *  'fred'@'x.example.％'：表示fred用户从x.example.net，x.example.com，x.example.edu任意域名后缀的主机连接(但后缀%限制可能不生效)。
 *  'fred'@'192.51.100.177'：表示fred用户从IP地址为192.51.100.177的主机连接。
 *  'fred'@'192.51.100.％'：表示fred用户从192.51.100 C类子网中的任何主机连接。
 *  'fred'@'192.51.100.0/255.255.255.0'：表示fred用户从192.51.100 C类子网中的任何主机连接。

客户端传入Server中的身份标识(主机名和用户名)可能与用户表中的多个行记录匹配成功。当一个用户尝试连接Server时，如果在Server的user表中匹配到多个行记录的身份认证信息，则Server必须确定要能够确定使用哪一行记录进行许可（不同的身份信息行记录可能对应着不同的权限）：

*  Server 只要将user表读入内存，即就会在内存中对用户信息进行排序。
 *  当客户端尝试连接时，Server 会按照内存中排好序的内容依次进行匹配。
 *  Server 使用与客户端主机名和用户名相匹配的第一行进行授权。

Server 使用的排序规则中，先排序主机列值（越精确的值越靠前，字符串主机名和IP地址是最具体的，另外，IP地址的精确性不会受到掩码的影响，例如：192.51.100.13和192.51.100.0/255.255.255.0被视为具有相同的精确度。通配符'％'表示“任何主机”，被视为精确度较差的主机名。空字符串“'也意味着”任何主机“，但精确度比'％'更差，所以排序在'%'之后）。然后再按照用户列值进行排序（排序规则跟主机列值类似），host和user两列的排序规则有点类似与多列索引中的排序规则。

示例一：假设用户表中记录的内容如下所示：

+-----------+----------+-
    | Host      | User     | ...
    +-----------+----------+-
    | %         | root     | ...
    | %         | jeffrey  | ...
    | localhost | root     | ...
    | localhost |          | ...
    +-----------+----------+-
    
    # 当Server将表中的内容读入内存时，会使用刚刚描述的规则在内存中对用户信息行进行排序。排序后的结果如下所示：
    +-----------+----------+-
    | Host      | User     | ...
    +-----------+----------+-
    | localhost | root     | ...
    | localhost |          | ...
    | %         | jeffrey  | ...
    | %         | root     | ...
    +-----------+----------+-
    
    # 当客户端尝试连接时，Server 会查看在内存中已排好序的用户身份认证信息，并使用第一个匹配项进行许可。如：对于用户jeffrey的localhost的主机连接，首先，精确匹配localhost主机列，有两列匹配，然后再匹配用户名列，也有两列(空值和jeffrey)，两列交集最终确定匹配行为：host=localhost,user=''，即''@'localhost'身份

示例二：假设用户表中记录的信息如下所示：

+----------------+----------+-
    | Host           | User     | ...
    +----------------+----------+-
    | %              | jeffrey  | ...
    | h1.example.net |          | ...
    +----------------+----------+-
    
    # 在内存中排序之后的内容如下所示：
    +----------------+----------+-
    | Host           | User     | ...
    +----------------+----------+-
    | h1.example.net |          | ...
    | %              | jeffrey  | ...
    +----------------+----------+-
    
    # 来自h1.example.net主机的jeffrey用户的连接与第一行记录匹配成功，而来自任何主机的jeffrey用户的连接与第二行匹配成功

注意：

*  通过上述示例可知，当存在匿名用户的时候，如果您能够成功连接到服务器，但您的权限可能不符合您的期望，那么表示您此时可能正在通过其他帐户进行身份验证。可以使用select current\_user();或者select current\_user;语句来检查你当前登录成功的帐号身份信息是什么？以便确定是否正确对应了权限信息，如下：

mysql> SELECT CURRENT_USER();
    +----------------+
    | CURRENT_USER() |
    +----------------+
    | @localhost     |
    +----------------+

4.2. 第二阶段(权限检查)

当客户端与MySQL Server 建立连接之后，Server 进入权限访问控制的第2阶段。在第2阶段中，客户端发送给服务端的每个请求，服务端都会检查请求操作的类型，然后检查是否有足够的访问权限来执行请求操作。该检查工作依赖于mysql schema下的user、db、tables\_priv、columns\_priv、procs\_priv、proxies\_priv权限表中存放的权限信息。

user：该表中的权限作用范围是全局的，所以该表中相应权限类型列值为'Y'时，就表示表示对数据库实例中的所有数据库表都有该权限，所以，在大多数时候，我们需要根据具体的业务环境需求来给定需要访问的数据库对应的权限，而不是投方便直接给所有库所有表的权限（关于如何给定权限，请参考上文提到的权限分类）。

*  user列为空时表示匿名用户，非空值必须匹配字符串字面本身表示的用户名，用户名不能使用通配符。
 *  host列值不允许为空(虽然授权语句和创建用户的语句可以只写用户名而不写主机名，但实际上存储在表中时会被转换为%)，但可以使用通配符（%和\_：%表示任意主机，\_表示主机名中的任意一个字符），可以使用like关键字来配合通配符进行匹配。

db：该表中的权限作用范围是数据库级别，对应数据库内的所有对象：

*  user列和host列的表现形式要求与user表相同。
 *  PS：与user表类似，Server会在启动时就将db表中的内容读入内存，并在内存中进行排序，根据Host，Db和User 三列对db表中的数据进行排序。排序会将最具体的值放在最前面，将最不具体的值放到最后，当Server进行用户匹配查找时，会使用第一个匹配行进行许可。

tables\_priv，columns\_priv和procs\_priv：这三张表中记录着表级别权限、列级别权限、线程级别权限：

*  user列和host列的表现形式要求与user表相同。
 *  Db，Table\_name，Column\_name和Routine\_name列不能包含通配符或为空值。
 *  PS：与user表类似，Server会在启动时就将db表中的内容读入内存，并在内存中进行排序，根据Host，Db和User三列对tables\_priv，columns\_priv和procs\_priv表数据进行排序。

当一个客户端连接在进行第二阶段权限验证时，首先检查user表，如果所检查权限是user表特有的(其他权限表没有的权限类别)，则user表中允许执行则Server 授予客户端访问权限，否则直接拒绝而不会继续检查其他权限表(因为其他权限表不具备该权限列表，无需检查)，如果所检查权限类别除了在user表之外，在其他权限表中也具有该权限类别(例如：DML权限)，则即时在user表中不允许(毕竟user表中的权限是表示是否具有全局权限的意思)，也会继续往下检查db表，然后再检查tables\_priv表，以此类推。

\* grant语句在授予用户权限时，授予库级别权限时，数据库不需要事先存在即可授权成功，但如果是对表级别对象授权，则表需要事先存在，否则授权失败，提示表不存在的报错信息 。  
\* 对于存储程序的请求操作，Server 使用procs\_priv表检查权限，而不是tables\_priv和columns\_priv表。

上文中提及的权限检查逻辑，可以使用如下布尔型的伪代码来表示：

*  PS： 
    
    \* 如果某客户端在user表中类似DML权限不足，而在其他db、tables\_priv、columns\_priv表中都没有找到对应的user,host列记录（则表示用户在所有权限表中都没有对应操作类型的权限），则客户端访问被拒绝，返回无访问权限提示信息 。

global privileges
    OR (database privileges AND host privileges)
    OR table privileges
    OR column privileges
    OR routine privileges

PS：某些类型的一个语句可以需要请求多个类型的权限，例如：INSERT...SELECT，该语句需要请求INSERT和SELECT两个权限，而这两个权限可能在授予用户的时候授予范围不同，假如INSERT授予的是全局范围权限，而SELECT是授予的db级别的权限，此时，INSERT权限是保存在user表中的，SELECT权限是保存在db表中的，那么也就是说，这个时候Server需要分两次查询之后将两个表中记录的权限信息进行组合，然后再用于判断用户是否具INSERT...SELECT语句的访问请求权限，并返回相应的请求结果。如果任意一个权限不满足，则拒绝访问。

### **5、权限变更的影响** ###

当mysqld启动时，将读取所有权限表的内容到内存中。后续所有用户对MySQL Server的访问的权鉴都是基于内存中保存的这些值进行。

*  如果在MySQL Server运行期间使用帐户管理语句（如GRANT，REVOKE，SET PASSWORD或RENAME USER）间接修改了权限表，则Server会立即将权限表中的内容重新加载到内存中。
 *  如果在MySQL Server运行期间使用INSERT，UPDATE或DELETE等语句直接修改权限表，那么对权限表的更改不会立即生效，除非重新启动Server或使用flush privileges;语句或者mysqladmin flush-privileges|reload等命令来重新加载权限表。

对于权限表的重载，需要注意如下事项：

*  对于表和列级权限，修改并重载权限表之后，对于已经建立的客户端连接，会在对表、列的下一个请求中生效。对于新建连接，第一个请求即生效。
 *  对于库级别权限，修改并重载权限表之后，对于已经建立的客户端连接，会在下一次使用use db\_name;语句时生效。对于新建连接，第一个请求即生效 。
    
    \* PS：如果回收了某用户的某库的权限，但客户端是已经建立连接的且客户端当前默认库正好是已回收权限的库，则如果客户端不使用use db\_name;语句切换默认库，对于该客户端来讲可能无法感知到库级别权限发生了修改。
 *  对于全局权限和密码的修改，不影响已建立连接的客户端，只针对重连或新创建的客户端连接生效。

如果Server 启动时使用了--skip-grant-tables选项，则Server不会读权限表，也不会进行任何访问权限控制，这个时候任何人都可以免密码登录数据库并可以做任何事情，这种情况除非维护时间窗口，否则禁止使用，在这种情况下，如果要重新加载权限表，无需重新启动，只需要执行flush privileges;语句即可。

### **6、MySQL 常见连接问题** ###

客户端无法连接服务器的问题

*  服务端未启动，可以通过检查服务端进程是否存在来排除(ps aux |grep mysqld，如果未启动则尝试拉起，如果启动失败则检查错误日志排查原因)，通常报错信息类似如下：
    
     \* TCP/IP方式连接：ERROR 2003: Can't connect to MySQL server on 'host\_name' (111)
    
     \* socket方式连接：ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
 *  客户端连错端口，可以通过检查服务器进程运行的端口(netstat -ln | grep mysqld)，找到正确的端口并在客户端指定连接该端口可以解决。
 *  服务器启用了--skip-networking选项或者--bind-address = 127.0.0.1选项时，它将仅在本地环回接口上侦听TCP/IP连接，并且不会接受远程连接。去除这些选项并重启进程可以解决。
 *  服务端防火墙未打开MySQL Server的端口访问权限，关闭防火墙或者允许MySQL Server的服务端口对外提供服务可以解决。
 *  没有使用正确的帐号或者密码连接服务器，通常报错信息类似：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 *  如果你的数据库是第一次初始化，且使用了命令mysqld --initialize-secure来初始化，则会为root用户生成一个随机密码字符串，在MySQL Server启动之后需要在error log中搜索password关键字，以找到随机密码字符串进行登录(使用该命令初始化会产生一个随机密码，如果你不需要该随机密码，则可以使用mysqld --initialize-insecure命令来初始化数据库)，否则也会报错：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 *  如果你升级了服务端到最新版本，而客户端没有做相应的升级，则可能报认证协议不支持的错误(最好的办法是升级客户端版本，不建议去修改密码认证插件)：Client does not support authentication protocol requested by server; consider upgrading MySQL client
 *  服务端达到了最大用户连接数参数限制，此时使用具有super权限的管理员帐号登录数据库，修改最大连接数。
 *  服务端达到了最大错误连接数参数限制，可能反复尝试连接的某些客户端被拒绝连接(例如使用错误的帐号或密码反复尝试多次，达到了最大错误连接数)，此时，使用管理员帐号从其他主机登录数据库执行flush hosts;语句刷新主机缓存信息，或者修改最大错误连接数参数。

PS：MySQL 访问权限系统有如下限制：

*  不能明确拒绝给定用户访问，只能明确地允许给定用户的访问，例如：使用了正确的帐号和密码，且从被授予访问的主机上访问数据库。
 *  不能单独授予用户只能创建或删除数据库中的表，而不能创建或删除数据库本身（指定了某用户对某表的 create和drop权限之后，用户就能够创建和删除该表所在的库）。
 *  帐号的密码在Server中的作用域是全局的。不能使用密码来与特定对象的访问权限挂钩（如数据库、表或存储过程与函数等）。

本期内容就介绍到这里，本期内容参考链接如下：

*  https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html
 *  https://dev.mysql.com/doc/refman/5.7/en/user-account-management.html
 *  https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html

**作者简介**

**罗小波·沃趣科技高级数据库技术专家**

IT从业多年，主要负责MySQL 产品的数据库支撑与售后二线支撑。曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写，熟悉MySQL体系结构，Innodb存储引擎，喜好专研开源技术，多次在公开场合做过线下线上数据库专题分享，发表过多篇数据库相关的研究文章。