手记

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 中常见的权限。

权限 权限层级 权限解释
CREATE 库、表和索引 创建库、表和索引的权限
DROP 库、表 删除库和表的权限
ALTER 更改表的权限,例如:添加字段、修改字段类型
DELETE 删除数据的权限
INDEX 索引权限
INSERT 插入权限
SELECT 查询权限
UPDATE 更新权限
CREATE VIEW 视图 创建视图权限
SHOW VIEW 视图 查看视图权限
CREATE ROUTINE 存储过程 创建存储过程的权限
ALTER ROUTINE 存储过程 更改存储过程的权限
EXECUTE 存储过程 执行存储过程的权限
PROCESS 服务器 查看进程的权限
SHOW DATABASES 服务器 查看数据库的权限
SHUTDOWN 服务器 关闭数据库的权限
SUPER 服务器 执行 KILL 用户线程的权限

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

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

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

2. 解读 MySQL 中的权限表

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

2.1 mysql.user 表

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

字段名 字段解释
Host 主机名,其中:localhost 代表本地;% 代表不受限制;单独的 ip 代表某一台机器的访问权限
User 用户名
Select_priv SELECT 权限
Insert_priv INSERT 权限
Update_priv UPDATE 权限
Delete_priv DELETE 权限
Create_priv CREATE 权限
Drop_priv DROP 权限
Reload_priv 刷新和重新加载 MySQL 内部缓存的权限
Shutdown_priv 关闭 MySQL 服务器的权限,只能是 root 用户
Process_priv SHOW PROCESSLIST 命令权限
File_priv SELECT INTO OUTFILE 和 LOAD DATA INFILE 命令权限
Grant_priv 权限授予其他用户的权限
References_priv 参照表权限
Index_priv 索引操作(创建、删除)权限
Alter_priv ALTER 权限
Show_db_priv 查看数据库权限
Super_priv Super 权限
Create_tmp_table_priv 创建临时表权限
Lock_tables_priv 执行lock table 权限
Execute_priv 执行存储过程的权限
Repl_slave_priv 复制权限
Repl_client_priv 复制权限
Create_view_priv 创建视图权限
Show_view_priv 查看视图权限
Create_routine_priv 创建存储过程、函数的权限
Alter_routine_priv 修改、删除存储过程、函数的权限
Create_user_priv 创建用户的权限
Event_priv 操作(创建、修改、删除)事件的权限
Trigger_priv 操作(创建、修改、删除)触发器的权限
Create_tablespace_priv 创建表空间的权限
ssl_type 加密
ssl_cipher 加密
x509_issuer 标识用户
x509_subject 标识用户
max_questions 每小时允许执行多少次查询,0表示没有限制
max_updates 每小时允许多少次更新
max_connections 每小时允许建立多少连接
max_user_connections 单个用户可以同时建立的连接数
plugin 认证插件
authentication_string 登录密码
password_expired 密码是否过期
password_last_changed 密码最近一次修改时间
password_lifetime 密码使用期限
account_locked 账户是否被锁定

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

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

2.2 mysql.db 表

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

字段名 字段解释
Host 主机名,其中:localhost 代表本地;% 代表不受限制;单独的 ip 代表某一台机器的访问权限
Db 数据库名
User 用户名
Select_priv SELECT 权限
Insert_priv INSERT 权限
Update_priv UPDATE 权限
Delete_priv DELETE 权限
Create_priv CREATE 权限
Drop_priv DROP 权限
Grant_priv 权限授予其他用户的权限
References_priv 参照表权限
Index_priv 索引操作(创建、删除)权限
Alter_priv ALTER 权限
Create_tmp_table_priv 创建临时表权限
Lock_tables_priv 执行lock table 权限
Create_view_priv 创建视图权限
Show_view_priv 查看视图权限
Create_routine_priv 创建存储过程、函数的权限
Alter_routine_priv 修改、删除存储过程、函数的权限
Execute_priv 执行存储过程的权限
Event_priv 操作(创建、修改、删除)事件的权限
Trigger_priv 操作(创建、修改、删除)触发器的权限

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

2.3 mysql.tables_priv 表

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

字段名 字段解释
Host 主机名,其中:localhost 代表本地;% 代表不受限制;单独的 ip 代表某一台机器的访问权限
Db 数据库名
User 用户名
Table_name 表名
Grantor 谁授予该用户的权限
Timestamp 授予权限的时间戳
Table_priv 授予表的哪些权限(例如:Select、Insert、Drop、Index 等等)
Column_priv 对表中的字段授予的权限(Select、Insert、Update、References)

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

2.4 mysql.columns_priv 表

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

字段名 字段解释
Host 主机名,其中:localhost 代表本地;% 代表不受限制;单独的 ip 代表某一台机器的访问权限
Db 数据库名
User 用户名
Table_name 表名
Column_name 列名
Timestamp 授予权限的时间戳
Column_priv 授予列的权限(Select、Insert、Update、References)

2.5 mysql.procs_priv 表

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

字段名 字段解释
Host 主机名,其中:localhost 代表本地;% 代表不受限制;单独的 ip 代表某一台机器的访问权限
Db 数据库名
User 用户名
Routine_name 程序名
Routine_type 程序类型(函数、存储过程)
Grantor 谁授予该用户的权限
Proc_priv 授予的权限(Execute、Alter Routine、Grant)
Timestamp 授予权限的时间戳

到这里,我们基本就把 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:如果你有任何问题,欢迎你在评论区进行留言,想看更多精品内容请点击这里

0人推荐
随时随地看视频
慕课网APP