MySQL用户和权限管理(一)

SEian.G

发布于 2021-03-03 09:57:45

3.4K00

代码可运行

文章被收录于专栏：SEian.G学习记录SEian.G学习记录

运行总次数：0

代码可运行

针对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账户与权限管理的第一部分就先介绍到这里，请关注第二部分的介绍与使用；

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-02-19，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库