280.MySQL的权限管理

说了前面这些内容，相信大家对权限管理有基本的认知了，接下来深入说下MySQL内是如何管理的。

首先，MySQL 在安装时会自动创建一个名为 mysql 的数据库，mysql 数据库中存储的都是用户权限表。用户登录以后，MySQL 会根据这些权限表的内容为每个用户赋予相应的权限。

• user表：最重要的一个权限表，用来记录允许连接到服务器的用户信息。
• db 表：非常重要的权限表，表中存储了用户对某个数据库的操作权限。
• tables_priv 表，columns_priv 表：对于单个表和字段进行设置。
• procs_priv 表：关于存储过程的权限表

‍

‍

user表

user 表中的字段大致可以分为 4 类，分别是用户列、权限列、安全列和资源控制列，下面主要介绍这些字段的含义。

需要注意的是，在 user 表里启用的所有权限都是全局级的，适用于所有数据库。

用户列

用户列存储了用户连接 MySQL 数据库时需要输入的信息：

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机名
User	char(32)	NO	无	用户名
authentication_string	text	YES	无	密码（以密文形式存储）

需要注意的是 MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication_string。

用户登录时，如果这 3 个字段同时匹配，MySQL 数据库系统才会允许其登录。创建新用户时，也是设置这 3 个字段的值。

修改用户密码时，实际就是修改 user 表的 authentication_string 字段的值。因此，这 3 个字段决定了用户能否登录。

‍

权限列

权限列的字段决定了用户的权限，用来描述在全局范围内允许对数据和数据库进行的操作。

权限大致分为两大类，分别是高级管理权限和普通权限：

• 高级管理权限主要对数据库进行管理，例如关闭服务的权限、超级权限和加载用户等；
• 普通权限主要操作数据库，例如查询权限、修改权限等。

user 表的权限列包括 Select_priv、Insert_ priv 等以 priv 结尾的字段，这些字段值的数据类型为 ENUM，可取的值只有 Y 和 N：Y 表示该用户有对应的权限，N 表示该用户没有对应的权限。从安全角度考虑，这些字段的默认值都为 N。

如果要修改权限，可以使用 GRANT 语句为用户赋予一些权限，也可以通过 UPDATE 语句更新 user 表的方式来设置权限。

例如，我们可以看看目前用户的 select_priv 和 insert_priv：

select user, select_priv, insert_priv from mysql.user;
+------------------+-------------+-------------+
| user             | select_priv | insert_priv |
+------------------+-------------+-------------+
| pig              | N           | N           |
| pig2             | Y           | Y           |
| testgrant        | Y           | N           |
| mysql.infoschema | Y           | N           |
| mysql.session    | N           | N           |
| mysql.sys        | N           | N           |
| root             | Y           | Y           |
+------------------+-------------+-------------+
7 rows in set (0.00 sec)

‍

‍

完整的表如下：

字段名	字段类型	是否为空	默认值	说明
Select_priv	enum('N','Y')	NO	N	是否可以通过SELECT 命令查询数据
Insert_priv	enum('N','Y')	NO	N	是否可以通过 INSERT 命令插入数据
Update_priv	enum('N','Y')	NO	N	是否可以通过UPDATE 命令修改现有数据
Delete_priv	enum('N','Y')	NO	N	是否可以通过DELETE 命令删除现有数据
Create_priv	enum('N','Y')	NO	N	是否可以创建新的数据库和表
Drop_priv	enum('N','Y')	NO	N	是否可以删除现有数据库和表
Reload_priv	enum('N','Y')	NO	N	是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令， 包括日志、权限、主机、查询和表
Shutdown_priv	enum('N','Y')	NO	N	是否可以关闭MySQL服务器。 将此权限提供给root账户之外的任何用户时，都应当非常谨慎
Process_priv	enum('N','Y')	NO	N	是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv	enum('N','Y')	NO	N	是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv	enum('N','Y')	NO	N	是否可以将自己的权限再授予其他用户
References_priv	enum('N','Y')	NO	N	是否可以创建外键约束
Index_priv	enum('N','Y')	NO	N	是否可以对索引进行增删查
Alter_priv	enum('N','Y')	NO	N	是否可以重命名和修改表结构
Show_db_priv	enum('N','Y')	NO	N	是否可以查看服务器上所有数据库的名字， 包括用户拥有足够访问权限的数据库
Super_priv	enum('N','Y')	NO	N	是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程； 使用SET GLOBAL命令修改全局MySQL变量， 执行关于复制和日志的各种命令。（超级权限）
Create_tmp_table_priv	enum('N','Y')	NO	N	是否可以创建临时表
Lock_tables_priv	enum('N','Y')	NO	N	是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv	enum('N','Y')	NO	N	是否可以执行存储过程
Repl_slave_priv	enum('N','Y')	NO	N	是否可以读取用于维护复制数据库环境的二进制日志文件
Repl_client_priv	enum('N','Y')	NO	N	是否可以确定复制从服务器和主服务器的位置
Create_view_priv	enum('N','Y')	NO	N	是否可以创建视图
Show_view_priv	enum('N','Y')	NO	N	是否可以查看视图
Create_routine_priv	enum('N','Y')	NO	N	是否可以更改或放弃存储过程和函数
Alter_routine_priv	enum('N','Y')	NO	N	是否可以修改或删除存储函数及函数
Create_user_priv	enum('N','Y')	NO	N	是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户
Event_priv	enum('N','Y')	NO	N	是否可以创建、修改和删除事件
Trigger_priv	enum('N','Y')	NO	N	是否可以创建和删除触发器
Create_tablespace_priv	enum('N','Y')	NO	N	是否可以创建表空间

‍

‍

‍

‍

安全列

安全列主要用来判断用户是否能够登录成功，user 表中的安全列如下所示：

字段名	字段类型	是否为空	默认值	说明
ssl_type	enum('','ANY','X509','SPECIFIED')	NO		支持ssl标准加密安全字段
ssl_cipher	blob	NO		支持ssl标准加密安全字段
x509_issuer	blob	NO		支持x509标准字段
x509_subject	blob	NO		支持x509标准字段
plugin	char(64)	NO	mysql_native_password	引入plugins以进行用户连接时的密码验证，plugin创建外部/代理用户
password_expired	enum('N','Y')	NO	N	密码是否过期 (N 未过期，y 已过期)
password_last_changed	timestamp	YES		记录密码最近修改的时间
password_lifetime	smallint(5) unsigned	YES		设置密码的有效时间，单位为天数
account_locked	enum('N','Y')	NO	N	用户是否被锁定（Y 锁定，N 未锁定）

‍

注意：

1. 即使 password_expired 为“Y”，用户也可以使用密码登录 MySQL，但是不允许做任何操作。
2. 通常标准的发行版不支持 ssl，读者可以使用 SHOW VARIABLES LIKE "have_openssl" 语句来查看是否具有 ssl 功能。如果 have_openssl 的值为 DISABLED，那么则不支持 ssl 加密功能。

‍

资源控制列

‍

资源控制列的字段用来限制用户使用的资源，user 表中的资源控制列如下表所示：

字段名	字段类型	是否为空	默认值	说明
max_questions	int(11) unsigned	NO	0	规定每小时允许执行查询的操作次数
max_updates	int(11) unsigned	NO	0	规定每小时允许执行更新的操作次数
max_connections	int(11) unsigned	NO	0	规定每小时允许执行的连接操作次数
max_user_connections	int(11) unsigned	NO	0	规定允许同时建立的连接次数

以上字段的默认值为 0，表示没有限制。一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时才可以在此执行对应的操作。可以使用 GRANT 语句更新这些字段的值。

‍

例如，授权时后面带 with_option 参数，这个参数有 5 个选项：

• GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
• MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
• MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
• MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
• MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

‍

‍

‍

管理用户的第二种方式

其实，对用户进行增加、删除和管理，也可以通过对user表进行insert、delete和update来实现。例如删除用户：

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

‍

只不过这样比较麻烦，不推荐。

‍

‍

db表

表中的字段大致可以分为两类，分别是用户列和权限列。

db 表用户列有 3 个字段，分别是 Host、User、Db，标识从某个主机连接某个用户对某个数据库的操作权限，这 3 个字段的组合构成了 db 表的主键。

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机名
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名

‍

db 表中的权限列和 user 表中的权限列大致相同，只是user 表中的权限是针对所有数据库的，而 db 表中的权限只针对指定的数据库。如果希望用户只对某个数据库有操作权限，可以先将 user 表中对应的权限设置为 N，然后在 db 表中设置对应数据库的操作权限。

‍

tables_priv表和columns_priv表

tables_priv 表用来对单个表进行权限设置，columns_priv 表用来对单个数据列进行权限设置。tables_priv 表结构如下表所示：

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名
Table_name	char(64)	NO	无	表名
Grantor	char(93)	NO	无	修改该记录的用户
Timestamp	timestamp	NO	CURRENT_TIMESTAMP	修改该记录的时间
Table_priv	set('Select','Insert','Update','Delete',' Create','Drop','Grant','References', 'Index','Alter','Create View','Show view','Trigger')	NO	无	表示对表的操作权限，包括 Select、Insert、 Update、Delete、Create、Drop、Grant、References、Index 和 Alter 等
Column_priv	set('Select','Insert','Update','References')	NO	无	表示对表中的列的操作权限，包括 Select、Insert、Update 和 References

‍

‍

columns_priv 表结构如下表所示：

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名
Table_name	char(64)	NO	无	表名
Column_name	char(64)	NO	无	数据列名称，用来指定对哪些数据列具有操作权限
Timestamp	timestamp	NO	CURRENT_TIMESTAMP	修改该记录的时间
Column_priv	set('Select','Insert','Update','References')	NO	无	表示对表中的列的操作权限， 包括 Select、Insert、Update 和 References

‍

‍

procs_priv表

procs_priv 表可以对存储过程和存储函数进行权限设置，表结构：

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机名
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名
Routine_name	char(64)	NO	无	表示存储过程或函数的名称
Routine_type	enum('FUNCTION','PROCEDURE')	NO	无	表示存储过程或函数的类型，Routine_type 字段有两个值， 分别是 FUNCTION 和 PROCEDURE。FUNCTION 表示这是一个函数； PROCEDURE 表示这是一个 存储过程。
Grantor	char(93)	NO	无	插入或修改该记录的用户
Proc_priv	set('Execute','Alter Routine','Grant')	NO	无	表示拥有的权限，包括 Execute、Alter Routine、Grant 3种
Timestamp	timestamp	NO	CURRENT_TIMESTAMP	表示记录更新时间

‍

‍

参考

MySQL教程：MySQL数据库学习宝典（从入门到精通） C语言中文网 (opens new window)