13.4 MySQL账号访问控制两阶段
13.4.1 第一阶段（账号和密码认证）
当用户尝试连接MySQL服务器时，服务器根据如下条件来决定是否接受或拒绝连接。
● 用户的身份信息（账号名称，由user_name@host_name格式组成）以及密码信息是否可以验证通过。
● 用户的账号是否处于锁定状态。
当MySQL服务器接收到一个新的连接请求时，服务器首先检查用户凭证（账号+密码），然后检查账号的锁定状态。任意一个步骤检查失败，都会拒绝连接。如果两个步骤都通过检查，则进入第二阶段并等待执行请求。
MySQL服务器使用user表中的Host、User、authentication_string三个字段存储的用户凭证信息来执行凭证检查。用户账号的锁定状态记录在user表的account_locked字段中，如下所示。
mysql> 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两部分组成，对于这两个组成部分有如下认证规则。
● 如果用户名字段即User字段值不为空，则用户尝试连接时就必须传入用户名字符串，且必须完全匹配；如果用户名字段值为空，则在进行认证时可以匹配任意用户名（包括为空和不为空的用户名，为空的用户名被称为匿名用户）。在账号访问控制的第一阶段匹配到匿名用户的，在第二阶段认证时仍然会使用匿名用户。
● 如果密码信息字段即authentication_string字段值为空，则意味着用户尝试连接服务器时不需要输入密码（注意：密码信息字段与用户名字段不同，当密码信息字段值为空时，只能匹配空串的密码，不能匹配任意密码）。如果服务器使用了认证插件对客户端进行身份验证，则在插件实现的身份验证方法中可能会也可能不会使用authentication_string字段中的密码字符串，甚至可能还会使用外部密码认证服务器结合MySQL服务器进行身份验证。
● 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'@'%'：表示fred用户从任意主机连接。
● ''@'%'：表示任意用户从任意主机连接。
● '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.%'。
客户端传入服务器中的身份标识（主机名和用户名）可能与user表中的多个行记录匹配成功。当客户端尝试连接服务器时，如果在服务器的user表中匹配到多个行记录的身份认证信息，则服务器必须确定要使用哪一行记录进行许可（不同的身份信息行记录可能对应着不同的权限）。
● 服务器只要将user表读入内存，就会在内存中对用户信息进行排序。
● 当客户端尝试连接时，服务器会按照内存中排好序的内容依次进行匹配。
● 服务器使用与客户端主机名和用户名相匹配的第一行记录进行授权。
在服务器使用的排序规则中，先排序主机名字段值（越精确的值越靠前，字符串主机名和IP地址是最具体的。另外，IP地址的精确度不会受到掩码的影响，例如：192.51.100.13和192.51.100.0/255.255.255.0被视为具有相同的精确度。通配符“%”表示“任意主机”，被视为精确度较差的主机名。空字符串也意味着“任意主机”，但精确度比“%”更差，所以排在“%”之后），然后按照用户名字段值进行排序（排序规则与主机名字段值的排序规则类似）。主机名和用户名两个字段值的排序规则有点类似于多列索引中的排序规则。
示例一：用户表中记录的内容如下。
+--------------+-------------+- | Host | User | ... +--------------+-------------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +--------------+-------------+- # 当服务器将表中的内容读入内存时，会使用刚刚描述的规则在内存中对用户身份认证信息进行排序。排序后的结果如下 +--------------+-------------+- | Host | User | ... +--------------+-------------+- | localhost | root | ... | localhost | | ... | % | jeffrey | ... | % | root | ... +--------------+-------------+- # 当客户端尝试连接时，服务器会查看在内存中已排好序的用户身份认证信息，并使用第一个匹配行进行许可。如：对于用户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 |
+----------------+
13.4.2 第二阶段（权限检查）
当客户端与MySQL服务器建立连接之后，服务器进入账号访问控制的第二阶段。在第二阶段中，客户端发送给服务器的每个请求，服务器都会检查请求操作的类型，然后检查是否有足够的访问权限来执行请求操作。该检查工作依赖于mysql库下的user、db、tables_priv、columns_priv、procs_priv、proxies_priv权限表中存放的权限信息。
user表中的权限作用范围是全局性的，当该表中的相应权限类型字段值为’Y’时，就表示对数据库实例中的所有数据库表都有该权限。所以，在大多数时候，我们需要根据具体的业务环境需求来为需要访问的数据库授予对应的权限，而不是图方便直接为所有库所有表授予所有权限（关于如何授予权限，请参考上文中提到的权限分类内容）。
● 当User字段值为空时表示匿名用户，非空值必须匹配字符串字面本身表示的用户名，用户名不能使用通配符。
● Host字段值不允许为空（虽然授权语句和创建用户的语句可以只写用户名而不写主机名，但实际上存储在表中时会被转换为“%”），但可以使用通配符（“%”和“_”，“%”表示任意主机，“_”表示主机名中的任意一个字符），并且可以使用LIKE关键字来配合通配符进行匹配。
db表中的权限作用范围是数据库级别的，对应数据库内的所有对象。
● User字段和Host字段的表现形式要求与user表相同。
● 与user表类似，服务器会在启动时就将db表中的内容读入内存，并在内存中根据Host、Db和User三个字段对db表中的数据进行排序。排序会将最具体的值放在最前面，将最不具体的值放到最后，当服务器进行用户匹配查找时，会使用第一个匹配行进行许可。
tables_priv、columns_priv和procs_priv这三个表中记录着表级别权限、字段级别权限和存储程序权限。
● User字段和Host字段的表现形式要求与user表相同。
● Db、Table_name、Column_name和Routine_name字段不能包含通配符或为空值。
● 与user表类似，服务器会在启动时就将这三个表的内容读入内存，并在内存中根据Host、Db和User三个字段对tables_priv、columns_priv和procs_priv表数据进行排序。
当一个客户端连接在进行第二阶段权限检查时，首先会检查user表，如果所检查权限是user表特有的（在其他权限表中没有该权限），且在user表中该权限记录为Yes，则服务器授予客户端访问权限，否则直接拒绝而不会继续检查其他权限表（因为在其他权限表中没有该权限，无须检查）；如果所检查权限除在user表中有之外，在其他权限表中也有（例如：DML操作权限），则即使在user表中不允许（毕竟user表中的权限是全局性的），也会继续往下检查db表，然后检查tables_priv表，依此类推。
提示：
● 如果一个客户端在user表中类似的DML操作权限不足，而在db、tables_priv、columns_priv表中都没有找到对应的User、Host字段记录（表示在所有权限表中都没有对应操作类型的权限），则客户端访问被拒绝，返回无访问权限提示信息。
● 在使用GRANT语句授予用户库级别权限时，数据库不需要事先存在即可授权成功；但如果是授予表级别权限，则表需要事先存在，否则授权失败，提示表不存在的报错信息。
● 对于存储程序的请求操作，服务器使用procs_priv表检查权限，而不是tables_priv表和columns_priv表。
上文中提及的权限检查逻辑，可以使用如下布尔型的伪代码来表示。
global privileges OR（database privileges AND host privileges） OR table privileges OR column privileges OR routine privileges
提示：用户可以根据需要使用某些类型的语句请求多个权限，例如：使用INSERT... SELECT语句，请求Insert和Select两个权限，而这两个权限可能在授予用户时范围不同，假如Insert权限授予的范围是全局性的，而Select权限授予的范围是库级别的，此时，Insert权限是保存在user表中的，Select权限是保存在db表中的，那么服务器需要分两次查询将两个表中记录的权限信息进行组合，然后再判断用户是否具有使用INSERT...SELECT语句所请求的权限，并返回相应的请求结果。如果任意一个权限不满足，则拒绝访问。