文章目录[隐藏]

针对MYSQL用户和权限管理，准备分两个部分来介绍

第一部分：主要是MYSQL数据库的权限体系以及MYSQL访问控制的两个阶段；我们都知道，MYSQL初始化完成之后，自带四个默认的数据库；下面的内容主要涉及到的是mysql库中相关的内容；MYSQL用户权限管理（一）

第二部分：主要是MYSQL账户以及密码管理，会涉及到账户的创建、删除、授权等问题；MYSQL用户权限管理（二）

一、MySQL权限体系

授予MySQL帐户的权限决定了帐户可以执行的操作。MySQL权限在它们适用的上下文和不同操作级别上有所不同：
管理权限使用户能够管理MySQL服务器的操作；这些权限是全局的，因为它们不是特定于特定数据库的。
数据库权限适用于数据库及其中的所有对象。可以为特定数据库或全局授予这些权限，以便它们适用于所有数据库。
可以为数据库中的特定对象，数据库中给定类型的所有对象（例如，数据库中的所有表）或全局的所有对象授予数据库对象（如表，索引，视图和存储例程）的权限。所有数据库中给定类型的对象

MySQL的认证方式是“用户”+“主机”形式，而权限是访问资源对象，MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中，初始化数据库时会初始化这些权限表。存储账户权限信息表主要有：user，db，tables_priv，columns_priv，procs_priv这五张表（5.6之前还有host表，现在已经把host内容整合进user表）。

官方文档对权限有比较详细的描述，为了方便我把其中的表格列在下面。第一列表示所有的权限，可以在 Grant 语句中指定的，第二列是对应权限存储在系统数据库 mysql 几张表中的定义，第三列表示权限作用的范围，其中 Global（Server administration）对应 mysql.user 表，Database 对应 mysql.db 表，Tables 对应 mysql.tables_priv 表，Columns 对应 mysql.columns_priv 表，Stored routines 对应 mysql.procs_priv 表。

GRANT语句赋予对应用户相应的权限，会根据不同的语法存储到不同的表中，以链接中官方文档中的语句为例：

Global Privileges

GRANT ALL ON *.* TO ‘someuser’@’somehost’;GRANT SELECT, INSERT ON *.* TO ‘someuser’@’somehost’;
其中 *.* 表示所有数据的所有表，对应的权限会保存在 mysql.user 表中，和 user 相关联。

user表
user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。例如：一个用户在user表中被授予了DELETE权限，则该用户可以删除MySQL服务器上所有数据库的任何记录。
user表中大概有45个字段，这些字段大概可以分为4类，分别是用户列、权限列、安全列和资源控制列（详细的表结构见下），详细解释如下：
1）Host、User：表示主机和用户，是user表的主键。
2）*-priv：此类型的字段都是权限列，权限列的字段决定了用户的权限，描述了全局范围内允许对数据和数据库进行的操作。包括查询、修改、删除等普通权限，还有包括了关闭服务器、超级权限和加载用户等高级权限。user表对应的权限是针对所有用户数据库的，这些字段的类型为ENUM，可以取的值只能为Y或N，Y表示该用户有对应的权限；N表示没有。
3）ssl_type、ssl_cipher、x509_issuer、x509_subject：这几个字段是安全连接相关的，SSL和证书。
4）max_questions、max_updates、max_connections、max_user_connections：这几个字段是用户资源限制相关的，如此用户某个时间内最大查询、更新、连接等操作。
5）authentication_string、account_locked、password_lifetime、password_last_changed：这几个字段是跟用户密码相关的，部分都是MySQL 5.7新增字段，作用分别是标记账号锁定、密码存活时间和密码改变时间；
MySQL 5.7去掉了password字段，由authentication_string替换，因为5.7更换了密码插件，因此存储密码的字段更换了。

MySQL [(none)]> show create table mysql.user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.01 sec)

Database Privileges

GRANT ALL ON mydb.* TO ‘someuser’@’somehost’;GRANT SELECT, INSERT ON mydb.* TO ‘someuser’@’somehost’;
其中 mydb.* 表示 mydb 数据库下的所有表，对应的权限会保存在 mysql.db 表中，和 db 相关联。

db表
db表存储了用户对某个数据库的操作权限，决定用户能从哪个主机哪个用户来操作哪个数据库。
User表中存储了某个主机和用户对数据库的操作权限，配置和db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响，字段大致可以分为两类：用户列和权限列，详细解释如下：
1）Host、Db、User：表示主机、数据库和用户，是db表的主键。
2）*-priv：此类型的字段都是权限列，权限列的字段决定了用户的权限。user表的权限时针对所有数据库的，全局的；但如果希望某个用户只对某个数据库有相应的查询、修改、删除等普通权限，那么就需要在db表中设定，而user表只有对应的主机、用户和密码等信息。这些字段的类型为ENUM，可以取的值只能为Y或N，Y表示该用户有对应的权限；N表示没有。

MySQL [(none)]> show create table mysql.db\G;
*************************** 1. row ***************************
Table: db
Create Table: CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)

Table Privileges

GRANT ALL ON mydb.mytbl TO ‘someuser’@’somehost’;GRANT SELECT, INSERT ON mydb.mytbl TO ‘someuser’@’somehost’;
对应的权限保存在 mysql.tables_priv 中，和 db , user 关联。

tables_priv表
tables_priv表用来对表设置操作权限，有几个字段分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv，各个字段说明如下：
1）Host、Db、User和Table_name这几个字段分表示主机名、数据库名、用户名和表名。
2）Grantor字段表示修改该记录的用户。
3）Timestamp字段表示修改该记录的时间。
4）Table_priv字段表示对表的操作权限，包括set(‘Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,’Grant’,’References’,’Index’,’Alter’,’Create View’,’Show view’,’Trigger’)。
5）Column_priv字段表示对表中的列的操作权限，包括set(‘Select’,’Insert’,’Update’,’References’)。

MySQL [(none)]> show create table mysql.tables_priv\G;
*************************** 1. row ***************************
Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

Column Privileges

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@’somehost’;
对应的权限保存在 mysql.columns_priv 中，和 db, table, user 关联。

columns_priv表
columns_priv表用来对表设置操作权限，有这么几个字段分别是Host、Db、User、Table_name、Timestamp、Column_name和Column_priv，各个字段说明如下：
1）Host、Db、User和Column_name这几个字段分表示主机名、数据库名、用户名和列名。
2）Timestamp字段表示修改该记录的时间。
3） Column_priv字段表示对表中的列的操作权限，包括set(‘Select’,’Insert’,’Update’,’References’)。

MySQL [(none)]> show create table mysql.columns_priv\G;
*************************** 1. row ***************************
Table: columns_priv
Create Table: CREATE TABLE `columns_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges'
1 row in set (0.00 sec)

Stored Routine Privileges

GRANT CREATE ROUTINE ON mydb.* TO ‘someuser’@’somehost’;GRANT EXECUTE ON PROCEDURE mydb.myproc TO ‘someuser’@’somehost’;
对应的权限保存在 mysql.procs_priv 中，和 routine_name， db，user 关联。

procs_priv表
存储过程和存储函数相关的权限，分别是Host、Db、User、Routine_name、Routine_type、Grantor、Proc_priv和Timestamp，各个字段的说明如下：
1）Host、Db和User字段分别表示主机名、数据库名和用户名；Routine_name表示存储过程或函数的名称。
2）Routine_type字段表示存储过程或函数的类型。
3）Routine_type字段有两个值，分别是FUNCTION和PROCEDURE。FUNCTION表示这是一个函数；PROCEDURE表示这是一个存储过程。
4）Grantor字段记录是插入或修改该记录的用户。
5）Proc_priv字段表示拥有的权限，包括Execute、Alter Routine、Grant这3种。
6）Timestamp字段表示记录更新时间。

MySQL [(none)]> show create table mysql.procs_priv\G;
*************************** 1. row ***************************
Table: procs_priv
Create Table: CREATE TABLE `procs_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,
`Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
`Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges'
1 row in set (0.00 sec)

二、MySQL访问控制两阶段

阶段1：客户端连接验证阶段
当尝试连接到MySQL服务器时，服务器会根据以下条件接受或拒绝连接：

您的身份以及是否可以通过提供正确的密码来验证您的身份
您的帐户是锁定还是解锁

当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证，来接受或拒绝连接。使用三个执行凭证检查 user表范围列（Host，User，和 authentication_string）。锁定状态记录在user表格 account_locked列中。仅当某些表行中的Host和 User列user与客户端主机名和用户名匹配时，服务器才接受连接，客户端提供该行中指定的密码， account_locked值为’N’。服务器首先检查凭据，然后检查帐户锁定状态。任一步骤失败都会导致服务器完全拒绝您的访问权限。否则，服务器接受连接，然后进入阶段2并等待请求。

如果User列值为非空，则传入连接中的用户名必须完全匹配。如果 User值为空，则它匹配任何用户名。如果user与传入连接匹配的表行具有空白用户名，则该用户被视为没有名称的匿名用户，而不是具有客户端实际指定名称的用户。这意味着空白用户名用于连接持续时间内的所有进一步访问检查（即，在阶段2期间）。

该authentication_string列可以为空白。这不是通配符，并不表示任何密码匹配。这意味着用户必须在不指定密码的情况下进行连接。如果服务器使用插件对客户端进行身份验证，则插件实现的身份验证方法可能会也可能不会使用authentication_string 列中的密码。在这种情况下，外部密码也可能用于向MySQL服务器进行身份验证。

下表显示了表中各种组合User和Host值。

传入连接的客户端主机名和用户名可以匹配user表中的多个行。前述组实例证明这一点：若干条目的匹配示出从连接h1.example.net的fred。
当可能存在多个匹配项时，服务器必须确定要使用哪些匹配项。它解决了这个问题如下：
只要服务器将user表读入内存，它就会对行进行排序。
当客户端尝试连接时，服务器按排序顺序查看行。
服务器使用与客户端主机名和用户名匹配的第一行。

关于连接验证阶段详细可参考官方文档说明：https://dev.mysql.com/doc/refman/5.7/en/connection-access.html

阶段2：客户端请求验证阶段

建立连接后，服务器进入访问控制的第2阶段。对于通过该连接发出的每个请求，服务器确定您要执行的操作，然后检查您是否具有足够的权限来执行此操作。这是授权表中的特权列发挥作用的地方。这些权限可以来自mysql库下的 user，db， tables_priv，columns_priv，或procs_priv表。

那么接下来就可以发送数据库的操作命令给服务器端处理，服务器检查用户要执行的操作，在确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授权；MySQL将检查db表，db表时下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。

关于请求验证阶段详细可参考官方文档说明：https://dev.mysql.com/doc/refman/5.7/en/request-access.html

MYSQL账户与权限管理的第一部分就先介绍到这里，请关注第二部分的介绍与使用；MYSQL用户权限管理（二）