MySQL权限表有哪些？user表、db表等权限表结构与作用解析

MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限。

MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。用户管理包括管理用户账号、权限等。

介绍MySQL用户管理中的相关知识点，包括权限表、账户管理和权限管理。

PART1. 权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db 脚本初始化。

存储账户权限信息的表主要有user、db、host、tables_priv、columns_priv和procs_priv。本节将为读者介绍这些表的内容和作用。

1. user 表

user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。

MySQL8.0中user表由42个字段，这些字段分为4类，分别是用户列、权限列、安全列和资源控制列。

1. 用户表

user 表的用户列包括HOST、User、authenticatio_string，分别表示主机名、用户名和密码。其中User和Host为User表的联合主键。

当用户和服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。

这3个字段的值就是创建账户时保护的账户信息。修改用户面膜时，实际就是修改user表的authentication_string字段的值。

2. 权限列

权限列的字段确定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。

user表中对应的权限时针对所有用户数据库的。这些字段值的类型为ENUM，可以取的值只能为Y和N，Y表示该用户有对应的权限；N表示用户没有对应的权限。查看user表的结构可以看到，这些字段来修改用户对应的权限。

3. 安全列

安全列只有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。

ssl用于加密；x509标准可用于识别用户；Plugin字段识别可以用于验证用户省份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。

读者可以通过SHOW VARIABLES LIKE ‘have_openssl’ 语句来查看服务器是否支持ssl功能。

4. 资源控制列

资源控制列的字段用来限制用户使用资源，包括4个字段：

1）max_questions——用户每小时允许执行的查询操作次数。

2）max_updates——用户每小时允许执行的更新操作次数。

3）max_connections——用户每小时允许执行的连接操作次数。

4）max_user_connections——用户允许同时建立的连接次数。

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

2. db表

db表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从那个主机存取哪个数据库。

db表比较常用。

1. 用户列

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

host表不存储用户名称，用户列只有2个字段，分别是Host和Db，表示从某个主机连接的用户对某个数据库的操作权限，其主键包括Host和Db两个字段。

host很少用到，一般情况下db表就可以满足权限控制需求了。

2. 权限列

db表中create_routine_priv和alter_routine_priv这两个字段表明用户是否有创建和修改存储过程的权限。

user表中的权限是针对所有数据库的，如果希望用户只对某个数据库有操作权限，那么需要将user表中对应的权限设置为N，然后在db表中设置对应数据库的操作权限。

例如，有一个名称为Zhangting的用户分别从名称为large.domain.com 和small.domain.com 的两个主机连接到数据库，并需要操作books数据库。这时，可以将用户名称Zhangting添加到db表中，而db表中的host字段值为空，然后将两个主机地址分别作为两条记录的host字段值添加到host表中，并将两个表的数据库字段设置为相同的值books。

当有用户连接到MySQL服务器时，db表中没有用户登录的主机名称，则MySQL会从host表中查找相匹配的值，并根据查询的结果决定用户的操作是否被允许。

3. tables_priv表和columns_priv表

tables_priv 表用来对表设置操作权限，columns_priv 表用来对表的某一列设置权限。

tables_priv表和columns_priv表的结构：

1）Host、Db、User和Table_name 4个字段分别表示主机名、数据库名、用户名和表名。

2）Grantor表示修改该记录的用户

3）Timestamp字段表示修改该记录的时间

4）Table_priv表示对表的操作权限，包括Select、Insert、Delete、Create、Drop、Grant、References、Index和Alter。

5）Column_priv 字段表示表中的列的操作权限，包括Select、Insert、Update和References，columns_priv 表只有7个字段，分别是Host、Db、User、Table_name、Column_name、Timestamp、Column_priv。其中，Column_name用来指定对哪些数据列具有操作权限。

4. procs_priv 表

procs_priv 表可以对存储过程和存储函数设置操作权限。

procs_priv 的表结构：

procs_priv 表包含8个字段，分别是Host、Db、User、Routine_time、Routine_type、Grantor、Proc_priv 和 Timestamp。

1）Host、Db和User 字段分别表示主机名、数据库名和用户名。Routine_name表示存储过程或函数的名称。

2）Routine_type 表示存储过程或函数的类型。Routine_type字段有两个值，分别是Function 和Procedure：Function 表示这是一个函数，Procedure表示这是一个存储过程。

3）Grantor是插入或修改该记录的用户。

4）Proc_priv表示拥有的权限，包括Execute、Alter Routine、Grant 3种。

5）Timestamp 表示记录更新时间。

PART2. 账户管理

MySQL提供了许多语句来管理用户账号，包括登录和退出MySQL服务器、包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。

MySQL数据库的安全性需要通过账号管理来保证。

1. 登录和退出MySQL服务器

通过MySQL-help命令可以查看MySQL命令帮助信息。MySQL命令的常用参数：

1）-h 主机名，可以使用该参数指定主机名或ip，如果不指定，默认是localhost。

2）-u 用户名，可以使用该参数指定用户名。

3）-p 密码，指定登陆密码

4）-P 端口号，该参数后面接MySQL服务器的端口号，默认为3306。

5）数据库名，可以在命令的最后指定数据库名。

6）-e 执行SQL语句。如果指定了该参数，将在登录后执行-e 后面的命令或SQL语句并退出。

2. 新建普通用户

1. 使用CREATE USER语句创建新用户

执行CREATE USER 或 GRANT 语句时，服务器会修改相应的用户授权表，添加或者修改用户及其权限。CREATE USER语句的基本语法格式如下：

CREATE USER user_specification    [, user_specification] ...user_specification:    user@host    [        IDENTIFIED BY [PASSWORD] 'password'       | IDENTIFIED WITH auth_plugin [AS 'auth_string']    ]

user 表示创建的用户的名称；host 表示允许登录的用户主机名称；IDENTIFIED BY 表示用来设置用户的密码；[PASSWORD] 表示使用哈希值设置密码，该参数可选；‘password’表示用户登录时使用的普通明文密码；IDENTIFIED WITH 语句为用户指定一个身份验证插件；auth_plugin 是插件的名称，插件的名称可以是一个带单引号的字符串或者带双引号的字符串；auth_string是可选的字符串参数，该参数将传递给身份验证插件，由该插件解释该参数的意义。

CREATE USER语句会添加一个新的MySQL账户。使用CREATE USER语句用户，必须有全局的CREATE USER权限或MySQL数据库的INSERT 权限。每添加一个用户，CREATE USER语句会在MySQL.user表中添加一条新纪录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER 语句会返回一个错误。

2. 直接操作MySQL用户表

可以使用INSERT语句向user表中直接插入一条记录来创建一个新的用户。使用INSERT语句，必须拥有对MySQL.user表的INSERT权限。

INSERT INTO MySQL.user(Host, User, authentication_string)VALUES('host', 'username', MD5('password'));

Host、User、authentication_string 分别是user表中的主机、用户名称和密码字段；MD5()函数为密码加密函数。

3. 删除普通用户

1. 使用DROP USER语句删除用户

DROP USER user [, user];

DROP USER语句用于删除一个或多个MySQL账号。要使用DROP USER，必须拥有MySQL数据库的全局CREATE USER权限或DELETE权限。使用与GRANT或REVOKE相同的格式为每个账户名称。

注意：DROP USER不能主动关闭任何打开的用户对话。而且，如果用户有打开的对话，此时取消用户，命令则不会生效，直到用户对话被关闭后才能生效。一旦对话被关闭，用户也被取消，此用户再次试图登录时将会失败。

2. 使用DELETE语句删除用户

DELETE FROM MySQL.user WHERE host='hostname' and user='username'

host 和 user 为 user表中的两个字段，两个字段的组合确定所要删除的账户记录。

4. root 用户修改自己的密码(不建议尝试)

因为所有账户信息都保存在user表中，所以可以通过直接修改user表来改变root用户的密码。root用户登录到MySQL服务器后，使用UPDATE语句修改MySQL数据库的user表的authentication_string字段从而修改用户的密码。使用UPDATA语句修改root用户密码的语句：

UPDATE mysql.user set authentication_string=MD5('123456') WHERE User='root' and Host='localhost';

PASSWORD()函数用来加密用户密码。执行UPDATE语句后，需要执行FLUSH PRIVILEGES语句重新加载用户权限。

5. root 用户修改普通用户密码

root 用户拥有很高的权限，不仅可以修改自己的密码，还可以修改其他用户的密码。root用户登录MySQL服务器胡，可以通过SET语句修改MySQL.user表、通过UPDATE语句修改用户的密码。

创建用户user：

CREATE USER 'user'@'localhost' IDENTIFIED BY 'my123';

1. 使用SET语句修改普通用户的密码

SET PASSWORD FOR 'user'@'localhost' = 'sa123';

2. 使用UPDATE语句修改普通用户的密码

使用root用户登录到MySQL服务器后，可以使用UPDATE语句修改MySQL数据库的user表的password字段，从而修改普通用户的密码。

UPDATE MySQL.user SET authentication_string=MD5("123456")WHERE User="username" AND Host="hostname";

MD5()函数用来加密用户密码。执行UPDATE语句后，需要执行FLUSH PRIVILEGES语句重新加载用户权限。

PART3. 权限管理

权限管理主要是对登录到MySQL的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限。

1. MySQL的各种权限

账户权限信息被存储在MySQL数据库的user、db、host、table_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

GRANT和REVOKE语句所涉及的权限的名称如下所示，其中还有在授权表中每个权限的表列名称和每个权限有关的操作对象等。

1）CREATE和DROP权限，可以创建新数据库和表，或删除（移掉）已有数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户可以删掉MySQL访问权限保存的数据库。

2）SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。

3）SELECT权限只有在它们真正从一个表中检索行时才被用到。

4）INDEX权限允许创建或删除索引，INDEX使用已有表。如果具有某个表的CREATE权限，可以在CREATE TABLE语句中包括索引定义。

5）ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表。

6）CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUE权限用来执行保存的程序。

7）GRANT权限允许给予用户使用LOAD DATA INFILE 和 SELECT ... INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。FILE权限允许用户在MySQL服务器具有写权限的目录下创建新文件，但不能覆盖已有文件。

8）FILE权限给予用户使用LOAD DATA INFILE和SELECT ... INTO OUTFILE 语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。FILE权限允许用户在MySQL服务器具有写权限的目录下创建新文件，但不能覆盖已有文件。

其余的权限用于管理性操作，它使用MySQLadmin程序或SQL语句实施。

1）reload命令告诉服务器将授权表重新读入内存；flush-privileges 是reload的同义词；refresh命令清空所有表并关闭/打开记录文件；其他flush-xxx命令执行类似refresh的功能，但是范围更有限，并且在某些情况下可能更好用。

2）shutdown命令关掉服务器。只能从MySQLadmin发出命令。

3）processlist命令显示在服务器内执行的现成的信息（其他账户相关的客户端执行的语句）。kill命令杀死服务器线程。用户总是能显示或杀死自己的线程，但是需要PROCESS权限来显示或杀死其他用户和SUPER权限启动的线程。

4）kill命令能用来终止其他用户或更改服务器的操作方式。

2. 授权

授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授予权限。

授予的权限可以分为多个层级：

1. 全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在MySQL.user表中。GRANT ALL ON *.* 和REVOKE ALL ON *.*只授予和撤销全局权限。

2. 数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在MySQL.db 和 MySQL.host表中。GRANT ALL ON db_name. 和 REVOKE ALL ON db_name.*只授予和撤销数据库权限。

3. 表层级

表权限适用于一个给定表中的所有列。这些权限存储在MySQL.tables_priv表中。GRANT ALL ON db_name.tbl_name 和 REVOKE ALL ON db_name.tbl_name 只授予和撤销表权限。

4. 列层级

列权限适用于一个给定表中的单一列。这些权限存储在MySQL.columns_priv表中。当使用REVOKE时，必须指定与被授予列相同的列。

5. 子程序层级

CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并且存储在MySQL.procs_priv表中。

在MySQL中，必须是拥有GRANT 权限的用户才可以执行GRANT语句。

要使用GRANT或REVOKE，必须拥有GRANT OPTION权限，并且必须用于授予或撤销的权限。

GRANT priv_type[(columns)] [, priv_type[(columns)]] ...ON [object_type] table1, table2, ..., tablenTO user [WITH GRANT OPTION]object_type=TABLE | FUNCTION | PROCEDURE

其中，priv_type参数表示权限类型；columns参数表示权限作用于哪些列上，不指定该参数，表示作用域整个表；table1, table2, ..., tablen 表示授予权限的列所在的表；object_type指定授权作用的对象类型包括TABLE（表）、FUNCTION（函数）和PROCEDURE（存储过程），当从旧版本的MySQL升级时，要使用object_type子句，必须升级授权表；user参数表示用户账户，由用户名和主机名构成，形式是"'username'@'hostname'"；IDENTIFIED BY 参数用于设置密码。

WITH关键字后可以跟一个或多个with_option参数。

1）GRANT OPTION:被授权的用户可以将这些权限赋予别的用户。

2）MAX_QUERIES_PER_HOUR count：设置每个小时可以执行count次查询。

3）MAX_UPDATES_PER_HOUR count：设置每小时可以执行count次更新。

4）MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。

5）MAX_USER_CONNECTIONS count：设置单个用户可以同时建立count个连接。

3. 收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账号的记录将从db、host、tables_priv 和columns_priv 表中删除，但是用户账号记录仍然在user表中保存。

在将用户账户从user表删除之前，应该收回相应用户的所有权限。

第一种语法是收回所有用户的所有权限，用于取消对于已命名的用户的所有全局层级数据库层级、数据库层级、表层级和列层级的权限。

REVOKE ALL PRIVILEGES, GRANT OPTIONFROM 'user'@'host' [, 'user'@'host' ...]

REVOKE语句必须和FROM语句一起使用。FROM 语句指明需要收回权限的账户。

另一种为长格式的REVOKE语句：

REVOKE priv_type [(columns)] [, priv_type[(columns)]] ...ON table1, table2, ..., tablenFROM 'user'@'host'[, 'user'@'host'...]

其中，priv_type参数表示权限类型；columns参数表示权限作用于哪些列上，不指定该参数，表示作用于整个表；table1，table2，...，tablen 表示从哪个表中收回权限；‘user’@'host'参数表示用户账户，由用户名和主机名构成。

要使用REVOKE语句，必须拥有MySQL数据库的全局CREATE USER权限或UPDATE 权限。

注意：当从旧版本的MySQL升级时，如果要使用EXECUTE、CREATE VIEW、SHOW VIEW、CREATE USER、CREATE ROUTINE和ALTER ROUTINE权限，必须首先升级授权表。

4. 查看权限

SHOW GRANTS语句可以显示指定用户的权限信息，使用SHOW GRANT查看账户信息的基本语法格式如下：

SHOW GRANTS FOR 'user'@'host';

其中，user表示登录用户的名称，host表示登录的主机名称或者IP地址。在使用该语句时，要确保指定的用户名和主机名都要用单引号括起来，并使用’@‘符号将两个名字分隔开。

在这里，只是定义了个别的用户权限，GRANT可以显示更加详细的权限信息，包括全局级的和非全局级的权限，如果表层级或者列层级的权限被授予用户，那么它们也能在结果中显示出来。

在前面创建用户时，查看新建的账户时使用SELECT语句，也可以通过SELECT语句查看user表中的各个权限字段以确定用户的权限信息：

SELECT privileges_list FROM user WHERE user='username', host='hostname';

privileges_list 为想要查看的权限字段，可以为Select_priv、Insert_priv等。

PART4. 访问控制

正常情况下，并不希望每个用户都可以执行所有的数据库操作。当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。MySQL的访问控制分为两个阶段：连接核实阶段和请求核实阶段。

1. 连接核实阶段

当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝连接，即客户端用户连接请求中会提供用户名称、主机地址名和密码。MySQL使用user表中的3个字段（Host、User和authentication_string）执行身份检查，服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

2. 请求核实阶段

建立了连接之后，服务器进入访问控制的阶段2.对在此连接上的每个请求，服务器检查用户要执行的操作，然后检查是否由足够的权限来执行它。这正是授权表中的权限列发挥作用的地方。这些权限可以来自user、db、host、tables_priv或columns_priv 表。

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

注意：MySQL通过向下层级的顺序检查权限表（从user表到columns_priv表），但并不是所有的权限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时，只涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表。

PART5. 提升安全性

1. AES 256加密

之前讲过加密函数。其实MySQL支持多种加密解密模式。

MySQL8.0支持多种AES256加密模式，通过更大的密钥长度和不同的块模式增强了高级加密标准。这里主要通过加密函数AES_ENCODE() 和解密函数 AES_DECODE() 来提高安全强度。

1. AES_ENCRYPT()

AES_ENCRYPT(str,pswd_str)

str为需要加密的字符串，参数pswd_str是密钥。

下面通过将字符串’Adversity does teach who your real friends are‘加密，密钥为’key10001‘，加密后的串存在@ss中。

SET@ss=AES_ENCRYPT('Adversity does teach who your real friends are','key10001');

2. AES_DECODE()

AES_DECRYPT(str,pswd_str)

str为需要解密的字符串，参数pswd_str是密钥。

ENCRYPT()：使用UNIX crypt()系统加密字符串，ENCRYPT()函数接收要加密的字符串和（可选的）用于加密过程的salt（一个可以唯一确定口令的字符串，就像钥匙一样）

3. 将加密字符串存入数据表中

用户将加密的字符串存入数据表的过程中经常会出问题。需要使用varbinary、binary、blob 字段类型存储数据。

举例：创建数据表mm，包括3个字段，属性分别为varbinary、binary、blob。

CREATE TABLE mm (s1 varbinary(16), s2 binary(16), s3 blob);

2. 密码到期更换策略

MySQL8.0 允许数据库管理员手动设置账户密码过期时间。任何密码超期的账户想要连接服务端时都必须更改密码。通过设置default_password_lifetime 参数可以设置账户过期时间。

设置’SZM001‘用户的密码过期时间为260天。

将用户密码过期重新设置为永不过期：

3. 安全模式安装

MySQL新增了”安全模式“的安装形式，从而可以避免用户的数据被泄露。

1）为root账号设置密码。

2）移除能从本地主机以外的地址访问数据库的root账户。

3）移除匿名帐户。

4）移除test数据库，该数据库默认可被任意用户甚至匿名帐户访问。

使用mysqld-initialize命令来安装MySQL实例默认是安全的，原因如下：

1）在安装过程只创建一个root账户‘root'@’localhost‘，自动为这个账户生成一个随机密码并标记密码过期。

2）数据库管理员必须用root账户及该随机密码登录并设置一个新密码后才能对数据库进行正常操作。

3）安装过程不创建任何匿名帐户。

4）安装过程不创建test数据库。

PART6. MySQL8.0 的新特性——管理角色

在MySQL8.0数据库中，角色可以看成是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无须为每个用户单独授权。

创建角色：

CREATE ROLE role_tt; # 创建角色

给角色授予权限：

GRANT SELECT ON db.* to 'role_tt'; #给角色role_tt授予查询权限

创建用户myuser1：

CREATE USER 'myuser1'@'%' identified by '123456';

为用户myuser1赋予角色role_tt：

GRANT 'role_tt' TO 'myuser1'@'%';

给角色role_tt增加insert权限：

GRANT INSERT ON db.* to 'role_tt';

给角色role_tt删除insert权限：

REVOKE INSERT ON db.* FROM 'role_tt';

查看默认角色信息，执行语句如下：

SELECT * FROM mysql.default_roles;

查看角色与用户关系：

SELECT * FROM mysql.role_edges;

删除角色：

DROP ROLE role_tt;

总结