MySQL操作之用户管理权限管理：(DC)（五）

怼烎@ 2023-10-02 09:28 52阅读 0赞

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>类型</th> 
   <th align="left">地址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96165881" rel="nofollow">MySQL操作之概念、SQL约束（一）</a></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96339284" rel="nofollow">MySQL操作之数据定义语言（DDL)（二）</a></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96445064" rel="nofollow">MySQL操作之数据操作语言（DML)（三）</a></td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96726772" rel="nofollow">MySQL操作之数据查询语言：(DQL)（四-1）（单表操作）</a></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/96729083" rel="nofollow">MySQL操作之数据查询语言：(DQL)（四-2）（多表查询）</a></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120254916" rel="nofollow">MySQL操作之数据控制语言：(DC)（五）</a></td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/weixin_44624117/article/details/122593057" rel="nofollow">MySQL操作之数据库函数</a></td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383272" rel="nofollow">MySQL管理之数据类型</a></td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120393164" rel="nofollow">MySQL管理之索引</a></td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120240970" rel="nofollow">MySQL管理之事务管理</a></td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383054" rel="nofollow">MySQL管理之存储过程</a></td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120251668" rel="nofollow">MySQL管理之视图</a></td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120383282" rel="nofollow">MySQL管理之数据备份与还原</a></td> 
  </tr> 
  <tr> 
   <td>14</td> 
   <td>MySQL</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/95240161" rel="nofollow">Linux（centos 7.5）服务器安装MySQL</a></td> 
  </tr> 
  <tr> 
   <td>15</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/119246809" rel="nofollow">MyBatis从入门到多表关联</a></td> 
  </tr> 
  <tr> 
   <td>16</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/120823825" rel="nofollow">MyBatis常用方法</a></td> 
  </tr> 
  <tr> 
   <td>17</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/98309342" rel="nofollow">Mybatis逆向工程的使用（附文件地址）</a></td> 
  </tr> 
  <tr> 
   <td>18</td> 
   <td>MyBatis</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/106430172" rel="nofollow">spring boot连接Mybatis数据库的配置文件(MySql、SQLserver、Oracle)</a></td> 
  </tr> 
  <tr> 
   <td>19</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/119060130" rel="nofollow">Mybatis-Plus使用案例(包括初始化以及常用插件)</a></td> 
  </tr> 
  <tr> 
   <td>20</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124972178" rel="nofollow">Mybatis-Plus(Service CRUD 接口)</a></td> 
  </tr> 
  <tr> 
   <td>21</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="http://t.csdn.cn/3mdPc" rel="nofollow">Mybatis-plus 4种条件构造器方式</a></td> 
  </tr> 
  <tr> 
   <td>22</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124936692" rel="nofollow">Mybatis-Plus 执行自定义SQL</a></td> 
  </tr> 
  <tr> 
   <td>23</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124971821" rel="nofollow">MyBatis-plus配置自定义SQL(执行用户传入SQL)</a></td> 
  </tr> 
  <tr> 
   <td>24</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/124973383" rel="nofollow">Mybatis-Plus(连接Hive)</a></td> 
  </tr> 
  <tr> 
   <td>25</td> 
   <td>MyBatis-Plus</td> 
   <td align="left"><a href="https://blog.csdn.net/lydms/article/details/122095848" rel="nofollow">Mybatis-Plus 代码生成器</a></td> 
  </tr> 
 </tbody> 
</table>

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

### 文章目录 ###

*   *  一、简介
     *  二、用户管理
     *   *  2.1 User表
         *  2.2 创建普通用户
         *  2.3 删除普通用户
         *  2.4 修改用户密码
         *  2.5 解决root用户密码丢失
     *  三、权限管理
     *   *  3.1 MySQL的权限
         *  3.2 授予权限
         *  3.3 查看权限
         *  3.4 收回权限

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

## 一、简介 ##

MySQL中用户分为root用户和普通用户。root用户为超级管理员，具有所有权限(创建用户、删除用户、管理用户)。

## 二、用户管理 ##

### 2.1 User表 ###

*  新建MySQL时，会自动安装一个mysql的数据库，该数据库下面的表都是权限表。
 *  其中：`user`表时最重要权限表。记录了允许连接到服务器的账号信息以及一些全局权限信息。
 *  `user`表有42个字段，大致分为4类。

**1. 用户列**

*  包括`Host`、`User`、`Password`，分别代表主机名、用户名和密码。
 *  `Host`、`User`：为联合主键。
 *  `Host`、`User`、`Password`：三个字段都匹配时，才会允许建立连接。
 *  修改密码：只需要修改User表中`Password`即可。

**2. 权限列**

*  包括`Select_priv`、`Insert_priv`、`Update_priv`等以`priv`结尾的字段，这些字段决定了用户的权限。
 *  `Select_priv`：查询权限。
 *  `Insert_priv`：插入权限。
 *  `Update_priv`：更新权限。
 *  数据类型都是枚举类型`Enum`。
 *  默认值为：N(没有权限)。

**3. 安全列**

*  用户管理用户的安全信息，其中包含6个字段。
 *  `ssl_type`和`ssl_cipher`：用户加密。
 *  `x509_issuer`和`x509_subject`标准：用来标识用户。
 *  `plugin`和`authentication_string`：用于存储于授权相关的插件。

**4. 资源控制列**

*  用于限制用户使用的资源。其中包括4个字段。
 *  `max_questions`：每小时允许用户执行查询操作的次数。
 *  `max_updates`：每小时允许用户执行更新操作的次数。
 *  `max_connection`：每小时允许用户建立连接的次数。
 *  `max_user_connection`：每小时允许用户同时建立连接的次数。

<table> 
 <thead> 
  <tr> 
   <th>所属分类</th> 
   <th>字段名</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1.用户列</td> 
   <td>Host</td> 
   <td>主机名</td> 
  </tr> 
  <tr> 
   <td>1.用户列</td> 
   <td>User</td> 
   <td>用户名</td> 
  </tr> 
  <tr> 
   <td>1.用户列</td> 
   <td>Password</td> 
   <td>密码</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Select_priv</td> 
   <td>确定用户是否可以通过SELECT命令选择数据</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Insert_priv</td> 
   <td>确定用户是否可以通过INSERT命令插入数据</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Update_priv</td> 
   <td>确定用户是否可以通过UPDATE命令修改现有数据</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Delete_priv</td> 
   <td>确定用户是否可以通过DELETE命令删除现有数据</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Create_priv</td> 
   <td>确定用户是否可以创建新的数据库和表</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Drop_priv</td> 
   <td>确定用户是否可以删除现有数据库和表</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Reload_priv</td> 
   <td>确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令。<br>包括日志、权限、主机、查询和表</td> 
  </tr> 
  <tr> 
   <td>2. 权限列</td> 
   <td>Shutdown_priv</td> 
   <td>确定用户是否可以关闭MySQL服务器。<br>在将此权限提供给root账户之外的任何用户时，都应当非常谨慎</td> 
  </tr> 
  <tr> 
   <td>3. 安全列</td> 
   <td>ssl_type</td> 
   <td>支持ssl标准加密安全字段</td> 
  </tr> 
  <tr> 
   <td>3. 安全列</td> 
   <td>ssl_cipher</td> 
   <td>支持ssl标准加密安全字段</td> 
  </tr> 
  <tr> 
   <td>3. 安全列</td> 
   <td>x509_issuer</td> 
   <td>支持x509标准字段</td> 
  </tr> 
  <tr> 
   <td>3. 安全列</td> 
   <td>x509_subject</td> 
   <td>支持x509标准字段</td> 
  </tr> 
  <tr> 
   <td>3. 安全列</td> 
   <td>plugin</td> 
   <td>5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户</td> 
  </tr> 
  <tr> 
   <td>3. 安全列</td> 
   <td>authentication_string</td> 
   <td>通过authentication_string可以控制两者的映射关系。<br>(PAM plugin等,PAM可以支持多个服务名)尤其是在使用代理用户时，并须声明这一点</td> 
  </tr> 
  <tr> 
   <td>4. 资源控制列</td> 
   <td>max_questions</td> 
   <td>每小时允许用户执行查询操作的次数</td> 
  </tr> 
  <tr> 
   <td>4. 资源控制列</td> 
   <td>max_updates</td> 
   <td>每小时允许用户执行更新操作的次数</td> 
  </tr> 
  <tr> 
   <td>4. 资源控制列</td> 
   <td>max_connection</td> 
   <td>每小时允许用户建立连接的次数</td> 
  </tr> 
  <tr> 
   <td>4. 资源控制列</td> 
   <td>max_user_connection</td> 
   <td>每小时允许用户同时建立连接的次数</td> 
  </tr> 
 </tbody> 
</table>

### 2.2 创建普通用户 ###

有中方式创建：

*  使用`GRANT`语句创建用户
 *  使用`CREATE USER`语句创建用户
 *  使用`Insert`语句创建用户

**1. 使用`GRANT`语句创建用户**

*  `GRANT`语句不仅可以创建新用户，还可以对用户进行授权。
 *  该语句会自动加载权限表，不需要手动刷新。
 *  而且安全、准确、错误少。
 *  使用`GRANT`语句创建用户是最常用的方法。
 *  创建用户时，MySQL会对用户的密码自动加密，以提高安全性。

语法：

GRANT privileges ON database.table
    	TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]

*  `privileges`：表示该用户具有的权限信息。
 *  `database.table`：表示新用户的权限范围表，可以在指定的库、表上使用权限。
 *  `username`：新用户名称。
 *  `hostname`：主机名。
 *  `Password`：新用户的密码。

创建新用户：用户名`user01`，密码`1581145`，授予对`test.student`有查询权限。

GRANT SELECT ON test.student TO `user01`@`1581145` IDENTIFIED BY `123`

**2. 使用`CREATE USER`语句创建用户**

*  使用`CREATE USER`语句创建新用户，服务器会自动修改授权表。不需要手动刷新。
 *  但，新建的用户是没有权限的。

CREATE USER `username`@`hostname` [IDENTIFIED BY [PASSWORD]`password`]

*  `username`：表示新创建的用户名。
 *  `hostname`：表示主机名。
 *  `password`：表示用户的密码。
 *  `[PASSWORD]`：该参数是可选的，假如密码为普通字符串就不需要使用。

语句：

CREATE USER `user02`@`127.0.0.1` IDENTIFIED BU `123`

**3. 使用`Insert`语句创建用户**

*  在`User`表添加数据。
 *  通常只需要添加`Host`、`User`、`Password`这三个字段即可，其它字段取默认值。
 *  `ssl_cipher`、`x509_issuer`、`x509_subject`字段没有默认值，也需要添加。
 *  需要手动刷新权限表或者重启MySQL服务。

新建用户

INSERT INTO mysql.`user` ( `host`, `user`, `Password`, ssl_cipher, x509_issuer, x509_subject )
    	VALUES( 'localhost', 'user03', PASSWORD ( '123' ), '', '', '' );

刷新权限：

FLUSH PRIVILEGES;

### 2.3 删除普通用户 ###

**1. 使用`DROP User`语句删除用户**

语法

DROP USER `username`@`hostname` [,`username`@`hostname`];

DROP USER `user01`@`localhost`;

**2. 使用`DELETE`语句删除用户**

*  `DELETE`不仅可以删除普通表数据，还可以删除`user`表数据。

语法

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

sql

DELETE FROM mysql.user WHERE Host = 'localhost' AND User = 'user02';

### 2.4 修改用户密码 ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAaGFfbHlkbXM_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center]

**1. 使用`mysqladmin`修改密码**

mysqladmin -u username [-h hostname] -p password new_password

*  `username`：要修改的用户名。
 *  `-h hostname`：可以不写，默认localhost。
 *  `-p password`：为关键字。
 *  `new_password`：新设置的密码。

修改密码：

mysqladmin -u root -p password 1581145

上述语句执行完成后，会提示输入密码。这个密码是旧密码。

**2. 使用`Update`语句修改密码**

语法：

UPDATE mysql.user set Password=PASSWORD('new_password')
    	WHERE User = 'username' AND Host='hostname';

sql

UPDATE mysql.user set Password=PASSWORD('1581145')
    	WHERE User = 'root' AND Host='localhost';

刷新权限：

FLUSH PRIVILEGES;

**3. 使用`SET`语句修改密码**

*  用户登录MySQL服务器以后，可以使用`set`语句，修改密码。
 *  需使用`PASSWORD('new_password')`进行密码加密。

语法：

SET PASSWORD FOR `username`@'hostname' = PASSWORD('new_password');
    SET PASSWORD = PASSWORD('new_password');

SET PASSWORD = PASSWORD('1581145');

**4. root使用`GRANT`修改普通用户密码**

语法：

GRANT USAGE ON *.* TO 'usernmae'@'localhost' IDENTIFIED BY [PASSWORD]‘new_password';

### 2.5 解决root用户密码丢失 ###

*  `--skip-grant-tables`：可以停止MySQL的权限判断。也就是任何用户都可以登录MySQL。

1、停止MySQL服务

net stop mysql;

2、使用`--skip-grant-tables`启动MySQL服务

mysqld --skip-grant-tables

3、登录MySQL服务器

mysql -u -root

4、使用`Update`语句设置root用户密码

UPDATE mysql.user SET PASSWORD = PASSWORD ( '158145' ) 
    	WHERE User = 'root' AND Host = 'localhot';

5、加载权限表

FLUSH PRIVILEGES;

## 三、权限管理 ##

### 3.1 MySQL的权限 ###

MySQL的权限信息，被存储在user、db、host、tables、priv、cloumn\_priv和procs\_priv中。当MySQL启动时，会加载这些表，并将权限信息读取到内存中。

*  `Create`和`Drop`权限：可以创建数据库、表、索引，或者删除已有的数据库、表、索引。
 *  `Insert`、`Delete`、`Update`和`Select`权限：对数据库表进行增删改查。
 *  `Index`权限：可以创建和删除索引。适用于所有表。
 *  `Alter`权限：用于修改表的结构或者重命名表。
 *  `Grant`权限：允许用户为其它用户授权。可用户数据库和表。
 *  `File`权限：能读写MySQL服务器上的所有文件。

### 3.2 授予权限 ###

语法：

GRANT privileges [(columns)][,privileges [(columns)]...] ON database.table
    	TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]
    	[,`username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]]...
    	[WITH with_option [with_option]...]

*  `privileges`：表示权限类型。
 *  `columns`：表示权限作用域某一列。
 *  `IDENTIFIED BY`：为用户设置密码。

**其中`WITH`关键字有5个参数取值：**

1.  `GRANT OPTION`：将自己权限授予其它用户。
2.  `MAX_QUERIED_PER_HOUR count`：设置每小时最多可执行多少次`count`查询。
3.  `MAX_UPDATES_PER_HOUR count`：设置每小时最多可执行多少次更新。
4.  `MAX_CONNECTIONS_PER_HOUR count`：设置每小时最大连接数。
5.  `MAX_USER_CONNECTIONS`：设置每个用户最多可以同时建立的连接数量。s

授予权限：

GRANT INSERT,SELECT ON *.* TO 'user04'@'localhost' IDENTIFIED BY '123' 
    	WITH GRANT OPTION;

### 3.3 查看权限 ###

`SHOW GRANTS`：用来查看用户的权限

SHOW GRANTS FOR 'username'@'hostname';

SHOW GRANTS FOR 'root'@'1581145';

结果：

root用户

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 
    IDENTIFIED BY PASSWORD '*B105526BD04216F24C8DC48AF956866AA539F35C' WITH GRANT OPTION

普通用户( `SELECT,INSERT`)

GRANT SELECT,INSERT ON *.* TO 'user03'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

### 3.4 收回权限 ###

`REVOKE`：用于收回用户的权限。

REVOKE privileges [columns] [,privileges [columns]] ON database.table
    	FROM 'username'@'hostname'[,username'@'hostname']...

*  `privileges`：表示参数列表。
 *  `columns`：表示作用在哪一列。

收回`Insert`权限

REVOKE INSERT ON *.* FROM 'user04'@'localhost';

收回全部权限

REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'user04'@'localhost';

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAaGFfbHlkbXM_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/676731e49ac041a88f58e5889ab44a60.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAaGFfbHlkbXM=,size_18,color_FFFFFF,t_70,g_se,x_16#pic_center