MySQL 中常常被忽略的用户与权限

Tips：本篇文章来自于慕课专栏《实战派 MySQL 高阶应用指南》。

相信大家对于 “用户与权限” 这个名词一定不会陌生，我们的应用系统、Redis 集群、Hadoop 集群等等都会有各自对应的用户与权限体系。

对于 MySQL 这种多用户数据库来说，拥有强大的访问控制体系自然是理所应当的，而访问控制过程也正是用户与权限所负责的。但是，这个概念常常被忽略，因为大多数的时间，我们都是询问 DBA 获取一个可用的用户名和密码，而它背后的逻辑与思想也就自然没有去深究了。

在这一篇文章里，我们需要围绕三个问题去看待 MySQL 的用户与权限：

• MySQL 是怎样存储权限的，又对权限做了怎样的分类？
• MySQL 是如何控制用户行为的？
• 怎样在 MySQL 中去操作用户与权限？

下面，我们就带着这三个问题来系统的学习下常常被忽略的用户与权限吧。

1. 权限的存储与分类

既然要匹配哪些用户拥有哪些权限，那么这些用户及权限的信息一定得有地方存储才行。我们在安装完 MySQL 之后，可以看到有一个系统库叫做 mysql，用户及权限信息就存储在这个库中。同时，由于 MySQL 中的权限太多（超过20个），做好分类可以简化学习过程，也方便记忆。

1.1 权限的存储

用户与权限信息存储在 mysql 系统库中，一共用5张表去表达，分别是：user、db、tables_priv、columns_priv 和 procs_priv。同时，这5张表也代表了5个层级。下面，我们来详细的说一说这几张表的含义（只需要知道这几张表，暂时不用看它们的结构，我后面会讲到）。

• user 表：它属于全局层级，存储用户账户信息以及全局级别的权限，这些全局权限适用于系统中所有的数据库
• db 表：它属于数据库层级，适用于一个给定数据库中的所有目标
• tables_priv 表：它属于表层级，适用于一个给定表中的所有列
• columns_priv 表：它属于列层级，适用于一个给定表中的单一列
• procs_priv 表：它属于子程序层级，存放存储过程和函数级别的权限

从层级关系中可以看出，除 procs_priv 表之外，各层级的权限范围依次递减，即用户（代表所有）、库、表和列。

1.2 权限的分类

搞清楚了权限存储的位置之后，我们接下来看一看 MySQL 支持哪些权限，以及怎样对这些权限进行分类。下面，我先用一张表来说明 MySQL 中常见的权限。

其实，根据上表中对权限层级的描述，也就能够大概知道怎样对权限进行分类了。这里我们大致可以把权限简单的分为三类：

• 数据：对应到库、表、索引和列层级的权限都归为数据权限
• 管理：对应到服务器层级的权限，即管理员所操作的都归为管理权限
• 程序：对应到存储过程层级的权限都归为程序权限

当然，这些权限的分类是 “自定义的”，也就是说并不是 MySQL 标准中定义的，你也完全可以按照你的理解去把它们进行分类。

2. 解读 MySQL 中的权限表

到这里，我们已经知道了 MySQL 使用 5 张表去存储用户与权限信息，但是并没有细致的讲解这其中各个表中的字段代表什么样的含义。下面，我们来依次解读下这5张表。

2.1 mysql.user 表

user 表的权限是用户层级（全局），是基于服务器范围的所有权限。这个表包含的列非常多，其中大多数都以 priv 结尾，也就是权限标识的意思。在这里我以其中的一列 Select_priv 来说明含义：如果某个用户拥有所有数据库的 SELECT 权限，那么，这个用户对应的记录 Select_priv 列值就为 Y，否则为 N（枚举类型定义）。下面，我用一张表解释下 user 表各个字段的含义（并不对字段类型、默认值做过多说明，可以使用 desc 自行查看）。

由于 user 表的权限级别太高，责任重大，所以字段也就会稍微多一些。但是，仔细观察，你会发现，表字段的命名非常讲究，易于理解。

最后，需要特别提醒一下，MySQL 专门提供了命令创建用户与权限，表的修改过程也是封装在这些命令的实现中。如果你不是很清楚修改这张表会造成什么影响，一定不要尝试修改。

2.2 mysql.db 表

如果授予了某个用户单独一个数据库的权限，MySQL 就会在 db 表中保存一条记录。同样，下面我用一张表来解读下 db 表中各个字段的含义。

看上去似乎与 user 表的字段定义是相同的，这种感觉也是没问题的，只是 db 表中的每一条记录代表的是一个库，而 user 表记录代表的是所有库。

2.3 mysql.tables_priv 表

表级权限相对来说就比较简单了，根据之前对 user 和 db 的理解，我们应该可以猜到这张表的核心字段是 “表名”。关于这张表的字段解读，可以参照下表所示。

也许你对这张表最大的疑问是 Column_priv 字段，毕竟 tables_priv 表定义的是表级别的权限，为什么需要有列级权限的声明呢 ？其实这是 MySQL 做的一种优化，为了提高权限检查时的性能。即在权限检查时，如果发现 Column_priv 列值为空，就不需要再去检查列级权限了，省去了一步查询操作。

2.4 mysql.columns_priv 表

列级权限表与 tables_priv 表的结构是高度相似的，只是多了一列 Column_name。下面，我们来继续看这张表的解读。

2.5 mysql.procs_priv 表

procs_priv 表与之前介绍的4张表的结构有所不同，但是有了之前的层级权限基础，理解这张表也是比较简单的，解读如下。

到这里，我们基本就把 MySQL 中的用户与权限的概念、思想和存储搞清楚了。接下来，我们再去看一看 MySQL 怎样利用这些存储的权限对用户的连接和查询做访问控制。

3. MySQL 的访问控制

MySQL 的访问控制分为两个阶段：用户连接阶段和执行查询阶段。抛开权限验证不说，这两个阶段其实也就是你在使用 MySQL 时的所有操作了。所以，可以看到，我们在使用 MySQL 的过程中，权限验证自始至终都伴随着，它有多重要想必你也应该有答案了。

3.1 用户连接阶段

类似的权限验证出现在我们工作中的方方面面，我们在登录任何系统的时候，都会让我们输入用户名和密码，完全匹配之后才能进行接下来的操作。但是，对于 MySQL 来说，为了保证数据的安全，除了最基本的用户名和密码之外，还会有更加严格的限制。

• 用户连接时，MySQL 服务器首先匹配 mysql.user 表中的主机名、用户名和密码，匹配不到则拒绝当前的连接请求
• 检查 mysql.user 表的 max_connections 和 max_user_connections 字段值，如果超过设置的值范围则拒绝连接请求
• 检查 mysql.user 表的 SSL 安全连接配置，如果有配置 SSL，则需要用户提供证书且是合法的

不论我们是通过哪一种方式与 MySQL 服务器建立连接，MySQL 都会依次执行以上的3个权限校验工作。只有当所有的检查都通过后，服务器才会与客户端建立连接。当连接建立之后，用户使用 SQL 语句执行增删改查时，MySQL 服务器又需要做执行查询阶段的验证工作。

3.2 执行查询阶段

查询阶段与连接阶段的检查工作是类似的，也是会对照之前介绍的各个存储表进行比对工作，也同样会有几个步骤的操作。下面，一起来看看吧：

• 检查 mysql.user 表的 max_questions 和 max_updates 字段值，如果超过上限值，则拒绝执行 SQL 语句
• 检查 mysql.user 表，如果拥有全局性权限，直接执行；否则，继续下一步检查
• 检查 mysql.db 表，如果拥有数据库级别的权限，则执行；否则，继续下一步检查
• 检查 mysql.tables_priv，mysql.columns_priv，mysql.procs_priv 表，如果拥有相应对象的权限，则执行；否则，上报权限不足错误

从以上的检查过程可以看出，第一个检查是对属性规定的要求检查，后三个才是对权限的检查。同时，也可以看出，MySQL 的权限检查是一个比较复杂的过程。所以，为了提高性能，MySQL 在启动时会把5张权限表加载到内存中，是典型的空间换时间的思想。

4. 用户与权限管理实践

虽然我们在使用 MySQL 的过程中，几乎不会涉及到用户与权限的问题，因为这些都已经被 DBA 处理了。但是，如果你想完全掌控自己的 MySQL 或者是在没有 DBA 的情况下，学会用户与权限的实践操作就显得很有必要了。下面，我将主要围绕创建用户、分配权限来对这个话题进行讲解。

4.1 用户与权限管理的操作

想要给用户授予或者是删除权限，首先得有个用户。我们在 MySQL 中可以通过 CREATE USER 或 GRANT 语句来创建用户，它们的区别是 GRANT 可以在创建用户的同时授予权限。下面，我们先通过 CREATE USER 来创建用户 imooc-1：

-- 语法：CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';
-- 其中 HOST 可以是 ip 地址、主机名、网段地址或通配符
mysql> CREATE USER 'imooc-1'@'localhost' IDENTIFIED BY 'imooc';
Query OK, 0 rows affected (0.04 sec)

就这样，我们创建完成了用户 imooc-1，是不是非常的简单（此时，机智的你是不是去看一看那几张权限表发生了哪些变化呢 ？）。接下来，我们再去通过 GRANT 语句去创建用户 imooc-2：

-- 语法：GRANT PRIVILEGES ON DB_NAME.TABLE_NAME TO 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';
-- 其中 PRIVILEGES 代表想要授予的权限，ALL PRIVILEGES 代表所有的权限；* 是通配符，代表所有的库和表
mysql> GRANT ALL PRIVILEGES ON *.* TO 'imooc-2'@'localhost' IDENTIFIED BY 'imooc';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

GRANT 语句的语法也是相当简单的，需要注意的是紧接着执行了一个 FLUSH PRIVILEGES 操作，这是因为 MySQL 的权限数据在缓存中，我们更改了权限表，需要刷新缓存。下面，我将给出 GRANT 语句的常见授权方法（结合注释）。掌握了创建用户并给用户授权，也就基本掌握了用户与权限管理的操作。

-- 授予所有的权限
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'HOST';

-- 授予某个库所有表的所有权限
GRANT ALL PRIVILEGES ON DB_NAME.* TO 'USERNAME'@'HOST';

-- 授予某个库所有表的 SELECT、UPDATE 权限
GRANT SELECT, UPDATE ON DB_NAME.* TO 'USERNAME'@'HOST';

-- 授予某个库下某个表的 INSERT 权限
GRANT INSERT ON DB_NAME.TABLE_NAME TO 'USERNAME'@'HOST';

-- 授予某个库下某个表的某个列的 UPDATE 权限
GRANT UPDATE(COLUMN_NAME) ON DB_NAME.TABLE_NAME TO 'USERNAME'@'HOST';

-- 授予某个库下创建、修改、删除表结构的权限
GRANT CREATE ON DB_NAME.* TO 'USERNAME'@'HOST';
GRANT ALTER ON DB_NAME.* TO 'USERNAME'@'HOST';
GRANT DROP ON DB_NAME.* TO 'USERNAME'@'HOST';

以上基本上就是 GRANT 语句的使用方法，更多的权限授予语句无非是 MySQL 提供的权限的组合。需要记住，每次执行授权语句之后，都需要去刷新权限缓存，即执行 FLUSH PRIVILEGES 操作。想一想，我如果授予了错误的用户权限，想要收回怎么办呢 ？别急，MySQL 提供了 REVOKE 语句，它的使用方法如下所示。

-- 撤销某个库下某个表的 DROP 权限
REVOKE DROP ON DB_NAME.TABLE_NAME FROM 'USERNAME'@'HOST';

-- 撤销某个库下某个表的所有权限
REVOKE ALL ON DB_NAME.TABLE_NAME FROM 'USERNAME'@'HOST';

同样，执行撤销授权语句之后，还需要再次执行 FLUSH PRIVILEGES 操作刷新缓存。但同时，需要注意，撤销的权限必须是用户已经被授予的权限。否则，MySQL 将会返回错误。那么，如果我忘记了某个用户是否拥有某个权限，又该怎么办呢 ？MySQL 当然想到了这一点，继续往下看吧：

mysql> SHOW GRANTS FOR 'imooc-1'@'localhost';
+---------------------------------------------------------------------------------------+
| Grants for imooc-1@localhost                                                          |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'imooc-1'@'localhost'                                           |
| GRANT SELECT, UPDATE, CREATE, DROP, ALTER ON `imooc_mysql`.* TO 'imooc-1'@'localhost' |
| GRANT INSERT, UPDATE (type) ON `imooc_mysql`.`worker` TO 'imooc-1'@'localhost'        |
+---------------------------------------------------------------------------------------+

可以看到，使用 SHOW GRANTS FOR 可以查看到格式非常清晰的已经授予的权限。不得不说，MySQL 考虑的是非常全面了。

4.2 用户与权限管理的建议

由于用户与权限在任何系统中都是比较敏感的存在，对于 MySQL 这种管理数据的就更不用多说。所以，一定要谨慎使用。下面，我将总结一些经验之谈，给出用户与权限管理的建议。

• 使用 CREATE USER 或 GRANT 语句去创建用户，不要直接去修改系统表，这是非常危险的操作
• 授予用户能满足使用的最小权限，避免越权访问，最常见的就是只读账号
• 授予权限时一定要指定 ip 地址或 ip 段，避免外部撞库攻击
• 不允许创建弱密码的用户，密码最好包含：字母（大小写都有）、数字、特殊字符
• 定期删除无效用户，或者是回收不再需要的权限

关于以上这些建议只是一些通用场景，大家在实际使用时只作为参考就好，并不是一种限制或者约束。另外，多去尝试应用，记录并分享你在工作中的经验与建议。

5. 总结

MySQL 中的用户与权限是非常容易被大家忽略的话题，也确实是因为在日常的工作中，它出现的频率太低。但是，这并不意味着它就不重要，相反，它是使用 MySQL 的基础，也是保证数据安全的必备技能。大家并不一定非要记住它的方方面面，理解其核心思想，在使用到的时候再回过头去看、去查就是非常好的学习方式。

6. 问题

试一试在你的本地 MySQL 上创建新用户并分配一些权限

从 MySQL 的用户与权限设计，你能得到什么样的启发 ？

Tips：如果你有任何问题，欢迎你在评论区进行留言，想看更多精品内容请点击这里哦