MySQL 认证和鉴权

峰顶风景 · 发表于 2023-1-17 13:10:36

这篇笔记主要介绍 MySQL 权限管理的基本概念和常用语句，包括：

什么是认证？
什么是鉴权？
创建、删除用户语句
授予、撤销用户权限语句
创建、删除角色语句
授予、撤销角色权限语句
授予、撤销用户角色语句

常用这些概念和语句之后，就掌握了 MySQL 权限管理的核心部分，结合官方文档基本可应付日常需求。
什么是认证？

认证就是决定用户是否可以连接 MySQL。
使用预先创建的用户名和密码，尝试连接（本地或远程） MySQL；MySQL 验证用户名和密码，如果用户名存在，且用户名和密码匹配，就表示认证成功，可以连接 MySQL；否则，拒绝连接。
MySQL 的用户名不是普通意义上的用户名，有两部分组成：

用户名称：普通意义上的用户名
机器名称：发起连接的机器名称，可以是 IP 地址或主机名，支持通配符（%）

用户名示例
&#39;david&#39;@&#39;localhost&#39;表示用户名为 david，发起连接的机器是本机。
&#39;david&#39;@&#39;198.51.100.109&#39;表示用户名为 david，发起连接的机器 IP 地址为 198.51.100.109。
&#39;david&#39;@&#39;198.51.100.%&#39;表示用户名为 david，发起连接的机器 IP 地址以 198.51.100 开头即可。
&#39;david&#39;@&#39;%&#39;表示用户名为 david，可以使用任意的机器发起连接。
要特别注意，
&#39;david&#39;@&#39;localhost&#39;和
&#39;david&#39;@&#39;remotehost&#39;即使它们的用户名称都是 david，但对于 MySQL 而言，它们是两个不同的用户名。
什么是鉴权？

鉴权就是决定用户连接 MySQL 之后可以执行哪些操作，用户只能执行自己有权限的操作。
操作可以划分为三个类型：

管理员操作创建用户、创建角色、终止数据库实例等全局性的操作。
数据库和数据库对象操作对某个数据库的增删查改操作，也包括对这个数据库中的表、索引或视图的增删查改操作。
数据库对象操作对某个表、索引或视图的增删查改操作。

数据库对象可以理解为数据库中的表、索引或视图等。

用户创建完成之后，还需要由 MySQL 管理员为该用户进行授权，然后这个用户才能连接 MySQL 并执行这些有权限的操作。
用户

创建用户

CREATE USER &#39;finley&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;password&#39;;
使用 CREATE USER 创建用户；其中，用户名为 finley，机器名为 localhost，密码为 password。
删除用户

DROP USER &#39;finley&#39;@&#39;localhost&#39;;
使用 DROP USER 删除用户。
查看所有用户

SELECT
user, host
FROM
mysql.user;

数据表 mysql.user 详情可参考 user。

权限

授予权限

使用 GRANT 为用户授予权限。
GRANT SELECT, INSERT, UPDATE, DELETE
ON customer.addresses
TO &#39;finley&#39;@&#39;localhost&#39;
WITH GRANT OPTION;
这条语句表示要把数据库 customer 的数据表 addresses 的查询（SELECT）、增加（INSERT）、更新（UPDATE）和删除（DELETE）权限授予给用户 &#39;finley&#39;@&#39;localhost&#39;，而且用户 &#39;finley&#39;@&#39;localhost&#39; 可以把这些权限授予给其他用户。
其中，
SELECT, INSERT, UPDATE, DELETE 是要授予的权限，多个权限之间使用英文逗号（,）分隔，可以使用 ALL 代表全部权限，详细的权限列表可参考 Privileges Provided by MySQL。
customer.addresses 是数据库名称和数据库对象名称，customer 是数据库名称，addresses 是数据库对象名称，两者均支持通配符（*），*.* 表示全部的数据库和数据库对象。
&#39;finley&#39;@&#39;localhost&#39; 是用户。
WITH GRANT OPTION 是可选项，表示授权完成之后，用户可以把已获得的这些权限授予给其他用户。
再看两个例子：
GRANT ALL ON *.* TO &#39;finley&#39;@&#39;localhost&#39; WITH GRANT OPTION;

GRANT ALL ON bankaccount.* TO &#39;custom&#39;@&#39;localhost&#39;;
撤销权限

使用 REVOKE 为用户撤销权限。
REVOKE INSERT, UPDATE, DELETE
ON customer.addresses
FROM &#39;finley&#39;@&#39;localhost&#39;;
这条语句表示撤销用户 &#39;finley&#39;@&#39;localhost&#39; 在数据库 customer 的数据表 addresses 上的增加（INSERT）、更新（UPDATE）和删除（DELETE）权限。其中，权限、数据库和数据库对象的使用同授予权限。
查看权限

SHOW GRANTS FOR &#39;finley&#39;@&#39;localhost&#39;;
查询用户 &#39;finley&#39;@&#39;localhost&#39; 的授权信息。
角色

MySQL 也支持使用角色来管理权限，主要分为两个步骤：

把权限授予角色；
把角色授予用户；

授予完成之后，用户就拥有了角色所拥有的权限。角色代表着一组权限，可以一次性地将多个权限打包授予给用户。
创建角色

使用 CREATE ROLE 创建角色，多个角色使用英文逗号（,）分隔。
CREATE ROLE &#39;app_developer&#39;, &#39;app_read&#39;, &#39;app_write&#39;;
删除角色

使用 DROP ROLE 删除角色，多个角色使用英文逗号（,）分隔。
DROP ROLE &#39;app_read&#39;, &#39;app_write&#39;;
授予/撤销角色权限

角色权限和用户权限的授予/撤销/查看语法是一样的。
授予角色权限
GRANT ALL ON app_db.* TO &#39;app_developer&#39;;
把数据库 app_db 的全部权限授予给角色 app_developer。
GRANT SELECT ON app_db.* TO &#39;app_read&#39;;
把数据库 app_db 的查询权限授予给角色 app_read。
GRANT INSERT, UPDATE, DELETE ON app_db.* TO &#39;app_write&#39;;
把数据库 app_write 的增加、更新和删除权限授予给角色 app_write。
撤销角色权限
REVOKE INSERT, UPDATE ON app_db.* FROM &#39;app_write&#39;;
从角色 app_write 中撤销数据库 app_db 的增加和更新权限。
授予/撤销用户角色

创建用户
CREATE USER &#39;dev1&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;dev1pass&#39;;

CREATE USER &#39;read_user1&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;read_user1pass&#39;;

CREATE USER &#39;read_user2&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;read_user2pass&#39;;

CREATE USER &#39;rw_user1&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;rw_user1pass&#39;;
创建用户 dev1@localhost、read_user1@localhost、read_user2@localhost 和 rw_user1@localhost。
授予用户角色
GRANT &#39;app_developer&#39; TO &#39;dev1&#39;@&#39;localhost&#39;;
把角色 app_developer 授予给用户 dev1@localhost 。
GRANT &#39;app_read&#39; TO &#39;read_user1&#39;@&#39;localhost&#39;, &#39;read_user2&#39;@&#39;localhost&#39;;
把角色 app_read 授予给用户 read_user1@localhost 和 read_user2@localhost。
GRANT &#39;app_read&#39;, &#39;app_write&#39; TO &#39;rw_user1&#39;@&#39;localhost&#39;;
把角色 app_read 和 app_write 授予给用户 rw_user1@localhost。

注意授予用户角色和授予用户权限的语法区别。

撤销用户角色
REVOKE &#39;app_write&#39; FROM &#39;rw_user1&#39;@&#39;localhost&#39;;
撤销用户 rw_user1@localhost 的角色 app_write。
结语

MySQL 的权限管理实质就是维护数据库（或数据库对象）的操作权限和用户或角色之间的对应关系。