【MySQL基础篇】十三、用户与权限管理

Ⅰ. 认识用户管理

1、概念

​ MySQL 中的用户管理是指通过创建、配置和管理用户帐户，控制用户对数据库服务器的访问权限。MySQL 数据库服务器允许管理员创建多个用户帐户，并对这些帐户的权限进行灵活配置，以确保数据库的安全性和数据的隐私。

​ 而如果我们只使用 root 用户，并且如果密码强度不大的话，是存在很大的安全隐患的。这时，就需要使用 MySQL 的用户管理。

2、mysql 中的用户信息

​ MySQL 中的用户，都存储在系统数据库 mysql 的 user 表中，如下所示：

​ 这其实我们在 linux 中对应的目录下也能找到：

​ 我们可以查看其中的一些字段：

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *8C19F9348EC7664CA2D94837E24FDAE7AA6730A6 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

• host：表示这个用户可以从哪个主机登陆，如果是 localhost，表示只能从本机登陆
• user：用户名
• authentication_string：用户密码通过 password() 函数加密后得到的
• *_priv：用户拥有的权限（上面没有查询，认识就行，这里 * 号表示通配符的意思）

Ⅱ. 用户的操作

​ 下面的用户操作，其实本质就是在上面所提到的 user 表中的增删改操作，但是我们还是更建议使用下面 mysql 提供的语句去增删改用户！

1、创建用户

​ root 可以使用 create user 语句创建新的用户账户。语法如下：

create user 用户名@主机名 identified by 密码;

​ 其中用户名和主机名中间的 @ 号是必须的，然后主机名可以是具体的 IP 地址或主机名，也可以使用通配符 %（表示任意主机，但是强烈不推荐使用！！！）。

​ 再强调一次，不要轻易添加一个可以从任意地方登陆的 user，也 很不建议暴露自己的公网 IP。

​ 可能实际在设置密码的时候，因为 mysql 本身的认证等级比较高，一些简单的密码无法设置，会爆出如下报错：

ERROR 1819 (HY000): Your password does not satisfy the current policyrequirements

​ 解决方案：https://blog.csdn.net/zhanaolu4821/article/details/93622812

​ 查看密码设置相关要求：show variables like 'validate_password%';

2、删除用户

​ 如果不再需要某个用户账户，管理员可以使用 drop user 语句删除该账户：

drop user '用户名'@'主机名';

3、修改密码

​ root 可以使用 alter user 语句来修改用户的密码：

alter user '用户名'@'主机名' identified BY 新密码;

​ 其实 root 也可以用 set 语句来进行设置用户的密码：

set password for '用户名'@'主机名' = password(新密码);

​ 如果是用户想修改自己的密码的话，则可以使用如下命令：

set password = password(新密码);

举例

mysql> create user liren@localhost identified by 'TTh0@514fs';  #增加用户
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,authentication_string from user;  #查询用户表
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *031970029E0C6366F4DDE6BAAC435F3AE9CF55C9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | liren         | *C3DB967086D8B256F0500C5D84572C276F28D711 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> drop user liren@localhost;		#删除用户
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,authentication_string from user;   #发现用户表中用户不见
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *031970029E0C6366F4DDE6BAAC435F3AE9CF55C9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> create user liren@localhost identified by 'TTh0@514fs';
Query OK, 0 rows affected (0.00 sec)

mysql> set password=password('T14520!5fs');		#修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

Ⅱ. 刷新指令 – flush privileges

​ flush privileges 是一个 MySQL 数据库管理命令，用于 重新加载权限表并使最新的权限更改生效。当在 MySQL 中修改了用户权限或角色权限，需要使用 flush privileges 命令来通知数据库重新加载这些更改，以便新的权限设置能够立即生效。

​ 通常情况下，当通过 grant 或 revoke 命令授予或撤销用户的权限时，MySQL 并不会立即生效这些更改，而是在适当的时机进行刷新。使用 flush privileges 命令可以手动触发权限表的刷新，以确保最新的权限设置生效。

​ 语法如下所示：

flush privileges;

​ 执行此命令后，MySQL 会重新加载权限表，并将最新的权限更改应用到正在运行的数据库会话中。

​ 需要注意的是，大多数情况下我们不需要频繁使用 flush privileges 命令。只有在更改了用户权限或角色权限后，才需要执行该命令以确保更改生效。在正常情况下，MySQL 会自动在适当的时机刷新权限表。

​ 此外，从 MySQL 8.0.16 版本开始， flush privileges 命令实际上不再是必需的，因为权限表会在更改权限时自动刷新。但为了确保一致性，执行 flush privileges 仍然是一个良好的实践。

Ⅲ. 认识权限管理

1、概念

​ MySQL 中的权限管理是一种机制，允许管理员控制用户或角色对数据库服务器和其中的数据的访问和操作权限。通过仔细配置权限，可以 确保只有授权用户能够执行特定的数据库操作，从而保障数据库的安全性和数据的隐私。

​ 以下是 MySQL 中的权限管理的一些关键方面：

1. 用户和角色： 在 MySQL 中，可以创建用户帐户并为其分配不同的权限。此外，自 MySQL 8.0 版本起，还引入了角色的概念，可以创建角色并分配给用户，从而更好地管理权限。
2. 权限级别： MySQL 的权限可以分为全局级别、数据库级别和表级别。这意味着管理员可以控制用户在整个数据库服务器、特定数据库或特定表上的操作权限。
3. 权限类型： MySQL 支持多种权限类型，包括 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、GRANT 等等。管理员可以根据需求为用户分配适当的权限，以便他们执行所需的操作。
4. 授权和撤销： 使用 grant 命令可以为用户或角色授予特定的权限，而使用 revoke 命令可以撤销已授予的权限。
5. 权限验证： MySQL 使用用户名、密码和主机信息进行身份验证。只有通过验证的用户才能连接到数据库服务器并执行操作。
6. 视图和存储过程权限： 用户可以被授予创建和使用视图、存储过程以及其他数据库对象的权限。
7. 权限继承： 角色可以继承其他角色的权限，从而简化权限管理和配置。

2、MySQL权限信息以及权限表

​ MySQL 数据库给一个合法用户赋予的权限分为五个级别：全局级(全局访问)、数据库级(指定数据库的访问)、表级(指定表的访问)、列级(指定列的访问)、存储过程和函数级(指定存储过程和函数的访问)，这些权限从全局到局部的级别依次递减。

​ MySQL 数据库中 权限级别的划分是通过系统权限表来实现的。系统中有 6 张权限表与 5 个权限级别相对应，分别为：user 表、db 表、host 表、tables_priv 表、columns_priv 表以及 procs_priv 表。

​ 这些表默认存放在叫做 mysql 的库中，该 6 个表的主要信息为：

1. user表：称为全局表，又被称为超级用户表，因为 默认情况下 root 用户具有所有的权限。其用于前面的登录验证外，还用于判定合法登录用户是否拥有数据库服务器的全局操作权限。
   • 该表包含下面几列：
     • 用户信息列（Host、User、Password）
     • 权限列（数据库操作中 29 个全局权限 29 列，通过 N/Y 断定用户是否有该权限）
     • 安全列 （ssl_type、ssl_cipher、x509_issuer、x509_subject）
     • 资源控制列（max_questions、max_updates、max_connections、max_user_connections）
2. db 表：是 针对某个数据库 的，它用来指定用户对某个指定的数据库中的对象具有哪些操作权限。
   • 该表包含下面几列：
     • 用户信息列（Host、Db、User）
     • 权限列（数据库级权限 19 列）
3. host表：作为 db 表一个辅助表，大部分情况下不用。
   • 如果想要用户能在从若干主机使用一个数据库，在用户的 db 表行的 Host 值设为空值，然后将那些主机的信息存入 host 表。host 表没有 user 字段，其他和 db 表一样。
4. tables_priv表：用来指定用户对某个表格 所具有的权限。
   • 该表包含下面几列：
     • 用户信息列（Host、Db、User、Table_name）
     • 权限列（表级权限 Table_priv、Column_priv 用户所拥有对该表的所有列级权限，如果该列为空，那么 columns_priv 表中用户就没有对该表的列级权限记录）
     • 其他列（授权时间 Timestamp、授权者 Grantor）
5. columns_priv表：用于指定用户对某个字段 所拥有的权限。
   • 该表包含下面几列：
     • 用户信息列（Host、Db、User、Table_name）
     • 权限列（column_priv 列级权限）
     • 其他列（授权时间 timestamp）
6. procs_priv表：该表用于指定用户对某一个单独的存储过程或函数。
   • 该表包含下面几列：
     • 用户信息列（Host、Db、User、Routine_name、Routine_type）
     • 权限列（Proc_priv 存储过程权限）
     • 其他列（授权时间 Timestamp、授权者 Grantor）

3、MySQL授权规则

​ MySQL 数据库授权过程由系统管理员以及权限转授者完成。系统拥有一套比较简单的授权管理规则：

• 规则一：管理员 root 用户或拥有全局 grant_priv 的用户可以将系统资源的任何权限授予任何用户，能从任何用户回收任何权限。
  • 如果用户拥有全局的 grant_priv 权限，用户就可以将全局/数据库级/表级/列级/存储过程级所有的权限授予他人，比如系统中的超级用户 root 就有全局的 grant_priv 权限，他就可能生成更多的超级用户。
  • 这样如果一个普通用户创建了自己的数据库或表并且包含一些机密信息，就很容易被超级授权者将权限授予其他人，容易泄漏用户的信息。
• 规则二：数据库对象（数据库/表）创建者，没有所创建对象的任何操作权限（存储过程或函数除外），必须通过 root 用户统一授权。
• 规则三：普通用户如果要将权限授予其他用户或回收该权限，必须拥有该权限的使用权以及转授权。

4、MySQL权限检查机制

​ MySQL 用户访问请求的处理也是根据用户所拥有的权限的级别来实现的。

​ 具体规则为：如果高优先级的表中用户具有所请求的操作权限，那么就无需查阅优先级较低的授权表了，但是如果高优先级的表中对应权限的值为 N，那么就需要进一步查看低优先级的授权表。

​ 也就是说，如果某个用户发送一个的某一个请求，将作如下处理：

1. 首先检查 user 表
   • 如果表中相应权限字段为 Y，则不用考虑其他四个表，访问接受；
   • 如果为 N 且请求的权限为全局级权限，访问拒绝；
   • 如果为 N 但请求的权限不是全局级权限，则需要继续查找 db 表。
2. 检查 db 表中用户所指定数据库的相应权限信息
   • 如果表中相应权限字段为 Y，不再考察低优先级表，访问接受；
   • 如果为 N 且请求的权限为数据库级权限，访问拒绝；如果为 N 但请求的权限不是数据库级权限，继续检查 tables_priv 表。
3. 检查 tables_priv 表中用户所指定表格的相应权限信息，查看用户指定表格记录的表级权限字段 Table_privs 是否包含该请求权限
   • 如果包含，访问接受
   • 如果没有，判断请求的权限是否为表级权限，
     • 如果请求的是列级权限，查看查看用户指定表格记录的表级权限字段 Column_privs 是否包含该请求权限
       • 如果有，继续检查columns_privs 表格
       • 如果没有，访问拒绝。
4. 检查 colums_priv 表，查看表中是否有对表格指定字段的请求操作权限
   • 如果有相关权限记录，访问接受
   • 如果没有访问拒绝。

Ⅳ. 权限的操作

1、查看用户现有权限

​ 方法一：使用 SHOW GRANTS 命令。

show grants for '用户名'@'主机名';

​ 方法二：使用 information_schema.USER_PRIVILEGES 视图

select * from information_schema.USER_PRIVILEGES where GRANTEE = "'用户名'@'主机名'";

​ 下面我们两种方式都采用一下，来举个例子：

mysql> select * from information_schema.USER_PRIVILEGES where GRANTEE = "'liren'@'localhost'";
+---------------------+---------------+----------------+--------------+
| GRANTEE             | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------------------+---------------+----------------+--------------+
| 'liren'@'localhost' | def           | USAGE          | NO           |
+---------------------+---------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> show grants for liren@localhost;
+-------------------------------------------+
| Grants for liren@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'liren'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

2、授予权限 – grant

​ 创建用户账户后，默认情况下该用户没有任何权限。管理员可以使用 grant 语句授予用户特定的权限，以允许其执行特定的数据库操作！

​ 此时可以通过下面语句进行授权：

grant 权限列表 on 库.对象名 to '用户名'@'主机名' [identified by '密码'];

说明：

权限列表中，多个权限用逗号分开，如下所示：

grant select on ...						-- 表示赋予查询权限
grant select, delete, create on ....	-- 表示赋予查询、删除、创建权限
grant all [privileges] on ... 			-- 表示赋予该用户在该对象上的所有权限

对于「库.对象名」来说：

• *.*：代表本系统中的所有数据库的所有对象（表、视图、存储过程等）
• 库.*：表示某个数据库中的所有数据对象（表、视图、存储过程等)

identified by 是可选项。 如果用户存在，赋予权限的同时修改密码；如果该用户不存在，就是创建用户。

​ 在授权的时候可以参考该图！

​ 注意，如果发现授予权限后，没有生效的话，可以执行前面讲过的刷新指令：

flush privileges;

3、回收权限 – revoke

​ 管理员可以使用 revoke 语句从用户账户中撤销特定的权限。语法如下所示：

revoke 权限列表 on 库.对象名 from '用户名'@'主机名';

​ 这里就不演示操作了，比较简单！