MySQL 高级 - 第三章 | 用户、权限与角色管理

清疚 2024-05-06 23:21 11阅读 0赞

#### 目录 ####

*  一、用户管理
 *   *  1.1 用户登录
     *  1.2 查询用户
     *  1.3 创建用户
     *  1.4 修改用户
     *  1.5 删除用户
     *  1.6 修改密码
     *   *  1.6.1 设置当前用户密码
         *  1.6.2 修改其他用户密码
         *  1.6.3 降低密码校验规则
     *  1.7 密码管理
     *   *  1.7.1 密码过期策略
         *  1.7.2 密码重用策略
 *  二、权限管理
 *   *  2.1 权限列表
     *  2.2 查看权限
     *  2.3 授予权限
     *  2.4 收回权限
     *  2.5 分配权限的原则
 *  三、权限表
 *   *  3.1 user 表
     *  3.2 db 表
     *  3.3 tables\_priv
     *  3.4 columns\_priv
     *  3.5 procs\_priv
 *  四、访问控制
 *   *  4.1 连接核实阶段
     *  4.2 请求核实阶段
 *  五、角色管理
 *   *  5.1 创建角色
     *  5.2 赋予角色权限
     *  5.3 查看角色权限
     *  5.4 回收角色权限
     *  5.5 删除角色
     *  5.6 给用户赋予角色
     *  5.7 激活角色
     *  5.8 撤销用户角色
     *  5.9 设置强制角色

--------------------

上篇：[第二章、数据库目录结构与文件系统][Link 1]  
下篇：[第四章、配置文件与系统变量][Link 2]

本文内容主要源于：[bilibili-尚硅谷-MySQL高级篇][bilibili-_-MySQL]

## 一、用户管理 ##

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

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

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

--------------------

### 1.1 用户登录 ###

启动 `MySQL` 服务器之后可以通过 `mysql` 命令来登录 `MySQL` 服务器，命令如下：

mysql -h hostname | hostIP -P port -u username -p DatabaseName -e "SQL语句"

参数详情：

*  \-h：后面接主机名或者主机 `IP`，`hostname` 为主机，`hostIP` 为主机 `IP`
 *  \-P：后面接 `MySQL` 服务的端口，通过该参数连接到指定的端口，`MySQL` 服务的默认端口是 `3306`，不使用该参数时自动连接到 `3306` 端口，`port` 为连接的端口号
 *  \-u：后面接用户名，`username` 为用户名
 *  \-p：会提示输入密码
 *  DatabaseName ：指明登录到哪一个数据库中，如果没有该参数，就会直接登录到 `MySQL` 数据库中，然后使用 `use` 命令来选择数据库
 *  \-e：后面可以直接加 `sql` 语句，登录 `MySQL` 服务器以后即可执行这个 `SQL` 语句

示例：

mysql -h localhost -P 3306 -u root -p your_databaseName -e "show tables;"

![在这里插入图片描述][f61c1df6cd8841f6b45774014f9af043.png]

--------------------

### 1.2 查询用户 ###

在 `MySQL` 中可以通过查看 `mysql` 库中的 `user` 表来查询用户信息

# 切换到 mysql 库
    use mysql;
    # 查询用户表
    select * from user;
    # 或者竖式查询用户表
    select * from user\G;

示例：

在 `MySQL` 中，默认就存在以上 `4` 个用户

--------------------

### 1.3 创建用户 ###

在 `MySQL` 数据库中，官方推荐使用 `CREATE USER` 语句创建新用户，在 `MySQL-8.0` 版本移除了 `PASSWORD` 加密方法，因此不再推荐使用 `INSERT` 语句直接操作 `mysql` 库中的 `user` 表来增加用户。

使用 `CREATE USER` 语句来创建新用户时，必须拥有 `CREATE USER` 权限，每添加一个用户，`CREATE USER` 语句会在 `mysql` 库中的 `user` 表中添加一条新记录，但是新创建的账户没有任何权限，如果添加的账户已经存在，`CREATE USER` 语句就会返回一个错误。

`CREATE USER` 语句的基本语法形式如下：

CREATE USER 用户名 [IDENTIFIED BY '密码'] [, 用户名 [IDENTIFIED BY '密码']];

*  用户名参数表示新建用户的账户，由 `用户（User）` 和 `主机名（Host）` 构成
 *  `[ ]` 表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录，不过不指定密码不安全，不推荐使用，如果指定密码值，这里需要使用 `IDENTIFIED BY` 指定明文密码值
 *  `CREATE USER` 语句可以同时创建多个用户

示例：

创建好的用户 `midasha` 其 `host` 默认是 `%`，表示支持任何 `IP` 地址下的 `midasha` 用户进行连接

如果想要指定 `IP` 地址可以连接的话，需要在用户名后用 `@` 指明可连接的 `IP` 地址

示例：

用户表中的主键使用的 `host` \+ `user` 组合成的联合主键，如果创建的用户其 `host` \+ `user` 已存在，则会创建失败

示例：

mysql> CREATE USER 'caodali'@'localhost' IDENTIFIED BY 'caodali.8080';
    ERROR 1396 (HY000): Operation CREATE USER failed for 'caodali'@'localhost'

--------------------

### 1.4 修改用户 ###

可以通过 `UPDATE SET` 的 `SQL` 来修改用户表中用户名或者 `Host`

示例：

修改完之后并不会生效，还需要执行以下命令：

# 刷新权限
    FLUSH PRIVILEGES;

示例：

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

--------------------

### 1.5 删除用户 ###

在 `MySQL` 数据库中，可以使用 `DROP USER` 语句来删除 `普通用户`，也可以直接在 `mysql.user` 表中删除用户

*  方式一：使用 `DROP USER` 方式删除（推荐）

使用 `DROP USER` 语句来删除用户时，必须用于 `DROP USER` 权限，`DROP USER` 的基本语法如下：

DROP USER user[,user] ...;

其中，`user` 参数是需要删除的用户，由用户的 `用户名（User）` 和 `主机名（Host）` 组成，`DROP USER` 语句可以同时删除多个用户，各用户之间用逗号隔开，如果删除的用户 `userName` 未指定 `Host`，默认删除的为 `'userName'@'%'`

示例：

*  方式二：使用 `DELETE` 方式删除

可以使用 `DELETE` 语句直接将用户的信息从 `mysql.user` 表中删除，但必须拥有对 `mysql.user` 表的 `DELETE` 权限，`DELETE` 语句的基本语法形式如下：

示例：

mysql> DELETE FROM mysql.user WHERE user = 'caodali' and host = 'localhost';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

`host` 字段和 `user` 是 用户表 `mysql.user` 的联合主键，因此需要两个字段的值才能唯一确定一条记录

执行完删除语句之后要使用 `FLUSH` 命令才会生效：

# 刷新权限
    FLUSH PRIVILEGES;

示例：

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

> 注意：不推荐通过 `DELETE FROM mysql.user WHERE user = '用户名'` 这种方式进行删除，系统会有残留信息保留，而 `DROP USER` 命令会删除用户及对应的权限，执行命令之后 `mysql.user` 和 `mysql.db` 表中相应的记录都会消失

--------------------

### 1.6 修改密码 ###

--------------------

#### 1.6.1 设置当前用户密码 ####

适用于 `root` 用户修改自己的密码以及普通用户登录后修改自己的密码

`root` 用户拥有很高的权限，因此必须保证 `root` 用户的密码安全，`root` 用户可以通过多种方式来修改密码，使用 `ALTER USER` 修改用户密码是 `MySQL` 官方推荐的方式，也可以通过 `SET` 语句修改密码，由于 `MySQL-8.0` 中已移除了 `PASSWORD()` 函数，因此不再使用 `UPDATE` 语句直接操作用户表进行密码修改

*  方式一：使用 `ALTER USER` 命令修改当前用户密码

ALTER USER USER() IDENTIFIED BY 'new_password';

示例：

mysql> ALTER  USER()  IDENTIFIED BY 'duojiala.8080';
    Query OK, 0 rows affected (0.01 sec)

*  方式二：使用 `SET` 命令修改当前用户密码

使用 `root` 用户登录 `MySQL` 后，可以使用 `SET` 语句来修改密码，语法如下：

SET PASSWORD = 'new_password';

该语句会自动将密码加密后再赋给当前用户

在 `MySQL-5.7` 版本时也可以使用以下语句进行修改，但不推荐

SET PASSWORD = PASSWORD('new_password');

--------------------

#### 1.6.2 修改其他用户密码 ####

`root` 用户不仅可以修改自己的密码，还可以修改其它普通用户的密码，`root` 用户登录 `MySQL` 服务后，可以通过 `ALTER` 语句和 `SET` 语句来修改普通用户的密码，由于`MySQL-8.0` 中 `PASSWORD()` 函数已移除，因此也不再使用 `UPDATE` 去修改用户表的密码了

*  方式一：使用 `ALTER USER` 命令修改普通用户密码

ALTER USER user IDENTIFIED BY 'new_password' [, user IDENTIFIED BY 'new_password'] ...;

其中 `user` 表示用户的账号，由用户名和主机名构成，例如 `'username'@'hostname'`，`IDENTIFIED BY` 关键字用来设置密码

示例：

mysql> ALTER  USER  'zhangdaxian'@'%'  IDENTIFIED BY 'zhangdaxian.8080';
    Query OK, 0 rows affected (0.01 sec)

*  方式二：使用 `SET` 命令修改普通用户密码

使用 `root` 用户登录到 `MySQL` 服务器后，可以使用 `SET` 语句来修改普通用户的密码，`SET` 语句的语法如下：

SET PASSWORD FOR 'username'@'hostname' = 'new_password';

其中 `username` 参数是普通用户的用户名，`hostname` 参数是普通用户的主机名，`new_password` 是新密码

示例：

mysql> SET PASSWORD FOR 'zhangdaxian'@'%' = 'zhangdaxian.8080';
    Query OK, 0 rows affected (0.01 sec)

*  方式三：使用 `UPDATE` 语句修改普通用户密码（不推荐）

使用 `root` 用户登录 `MySQL` 服务器后，在 `MySQL-5.7` 以及之前的版本，可以使用 `UPDATE` 语句修改 `MySQL` 数据库的用户表中的 `password` 字段，从而修改普通用户的密码，语法如下：

# mysql 5.5 
    UPDATE mysql.user SET PASSWORD= PASSWORD('new_password') WHERE User = 'username';
    # mysql 5.7
    UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'username' and Host = 'hostname';

示例：

# mysql 5.5 
    mysql> UPDATE mysql.user SET PASSWORD= PASSWORD('duojiala.8080') WHERE User = 'duojiala';
    Query OK, 1 rows affected (0.01 sec)
    
    # mysql 5.7
    mysql> UPDATE mysql.user SET authentication_string = PASSWORD('duojiala.8080') WHERE User = 'duojiala' and Host = 'localhost';
    Query OK, 1 rows affected (0.01 sec)

修改完之后要使用 `FLUSH` 命令才会生效：

# 刷新权限
    FLUSH PRIVILEGES;

示例：

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

--------------------

#### 1.6.3 降低密码校验规则 ####

在 `MySQL-8.0` 中，如果你修改的密码过于简单，可能会提示 `Your password does not satisfy the current policy requirements`

![882727816bbc43a3b4f5e9684bd0e7f2.png][]

意思是您的密码不符合当前规定的要求，你要么就把你的密码设置得复杂点，要么就去降低密码的校验规则

在 `Linux` 上安装 `MySQL` 时会自动安装一个校验密码的插件，默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于 `8` 位。修改密码时新密码是否符合当前的策略，不满足则会提示 `ERROR`

[官网][Link 3] 上能查到这个密码校验的规则，文档中搜索：`validate_password`

![4656efb918e746748cef2f6def7adb28.png][]  
![76b02a02962d4a3fabfa298d230df72e.png][]  
![bc99307d65ca4364ab87bdc2d6b90f20.png][]

所以可以将这个限制密码位数设小一点，复杂度类型调底一点

降低密码的校验规则的方式：

# 将密码复杂度校验调整简单类型
    set global validate_password.policy = 0;
    # 设置密码最少位数限制为 4 位
    set global validate_password.length = 4;

示例：

![7b2a99a8df1b42b398eca06316c816a0.png][]

就可以设置较为简单的密码了

![6ed75dfd9f6740879fb5735803bfb959.png][]

不过 `MySQL` 还是建议使用高强度的密码，来保证数据库的安全

--------------------

### 1.7 密码管理 ###

`MySQL` 中记录使用过的历史密码，目前包含如下密码管理功能：

*  ① 密码过期：要求定期修改密码
 *  ② 密码重用限制：不允许使用旧密码
 *  ③ 密码强度评估：要求使用高强度的密码

> 提示：`MySQL` 密码管理功能只针对使用基于 `MySQL` 授权插件的账号，这些插件有 `mysql_native_password` 、`sha256_password` 和 `caching_sha_password`。

--------------------

#### 1.7.1 密码过期策略 ####

在 `MySQL` 中，数据库管理员可以 `手动设置` 账号密码过期，也可以建立一个 `自动` 密码过期策略，过期策略可以是 `全局的`，也可以为 `每个账号` 设置单独过期策略

**（1）手动设置立马过期**

手动设置账号密码过期，可使用以下语法：

ALTER USER user PASSWORD EXPIRE;

示例：

mysql> ALTER USER 'duojiala'@'localhost' PASSWORD EXPIRE;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW DATABASES;
    ERROR 1820 (HY000): You must reset your password using ALTER USER statemnet before executing this statement.

该语句将用户 `'duojiala'@'localhost'` 的密码设置过期，该用户仍然可以登录进入数据库，但无法进行查询，密码过期后，只有重新设置了新密码，才能正常使用

**（2）手动设置指定时间过期方式：全局**

如果密码使用的时间大于允许的时间，服务器会自动设置为过期，不需要手动设置

`MySQL` 使用 `default_password_lifetime` 系统变量建立全局密码过期策略

*  它的默认值是 `0`，表示禁用自动密码过期
 *  它允许的值是整数 `N`，表示允许的密码生存期，密码必须 `每隔 N 天` 进行修改

实现方式有以下两种：

*  方式一：使用 `SQL` 语句更改该变量的值并持久化

语法如下：

# 建立全局策略，设置密码每隔 180 天过期
    SET PERSIST default_password_lifetime = 180;

示例：

mysql> SET PERSIST default_password_lifetime = 180;
    Query OK, 0 rows affected (0.00 sec)

*  方式二：配置文件 `my.cnf` 中进行维护

在 `MySQL` 的配置文件 `my.cnf` 中添加以下配置：

[mysqld]
    # 建立全局策略，设置密码每隔 180 天过期
    default_password_lifetime = 180;

更改配置文件需要重启 `MySQL` 服务，配置才能生效

**（3）手动设置指定时间过期方式：单独设置**

每个账号既可延用全局密码过期策略，也可单独设置策略，在 `CREATE USER` 和 `ALTER USER` 语句上假如 `PASSWORD EXPIRE` 选项可实现单独设置策略

示例：

# 设置 duojiala 账号密码每 90 天过期
    CREATE USER 'duojiala'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
    ALTER USER 'duojiala'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
    
    # 设置密码永不过期
    CREATE USER 'duojiala'@'localhost' PASSWORD EXPIRE NEVER;
    ALTER USER 'duojiala'@'localhost' PASSWORD EXPIRE NEVER;
    
    # 延用全局密码过期策略
    CREATE USER 'duojiala'@'localhost' PASSWORD EXPIRE DEFAULT;
    ALTER USER 'duojiala'@'localhost' PASSWORD EXPIRE DEFAULT;

--------------------

#### 1.7.2 密码重用策略 ####

`MySQL` 限制使用已用过的密码，重用限制策略基于 `密码更改的数量` 和 `使用的时间`，重用策略可以是 `全局` 的，也可以为每个账号设置 `单独的策略`。

账号的历史密码包含过去该账号所使用的密码，`MySQL` 基于以下规则来限制密码重用：

*  如果账号的密码限制 `基于密码更改的数量`，那么新密码不能从最近限制的密码数量中选择，例如：如果密码更改的最小值为 `3`，那么新密码不能与最近 `3` 个密码中任何一个相同
 *  如果账号密码限制 `基于时间`，那么新密码不能从规定时间内选择，例如：如果密码重用周期为 `60` 天，那么新密码不能从最近 `60` 天内使用的密码中选择

`MySQL` 使用 `password_history` 和 `password_reuse_interval` 系统变量设置密码重用策略

*  `password_history`：规定密码重用的数量
 *  `password_reuse_interval`：规定密码重用的周期

这两个值可在 `服务器的配置文件` 中进行维护，也可在运行期间 `使用 SQL 语句更改` 该变量的值并持久化

**（1）手动设置密码重用方式：全局**

*  方式一：使用 SQL 语句更改

语法如下：

# 设置不能选择最近使用过的 6 个密码
    SET PERSIST password_history = 6;
    
    # 设置不能选择最近一年内的密码
    SET PERSIST password_reuse_interval = 365;

示例：

mysql> SET PERSIST password_history = 6;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET PERSIST password_reuse_interval = 365;
    Query OK, 0 rows affected (0.00 sec)

*  方式二：修改 `my.cnf` 配置文件

[mysqld]
    # 设置不能选择最近使用过的 6 个密码
    password_history = 6;
    # 设置不能选择最近一年内的密码
    password_reuse_interval = 365;

更改配置文件需要重启 `MySQL` 服务，配置才能生效

**（2）手动设置密码重用方式：单独设置**

每个账号可以延用全局密码重用策略，也可单独设置策略，这两个选项可以单独使用，也可以结合在一起使用

示例：

# 不能使用最近 5 个密码
    CREATE USER 'duojiala'@'localhost' PASSWORD HISTORY 5;
    ALTER USER 'duojiala'@'localhost' PASSWORD HISTORY 5;
    
    # 不能使用最近 365 天内的密码
    CREATE USER 'duojiala'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
    ALTER USER 'duojiala'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
    
    # 既不能使用最近 5 个密码，也不能使用 365 天内的密码
    CREATE USER 'duojiala'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;
    ALTER USER 'duojiala'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;
    
    # 延用全局策略
    CREATE USER 'duojiala'@'localhost' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
    ALTER USER 'duojiala'@'localhost' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;

--------------------

## 二、权限管理 ##

关于 `MySQL` 的权限简单的理解就是 `MySQL` 允许你做你权力以内的事情，不可以越界，比如：只允许你执行 `SELECT` 操作，那么你就不能执行 `UPDATE` 操作，只允许你从某台机器上连接 `MySQL`，那么你就不能从其它机器上连接 `MySQL`。

--------------------

### 2.1 权限列表 ###

在 `MySQL` 中可以通过以下命令查看权限：

SHOW PRIVILEGES;

示例：

`CRANT` 和 `REVOKE` 语句中可以使用的权限如下：

<table> 
 <thead> 
  <tr> 
   <th align="left">权限</th> 
   <th align="left">user 表中对应的列</th> 
   <th align="left">权限的范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">CREATE</td> 
   <td align="left">Create_priv</td> 
   <td align="left">数据库、表或索引</td> 
  </tr> 
  <tr> 
   <td align="left">DROP</td> 
   <td align="left">Drop_priv</td> 
   <td align="left">数据库、表或视图</td> 
  </tr> 
  <tr> 
   <td align="left">GRANT OPTION</td> 
   <td align="left">Grant_priv</td> 
   <td align="left">数据库、表或存储过程</td> 
  </tr> 
  <tr> 
   <td align="left">REFERENCES</td> 
   <td align="left">References_priv</td> 
   <td align="left">数据库或表</td> 
  </tr> 
  <tr> 
   <td align="left">EVENT</td> 
   <td align="left">Event_priv</td> 
   <td align="left">数据库</td> 
  </tr> 
  <tr> 
   <td align="left">ALTER</td> 
   <td align="left">Alter_priv</td> 
   <td align="left">数据库</td> 
  </tr> 
  <tr> 
   <td align="left">DELETE</td> 
   <td align="left">Delete_priv</td> 
   <td align="left">表</td> 
  </tr> 
  <tr> 
   <td align="left">INDEX</td> 
   <td align="left">Index_priv</td> 
   <td align="left">表</td> 
  </tr> 
  <tr> 
   <td align="left">INSERT</td> 
   <td align="left">Insert_priv</td> 
   <td align="left">表</td> 
  </tr> 
  <tr> 
   <td align="left">SELECT</td> 
   <td align="left">Select_priv</td> 
   <td align="left">表或列</td> 
  </tr> 
  <tr> 
   <td align="left">UPDATE</td> 
   <td align="left">Update_priv</td> 
   <td align="left">表或列</td> 
  </tr> 
  <tr> 
   <td align="left">CREATE TEMPORARY TABLES</td> 
   <td align="left">Create_temp_table_priv</td> 
   <td align="left">表</td> 
  </tr> 
  <tr> 
   <td align="left">LOCK TABLES</td> 
   <td align="left">Lock_tables_priv</td> 
   <td align="left">表</td> 
  </tr> 
  <tr> 
   <td align="left">TRIGGER</td> 
   <td align="left">Trigger_priv</td> 
   <td align="left">表</td> 
  </tr> 
  <tr> 
   <td align="left">CREATE VIEW</td> 
   <td align="left">Create_view_priv</td> 
   <td align="left">视图</td> 
  </tr> 
  <tr> 
   <td align="left">SHOW VIEW</td> 
   <td align="left">Show_view_priv</td> 
   <td align="left">视图</td> 
  </tr> 
  <tr> 
   <td align="left">ALTER ROUTINE</td> 
   <td align="left">Alter_routine_priv</td> 
   <td align="left">存储过程和函数</td> 
  </tr> 
  <tr> 
   <td align="left">CREATE ROUTINE</td> 
   <td align="left">Create_routine_priv</td> 
   <td align="left">存储过程和函数</td> 
  </tr> 
  <tr> 
   <td align="left">EXECUTE</td> 
   <td align="left">execute_priv</td> 
   <td align="left">存储过程和函数</td> 
  </tr> 
  <tr> 
   <td align="left">FILE</td> 
   <td align="left">Flie_priv</td> 
   <td align="left">访问服务器上的文件</td> 
  </tr> 
  <tr> 
   <td align="left">CREATE TABLESPACE</td> 
   <td align="left">Create_tablespace_priv</td> 
   <td align="left">服务器管理</td> 
  </tr> 
  <tr> 
   <td align="left">CREATE USER</td> 
   <td align="left">Create_user_priv</td> 
   <td align="left">服务器管理</td> 
  </tr> 
  <tr> 
   <td align="left">PROCESS</td> 
   <td align="left">Process_priv</td> 
   <td align="left">存储过程和函数</td> 
  </tr> 
  <tr> 
   <td align="left">RELOAD</td> 
   <td align="left">Reload_priv</td> 
   <td align="left">访问服务器上的文件</td> 
  </tr> 
  <tr> 
   <td align="left">REPLICATION CLIENT</td> 
   <td align="left">Repl_client_priv</td> 
   <td align="left">服务器管理</td> 
  </tr> 
  <tr> 
   <td align="left">REPLICATION SLAVE</td> 
   <td align="left">Repl_slave_priv</td> 
   <td align="left">服务器管理</td> 
  </tr> 
  <tr> 
   <td align="left">SHOW DATABASES</td> 
   <td align="left">Show_db_priv</td> 
   <td align="left">服务器管理</td> 
  </tr> 
  <tr> 
   <td align="left">SHUTDOWN</td> 
   <td align="left">Shutdown_priv</td> 
   <td align="left">服务器管理</td> 
  </tr> 
  <tr> 
   <td align="left">SUPER</td> 
   <td align="left">Super_priv</td> 
   <td align="left">服务器管理</td> 
  </tr> 
 </tbody> 
</table>

*  CREATE 和 DROP 权限：可以创建新的数据库和表，或删除已有的数据库和表，如果将 `MySQL` 数据库中的 `DROP` 权限授予某用户，用户就可以删除 `MySQL` 访问权限保存的数据库
 *  SELECT、INSERT、UPDATE 和 DELETE 权限：允许在一个数据库现有的表上实施操作
 *  SELECT 权限：只有在它们真正从一个表中检索行时才被用到
 *  INDEX 权限：允许创建或删除索引，`INDEX` 适用于已有的表，如果具有某个表的 `CREATE` 权限，就可以在 `CREATE TABLE` 语句中包括索引定义
 *  ALTER 权限：可以使用 `ALTER TABLE` 来更改表的结构和重新命名表
 *  CREATE ROUTINE 权限：用来创建保存的程序和函数，`ALTER ROUTINE` 权限用来更改和删除保存的程序
 *  EXECUTE 权限：用来执行保存的程序
 *  GRANT 权限：允许授予给其他用户，可用于数据库、表和保存的程序
 *  FILE 权限：使用户可以使用 `LOAD DATA INFILE` 和 `SELECT ... INTO OUTFILE` 语句读或写服务器上的文件，任何被授予 `FILE` 权限的用户都能读或写 `MySQL` 服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）

`MySQL` 的权限分布：

<table> 
 <thead> 
  <tr> 
   <th align="left">权限分布</th> 
   <th align="left">可设置的权限</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">表权限</td> 
   <td align="left">SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、GRANT、REFERENCES、INDEX、ALTER</td> 
  </tr> 
  <tr> 
   <td align="left">列权限</td> 
   <td align="left">SELECT、INSERT、UPDATE、REFERENCES</td> 
  </tr> 
  <tr> 
   <td align="left">过程权限</td> 
   <td align="left">EXECUTE、ALTER ROUTINE，GRANT</td> 
  </tr> 
 </tbody> 
</table>

--------------------

### 2.2 查看权限 ###

*  查看当前用户权限

SHOW GRANTS;
    # 或
    SHOW GRANTS FOR CURRENT_USER;
    # 或
    SHOW GRANTS FOR CURRENT_USER();

示例：

mysql> SHOW GRANTS;
    +-------------------------------------+
    | Grants for midasha@%                |
    +-------------------------------------+
    | GRANT USAGE ON *.* TO `midasha`@`%` |
    +-------------------------------------+
    1 row in set (0.00 sec)

*  查看某用户的全局权限

SHOW GRANTS FOR 'username'@'host';

示例：

mysql> SHOW GRANTS FOR 'midasha'@'%';
    +-------------------------------------+
    | Grants for midasha@%                |
    +-------------------------------------+
    | GRANT USAGE ON *.* TO `midasha`@`%` |
    +-------------------------------------+
    1 row in set (0.00 sec)

--------------------

### 2.3 授予权限 ###

给用户授权的方式有 `2` 种，分别是通过把 `角色赋予用户给用户授权` 和 `直接给用户授权`。用户是数据库的使用者。可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。

授权命令：

GRANT 权限1,权限2,...权限n ON 数据库名称.表名称 TO '用户名'@'用户地址' [IDENTIFIED BY '密码'];

*  `ON` 用来指定权限针对哪些库和表
 *  `.` 前面指定数据库名称，`.` 后面指定表名称，`*` 表示全部
 *  `TO` 表示将权限赋予某个用户
 *  `@` 前指定用户名，后面指定限制的主机，主机可以是 `IP`、`IP` 段、域名以及 `%`，`%` 表示任何地方（注意：`%` 有的版本不包括本地）
 *  `IDENTIFIED BY` 指定用户的登录密码

该权限如果发现没有该用户，则会直接新建一个用户

示例：

# 给用户 duojiala 授予在 mike_inner 库下的所有表增删改查的权限
    GRANT SELECT,INSERT,DELETE,UPDATE ON mike_inner.* TO 'duojiala'@'localhost';
    
    # 授予通过网络方式登录的用户 duojiala，对所有库所有表的全部权限，密码设置为 duojiala.8080（注意：这里不包含 GRANT 权限）
    GRANT ALL PRIVILEGES ON *.* TO 'duojiala'@'%' IDENTIFIED BY 'duojiala.8080';

`ALL PRIVILEGES` 是表示所有权限，也可以使用 `SELECT`、`UPDATE` 等权限

如果需要赋予包括 `GRANT` 在内的权限，需添加参数 `WITH GRANT OPTION` 这个选项即可，否则该用户无法使用 `GRANT` 给其他用户授权

示例：

# 授予通过网络方式登录的用户 duojiala，对所有库所有表的全部权限，包含 GRANT 权限
    GRANT ALL PRIVILEGES ON *.* TO 'duojiala'@'%' WITH GRANT OPTION;

使用 `GRANT` 命令赋予权限并不会将原有的权限覆盖掉，而是在原有的权限上再新增权限，也就是 `权限叠加`，比如先给用户添加了一个 `SELECT` 权限，然后又给用户添加了一个 `INSERT` 权限，那么该用户就同时拥有了 `SELECT` 和 `INSERT` 权限

> 在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的分组
> 
>  *  所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据
>  *  所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改、甚至是删除

--------------------

### 2.4 收回权限 ###

收回权限就是取消已经赋予用户的某些权限，收货用户不必要的权限可以在一定程度上保证系统的安全性

`MySQL` 中使用 `REVOKE` 语句取消用户的某些权限，使用 `REVOKE` 收回权限之后，用户账户的记录将从 `db`、`host`、`tables_priv` 和 `columns_priv` 表中删除，但是用户账户记录仍然在 `user` 表中保存（删除 `user` 表中的账户记录使用 `DROP USER` 语句）

注意：在将用户从 `user` 表中删除之前，应该回收相应用户的所有权限

语法：

REVOKE 权限1,权限2,...权限n ON 数据库名称.表名称 FROM '用户名'@'用户地址';

示例：

# 收回全库全表的所有权限
    REVOKE ALL PRIVILEGES ON *.* FROM 'duojiala'@'%';
    
    # 收回 mysql 库下的所有表的增删改权限
    REVOKE INSERT,DELETE,UPDATE ON mike_inner.* FROM 'duojiala'@'localhost';

注意：须用户重新登录后才能生效

--------------------

### 2.5 分配权限的原则 ###

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：

*  ① 只授予 `满足需要的最小权限`，防止用户对数据库产生不安全的操作，例如：用户只是需要查询，那就只给 `SELECT` 权限就可以了，不要赋予用户 `INSERT`、`UPDATE` 或者 `DELETE` 等权限
 *  ② 创建用户的时候 `限制用户的登录主机`，一般是限制成指定 `IP` 或者内网 `IP` 段
 *  ③ 为每个用户 `设置满足密码复杂度的密码`
 *  ④ `定期清理不需要的用户`，回收权限或者删除用户

> 有一些程序员喜欢使用 `root` 超级用户来访问数据库，完全把 `权限控制` 放在 `应用层面` 实现，这样虽然是可行的，但不建议，因为 `root` 账户密码一旦泄露，数据库就会完全失去保护，所以尽量使用数据库自己的角色和用户机制来控制访问权限，不要轻易使用 `root` 账户。

--------------------

## 三、权限表 ##

`MySQL` 服务器通过 `权限表` 来 `控制用户对数据库的访问`，权限表存放在 `mysql` 库中，`MySQL` 数据库系统会根据这些权限的内容为每个用户赋予相应的权限，这些权限表中最重要的是 `user` 表、`db` 表，除此之外，还有 `table_priv` 表、`colum_priv` 表和 `proc_priv` 表等，在 `MySQL` 启动时，服务器将这些数据库表中权限信息的内容读入内存。

`mysql` 数据库中的所有表：

<table> 
 <thead> 
  <tr> 
   <th align="left">表名</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">user</td> 
   <td align="left">用户账号及权限信息</td> 
  </tr> 
  <tr> 
   <td align="left">global_grants</td> 
   <td align="left">动态全局授权</td> 
  </tr> 
  <tr> 
   <td align="left">db</td> 
   <td align="left">数据库层级的权限</td> 
  </tr> 
  <tr> 
   <td align="left">tables_priv</td> 
   <td align="left">表层级的权限</td> 
  </tr> 
  <tr> 
   <td align="left">columns_priv</td> 
   <td align="left">列层级的权限</td> 
  </tr> 
  <tr> 
   <td align="left">procs_priv</td> 
   <td align="left">存储的过程和函数权限</td> 
  </tr> 
  <tr> 
   <td align="left">proxies_priv</td> 
   <td align="left">代理用户的权限</td> 
  </tr> 
  <tr> 
   <td align="left">default_roles</td> 
   <td align="left">账号连接并认证后默认授予的角色</td> 
  </tr> 
  <tr> 
   <td align="left">role_edges</td> 
   <td align="left">角色子图的边界</td> 
  </tr> 
  <tr> 
   <td align="left">password_history</td> 
   <td align="left">密码更改信息</td> 
  </tr> 
 </tbody> 
</table>

查询 `table_name` 表字段：

DESC table_name;

以列的方式显示 `table_name` 表数据：

SELECT * FROM table_name \G;

--------------------

### 3.1 user 表 ###

`user` 表是 `MySQL` 中最重要得一个权限表，记录用户账号和权限信息，字段如下：

mysql> DESC mysql.user;
    +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
    | Field                    | Type                              | Null | Key | Default               | Extra |
    +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
    | Host                     | char(255)                         | NO   | PRI |                       |       |
    | User                     | char(32)                          | NO   | PRI |                       |       |
    | Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
    | Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
    | Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
    | Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
    | References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
    | Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
    | Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
    | Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
    | Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
    | Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
    | Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
    | Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
    | Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
    | Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
    | Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
    | ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
    | ssl_cipher               | blob                              | NO   |     | NULL                  |       |
    | x509_issuer              | blob                              | NO   |     | NULL                  |       |
    | x509_subject             | blob                              | NO   |     | NULL                  |       |
    | max_questions            | int unsigned                      | NO   |     | 0                     |       |
    | max_updates              | int unsigned                      | NO   |     | 0                     |       |
    | max_connections          | int unsigned                      | NO   |     | 0                     |       |
    | max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
    | plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
    | authentication_string    | text                              | YES  |     | NULL                  |       |
    | password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
    | password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
    | password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
    | account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
    | Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
    | Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
    | Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
    | Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
    | User_attributes          | json                              | YES  |     | NULL                  |       |
    +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
    51 rows in set (0.00 sec)

这些字段可以分成 `4` 类，分别是范围列（或用户列）、权限列、安全列和资源控制列

**1. 范围列（或用户列）**

`user` 表的用户列包括 `User`、`Host`、`authentication_string`，分别表示用户名、主机名和密码。`User` 指明允许访问的用户名，`Host` 指明允许访问的 `IP` 或主机范围，并且 `User` 和 `Host` 是联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配 `user` 表中对应的字段，只有 `3` 个值都匹配的时候，才允许连接的建立。这 `3` 个字段的值就是创建账户时保存的账户信息，修改用户密码时，实际就是修改 `user` 表的 `authentication_string` 字段的值

*  `User`：表示用户名，同一用户通过不同方式连接的权限是不一样的
 *  `Host`：表示连接类型
    
     *  `%`：表示所有远程通过 `TCP` 方式的连接
     *  `IP 地址`：如 `192.168.1.2` 、`127.0.0.1` 通过指定 `IP` 地址进行的 `TCP` 方式的连接
     *  `机器名`：通过指定网络中的机器名进行的 `TCP` 方式的连接
     *  `::1`：`IPv6` 的本地 `IP` 地址，等同于 `IPv4` 的 `127.0.0.1`
     *  `localhost`：本地方式通过命令行方式的连接，例如：`mysql -u root -p password` 方式的连接
 *  `password/authentication_string`：密码
    
     *  所有密码串通过 `password`（明文字符串）生成的密文字符串，`MySQL-8.0` 在用户管理方面增加了角色管理，默认的密码加密方式也做了调整，由之前的 `SHA1` 改为了 `SHA2`，不可逆，同时加上 `MySQL-5.7` 的禁用用户和用户过期的权限，`MySQL` 在用户管理方面的功能和安全性都较之前版本大大的增强了
     *  `MySQL-5.7` 及之后版本的密码保存到 `authentication_string` 字段中，不再使用 `password` 字段

**2. 权限列**

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

`user` 表中对应的权限是针对所有用户数据库的，这些字段的类型为 `enum`，可以取的值只能为 `Y` 和 `N`，`Y` 表示该用户拥有对应的权限，`N` 表示用户没有对应的权限。从 `user` 表的结构可以看到，这些字段的值默认都是 `N`，如果要修改权限，就可以使用 `GRANT` 语句或 `UPDATE` 语句更改 `user` 表的这些字段来修改用户对应的权限。

*  `Grant_priv`：表示是否拥有 `GRANT` 权限
 *  `Shutdown_priv`：表示是否拥有停止 `MySQL` 服务的权限
 *  `Super_priv`：表示是否拥有超级权限
 *  `Execute_priv`：表示是否拥有 `EXECUTE` 权限，拥有 `EXECUTE` 权限可以执行存储过程和函数
 *  `Select_priv，Insert_priv`：为该用户所拥有的权限

**3. 安全列**

安全列只有 `6` 个字段，其中两个是 `ssl` 相关的（`ssl_type`，`ssl_cipher`），用于 `加密`；两个是 `x509` 相关的（`x509_issuer`，`x509_subject`），用于 `标识用户`；另外两个 `Plugin` 字段用于 `验证用户身份` 的插件，该字段不能为空，如果该字段为空，服务器就使用内建授权验证用户身份。

**4. 资源控制列**

资源控制列的字段用来 `限制用户使用的资源`，包含 `4` 个字段，分别为：

*  `max_questions`：用户每小时允许执行的查询操作次数
 *  `max_updates`：用户每小时允许执行的更新操作次数
 *  `max_connections`：用户每小时允许执行的连接操作次数
 *  `max_user_connections`：用户允许同时建立的连接次数

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

--------------------

### 3.2 db 表 ###

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

`user` 表中的权限是针对所有数据库的，如果 `user` 表中的 `Select_priv` 字段为 `Y`，那么该用户可以查询所有数据库中的表，如果希望用户只对某个数据库有操作权限，那么需要将 `user` 表中对应的权限设置为 `N`，然后在 `db` 表中设置对应数据库的操作权限。由此可见，用户先根据 `user` 表的内容获取权限，然后根据 `db` 表的内容获取权限

`db` 表中字段如下：

mysql> DESC mysql.db;
    +-----------------------+---------------+------+-----+---------+-------+
    | Field                 | Type          | Null | Key | Default | Extra |
    +-----------------------+---------------+------+-----+---------+-------+
    | Host                  | char(255)     | NO   | PRI |         |       |
    | Db                    | char(64)      | NO   | PRI |         |       |
    | User                  | char(32)      | NO   | PRI |         |       |
    | Select_priv           | enum('N','Y') | NO   |     | N       |       |
    | Insert_priv           | enum('N','Y') | NO   |     | N       |       |
    | Update_priv           | enum('N','Y') | NO   |     | N       |       |
    | Delete_priv           | enum('N','Y') | NO   |     | N       |       |
    | Create_priv           | enum('N','Y') | NO   |     | N       |       |
    | Drop_priv             | enum('N','Y') | NO   |     | N       |       |
    | Grant_priv            | enum('N','Y') | NO   |     | N       |       |
    | References_priv       | enum('N','Y') | NO   |     | N       |       |
    | Index_priv            | enum('N','Y') | NO   |     | N       |       |
    | Alter_priv            | enum('N','Y') | NO   |     | N       |       |
    | Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
    | Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
    | Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
    | Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
    | Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
    | Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
    | Execute_priv          | enum('N','Y') | NO   |     | N       |       |
    | Event_priv            | enum('N','Y') | NO   |     | N       |       |
    | Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
    +-----------------------+---------------+------+-----+---------+-------+
    22 rows in set (0.01 sec)

`db` 表的字段大致可分为两类，分别为用户列和权限列

**1. 用户列**

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

**2. 权限列**

以 `_priv` 结尾的都是权限列，`Create_routine_priv` 和 `Alter_routine_priv` 这两个字段决定用户是否具有创建和修改存储过程的权限。

--------------------

### 3.3 tables\_priv ###

`tables_priv` 表用来 `对表设置操作权限`，字段如下：

`tables_priv` 表有 `8` 个字段，各字段说明如下：

*  `User、Host、Db 和 Table_name`：这四个字段分别表示用户名、主机名、数据库名和表名
 *  `Grantor`：表示修改该记录的用户
 *  `Timestamp`：表示修改该记录的时间
 *  `Table_priv`：表示对象的操作权限
 *  `Column_priv`：表示表中列的操作权限

--------------------

### 3.4 columns\_priv ###

`columns_priv` 表用来 `对表的某一列设置操作权限`，字段如下：

mysql> DESC mysql.columns_priv;
    +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
    | Field       | Type                                         | Null | Key | Default           | Extra                                         |
    +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
    | Host        | char(255)                                    | NO   | PRI |                   |                                               |
    | Db          | char(64)                                     | NO   | PRI |                   |                                               |
    | User        | char(32)                                     | NO   | PRI |                   |                                               |
    | Table_name  | char(64)                                     | NO   | PRI |                   |                                               |
    | Column_name | char(64)                                     | NO   | PRI |                   |                                               |
    | Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
    | Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                                               |
    +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
    7 rows in set (0.00 sec)

`columns_priv` 表有 `7` 个字段，各字段说明如下：

*  `User、Host、Db 和 Table_name`：这四个字段分别表示用户名、主机名、数据库名和表名
 *  `Timestamp`：表示修改该记录的时间
 *  `Column_name`：表示所指定的列名
 *  `Column_priv`：表示表中列的操作权限

--------------------

### 3.5 procs\_priv ###

`procs_priv` 表可以对 `存储过程和存储函数设置操作权限`，字段如下：

mysql> DESC mysql.procs_priv;
    +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
    | Field        | Type                                   | Null | Key | Default           | Extra                                         |
    +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
    | Host         | char(255)                              | NO   | PRI |                   |                                               |
    | Db           | char(64)                               | NO   | PRI |                   |                                               |
    | User         | char(32)                               | NO   | PRI |                   |                                               |
    | Routine_name | char(64)                               | NO   | PRI |                   |                                               |
    | Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                                               |
    | Grantor      | varchar(288)                           | NO   | MUL |                   |                                               |
    | Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                                               |
    | Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
    +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
    8 rows in set (0.00 sec)

`procs_priv` 表有 `8` 个字段，各字段说明如下：

*  `User、Host 和 Db`：这三个字段分别表示用户名、主机名、数据库名
 *  `Routine_name`：表示存储过程或函数的名称
 *  `Routine_type`：表示存储过程或函数的类型，`FUNCTION` 表示 `存储函数`，`PROCEDURE` 表示 `存储过程`
 *  `Grantor`：表示修改该记录的用户
 *  `Proc_priv`：表示拥有的权限
 *  `Timestamp`：表示修改该记录的时间

--------------------

## 四、访问控制 ##

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

--------------------

### 4.1 连接核实阶段 ###

当用户试图连接 `MySQL` 服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接收或者拒绝连接，即客户端用户会在连接请求中提供用户名、主机地址、用户密码，`MySQL` 服务器接收到用户请求后，会使用 `user` 表中的 `Host`、`User` 和 `authentication_string` 这 `3` 个字段匹配客户端提供的信息。

服务器只有在 `user` 表记录的 `Host` 和 `User` 字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接，如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入第二阶段等待用户请求

--------------------

### 4.2 请求核实阶段 ###

一旦建立了连接，服务器就进入了访问控制的第二阶段，也就是请求核实阶段，对此连接上进来的每个请求，服务器检查该请求要执行什么操作，是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方，这些权限可以来自 `user`、`db`、`tables_priv` 和 `columns_priv` 表。

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

![在这里插入图片描述][2db83d85b54d45c496a9b7f252e76a2f.png]

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

--------------------

## 五、角色管理 ##

角色是在 `MySQL-8.0` 中引入的新功能。在 `MySQL` 中，`角色是权限的集合`，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限，对角色进行操作需要较高的权限，并且像用户账号一样，角色可以拥有授予和撤消的权限。

引入角色的目的是 `方便管理拥有相同权限的用户`。恰当的权限设定，可以确保数据的安全性，这是非常重要的

![在这里插入图片描述][11fe63af701649629bf21cf6ab754cb6.png]

--------------------

### 5.1 创建角色 ###

在实际应用中，为了安全性，需要给用户授予权限。当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以先将权限集合放入角色中，再赋予用户相应的角色。

创建角色使用 `CREATE ROLE` 语句，语法如下：

CREATE ROLE 'roloe_name'[@'host_name'] [, 'roloe_name'[@'host_name']] ...;

角色名称的命名规则和用户名类似，如果 `host_name` 省略，默认为 `%`，`role_name` 不可省略，不可为空

示例：

# 创建一个 manager 的角色
    mysql> CREATE ROLE 'manager'@'localhost';
    Query OK, 0 rows affected (0.00 sec)

上述示例中创建了一个名为 `manager` 的角色，角色可以登录的主机是 `localhost`

如果在创建角色时没有写主机号，比如：

CREATE ROLE 'roloe_name';

`MySQL` 默认是通配符 `%`，表示该角色可以在任意一台主机上登录数据库

还可以一次性创建多个角色，例如：

CREATE ROLE 'manager','stocker','developer';

--------------------

### 5.2 赋予角色权限 ###

创建角色之后，默认这个角色是没有任何权限的，需要给角色进行授权，语法如下：

GRANT PRIVILEGES ON table_name TO 'role_name'[@'host_name'];

上述语句中 `PRIVILEGES` 代表权限的名称，多个权限以逗号隔开，可使用 `SHOW PRIVILEGES;` 命令查询权限名称

示例：

# 赋予 manager 角色对于 mike_inner.t_user 表查询的权限
    mysql> GRANT SELECT ON mike_inner.t_user TO 'manager';
    Query OK, 0 rows affected (0.01 sec)
    
    # 赋予 developer 角色对应 mike_inner 库的增删改查权限
    mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON mike_inner.* TO 'developer'@'%';
    Query OK, 0 rows affected (0.01 sec)
    
    # 赋予 boss 角色所有的权限
    mysql> GRANT ALL PRIVILEGES ON *.* TO `boss`@'%';
    Query OK, 0 rows affected (0.01 sec)

--------------------

### 5.3 查看角色权限 ###

赋予角色权限之后，可以通过 `SHOW GRANTS` 语句来查看权限是否创建成功，语法如下：

SHOW GRANTS FOR 'role_name'@'host_name';

示例：

mysql> SHOW GRANTS FOR 'manager';
    +--------------------------------------------------------+
    | Grants for manager@%                                   |
    +--------------------------------------------------------+
    | GRANT USAGE ON *.* TO `manager`@`%`                    |
    | GRANT SELECT ON `mike_inner`.`t_user` TO `manager`@`%` |
    +--------------------------------------------------------+
    2 rows in set (0.00 sec)

如果没有指定主机，默认主机号为 `%`

--------------------

### 5.4 回收角色权限 ###

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销，添加权限使用 `GRANT` 语句，与角色授权相同，撤销角色或角色权限使用 `REVOKE` 语句。

修改了角色的权限，会影响拥有该角色账户的权限

撤销角色权限的语法如下：

REVOKE PRIVILEGES ON table_name FROM 'role_name'@'host_name';

示例：

# 撤销 manager 角色对 mike_inner.t_user 的查询权限
    mysql> REVOKE SELECT ON mike_inner.t_user FROM 'manager'@'%';
    Query OK, 0 rows affected (0.01 sec)

--------------------

### 5.5 删除角色 ###

当需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色，语法如下：

DROP ROLE role_name [, role_name] ...;

如果删除了角色，那么用户也就失去了通过这个角色所获得的所有权限

示例：

# 删除 manager 角色
    mysql> DROP ROLE 'manager'@'%';
    Query OK, 0 rows affected (0.00 sec)

--------------------

### 5.6 给用户赋予角色 ###

角色创建并授权后，要赋给用户并处于 `激活状态` 才能发挥作用，给用户添加角色可使用 `GRANT` 语句，语法如下：

GRANT role1 [,role2 ...] TO user1 [,user2 ...];

`role` 表示角色，`user` 表示用户，可将多个角色同时赋予多个用户，用逗号隔开

示例：

给用户 `duojiala` 赋予 `developer` 角色

# 查看用户 duojiala 的权限
    mysql> SHOW GRANTS FOR 'duojiala'@'%';
    +--------------------------------------+
    | Grants for duojiala@%                |
    +--------------------------------------+
    | GRANT USAGE ON *.* TO `duojiala`@`%` |
    +--------------------------------------+
    1 row in set (0.00 sec)
    
    # 查看角色 developer 的权限
    mysql> SHOW GRANTS FOR 'developer'@'%';
    +---------------------------------------------------------------------------+
    | Grants for developer@%                                                    |
    +---------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `developer`@`%`                                     |
    | GRANT SELECT, INSERT, UPDATE, DELETE ON `mike_inner`.* TO `developer`@`%` |
    +---------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    # 给用户 duojiala 赋予 developer 的角色
    mysql> GRANT 'developer'@'%' TO 'duojiala'@'%';
    Query OK, 0 rows affected (0.01 sec)
    
    # 再查询 duojiala 的权限
    mysql> SHOW GRANTS FOR 'duojiala'@'%';
    +-----------------------------------------+
    | Grants for duojiala@%                   |
    +-----------------------------------------+
    | GRANT USAGE ON *.* TO `duojiala`@`%`    |
    | GRANT `developer`@`%` TO `duojiala`@`%` |
    +-----------------------------------------+
    2 rows in set (0.01 sec)

按理说给 `duojiala` 这个用户赋予了 `developer` 的角色后，使用 `duojiala` 这个用户登录，应该可以去操作 `mike_inner` 这个数据库

示例：

# 使用 duojiala 登陆后，查询可操作的库
    mysql> SHOW DATABASES;;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.00 sec)

如果还是不能去操作 `mike_inner` 这个库

可用以下语句查询当前用户所拥有的角色：

SELECT CURRENT_ROLE();

如果显示为 `NONE` 或者未查询到所赋予的角色时，则表明该角色 `未被激活`

示例：

mysql> SELECT CURRENT_ROLE();
    +----------------+
    | CURRENT_ROLE() |
    +----------------+
    | NONE           |
    +----------------+
    1 row in set (0.00 sec)

--------------------

### 5.7 激活角色 ###

`MySQL` 中创建了角色之后，默认是没有激活的，也就是不能用的，必须要 `手动激活` 以后用户才能拥有该角色对应的权限

激活角色有两种方式：

*  方式一：使用 `SET DEFAULT ROLE` 命令激活角色

给指定用户激活已拥有的角色权限，语法如下：

SET DEFAULT ROLE role TO user [,user ...];

`role` 表示角色，通常写为 `'role_name'@'host_name'`，如果写为 `ALL` 则表明激活全部已拥有的角色

`user` 表示用户，通常写为 `'user_name'@'host_name'`

示例：

# 给用户 duojiala 激活 developer 角色（需 root 用户操作）
    mysql> SET DEFAULT ROLE 'developer'@'%' TO 'duojiala'@'%';
    Query OK, 0 rows affected (0.01 sec)
    
    # 给用户 duojiala 默认激活所有已拥有的角色（需 root 用户操作）
    mysql> SET DEFAULT ROLE ALL TO 'duojiala'@'%';
    Query OK, 0 rows affected (0.01 sec)

> 注意：用户需要退出重新登录，才能看到赋予的角色

使用 `duojiala` 用户登录，查看所拥有的角色，就能看到被赋予并且已经激活的角色

mysql> SELECT CURRENT_ROLE();
    +-----------------+
    | CURRENT_ROLE()  |
    +-----------------+
    | `developer`@`%` |
    +-----------------+
    1 row in set (0.00 sec)

*  方式二：将 `activate_all_roles_on_login` 设置为 `ON`

在 `MySQL` 中有一个系统变量 `activate_all_roles_on_login` 可设置对所有角色激活，在默认情况下是 `OFF` 关闭的状态

设置 `activate_all_roles_on_login` 开启

SET GLOBAL activate_all_roles_on_login = ON;

该语句可对 `所有角色永久激活`，运行这条语句之后，用户才真正有了被赋予角色的所有权限

当然也可以在配置文件 `my.cnf` 中去设置

--------------------

### 5.8 撤销用户角色 ###

撤销用户角色语法如下：

REVOKE role FROM user;

`role` 表示角色，通常写为 `'role_name'@'host_name'`，如果写为 `ALL` 则表明激活全部已拥有的角色

`user` 表示用户，通常写为 `'user_name'@'host_name'`

示例：

# 撤销用户 duojiala 的 developer 角色
    mysql> REVOKE 'developer'@'%' FROM 'duojiala'@'%';
    Query OK, 0 rows affected (0.01 sec)

--------------------

### 5.9 设置强制角色 ###

强制角色是给每个创建账户的默认角色，不需要手动设置，强制角色无法被 `REVOKE` 和 `DROP`

*  方式一：服务启动前设置

在 `MySQL` 的配置文件 `my.cnf` 中进行设置

[mysqld]
    mandatory_roles = 'role1,role2@localhost,role3@%'

*  方式二：运行时设置

语法如下：

# 重启系统后仍然有效
    SET PERSIST mandatory_roles = 'role1,role2@localhost,role3@%';
    
    # 系统重启后失效
    SET GLOBAL mandatory_roles = 'role1,role2@localhost,role3@%';

--------------------

上篇：[第二章、数据库目录结构与文件系统][Link 1]  
下篇：[第四章、配置文件与系统变量][Link 2]

[Link 1]: https://blog.csdn.net/xhmico/article/details/138151317
[Link 2]: https://blog.csdn.net/xhmico/article/details/138246022
[bilibili-_-MySQL]: https://www.bilibili.com/video/BV1iq4y1u7vj?p=105
[f61c1df6cd8841f6b45774014f9af043.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/d40dac89a4074a2e936f1b5ad9c39849.png
[882727816bbc43a3b4f5e9684bd0e7f2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/a7d6223903a64a5abff285271aa3e79a.png
[Link 3]: https://dev.mysql.com/doc/refman/8.0/en/
[4656efb918e746748cef2f6def7adb28.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/460abe530430404e8d6b7e16569c26d3.png
[76b02a02962d4a3fabfa298d230df72e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/8ccfb9a21fb94011a03bf4c03d567442.png
[bc99307d65ca4364ab87bdc2d6b90f20.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/3bf72a5ddb9548e3ba486bc35cbc1ef4.png
[7b2a99a8df1b42b398eca06316c816a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/3d3ebd7ffe3247f8bb8b0194586e5b80.png
[6ed75dfd9f6740879fb5735803bfb959.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/61a6eb4f8fb64f3986783878d0425945.png
[2db83d85b54d45c496a9b7f252e76a2f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/d20cc3c3d5e343dc97b43e4a3d20c04e.png
[11fe63af701649629bf21cf6ab754cb6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/35a879cdb1534a5a894fd28d5adb9a2d.png