MySQL_11 用户管理和权限管理

秒速五厘米 2023-10-12 09:00 76阅读 0赞

**目录**

一、用户管理

1.用户介绍 :

2.操作指令 :

3.代码演示 :

二、权限管理

1.MySQL常见权限汇总 ：

2.相关指令 :

3.代码演示 :

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

## 一、用户管理 ##

### 1.用户介绍 :  ###

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

![ddc48bb442fa4834ac2fba9852305732.png][]

**user表中，几个比较重要的字段——**  
**①Host : 允许“登录”的位置（IP）；**localhost表示该用户只允许本机登录；但也可以指定IP地址，eg : 192.168.1.100，192.168.1.%;  
**②User : 用户名;**  
**③Authentication\_string : 加密后的密码**，mysql8.0默认是通过caching\_sha2\_password()函数来加密的。

**Q :** 为什么需要进行用户管理？  
**A :** 当我们做项目开发时，可以根据不同的开发人员，赋予他不同的MySQL操作权限；因此，MySQL数据库的管理员root用户，会根据需要创建不同的用户，并分别赋予相应的权限，供不同人员来使用。

### 2.操作指令 :  ###

> **1° 创建用户（同时指定密码）——**
> 
> **CREATE USER** **'用户名' @ '允许登录的位置'** **IDENTIFIED BY** **'pwd\_expr';**
> 
>  **2° 删除用户 ——**
> 
> **DROP USER** **'用户名' @ '允许登录的位置';**
> 
> **3° 修改密码 —— (MySQL8.0)**
> 
> **ALTER USER** **'用户名' @ '允许登录的位置'** **IDENTIFIED WITH** **MYSQL\_NATIVE\_PASSWORD** **BY** **'pwd\_expr';**
> 
> **PS :** 
> 
> **①**MySQL下，认为**用户的完整信息由用户名和登陆位置组成**。
> 
> **②**不同的数据库用户，登录到DBMS后，根**据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样**。
> 
> **③**在创建用户时，若未指定Host，则为**%，表示所有IP都有连接权限**；同理，在删除用户时，若Host != %，必须在用户信息中明确指定用户的Host值。

### 3.代码演示 :  ###

**①**创建一个新的用户Ice，密码假设为12345，如下所示 :

CREATE USER 'Ice'@'localhost' IDENTIFIED BY '12345';

![4aab8cc803b64cbbbc13b0701ccd4f3d.png][]

**②**尝试登录Ice用户，如下 :

![6785c25fd40b44b3a0f6a4d11458db46.png][]

登录成功后，会发现Ice用户可操作的数据库很少，如下图所示 :

![9dfab91a950c46b7bef30fdb8d6cae89.png][]

**③**尝试更改Ice用户的密码，如下 :

ALTER USER 'ice'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '54321';

更改密码后，**已建立的Ice用户与MySQL的连接将无法重新连接**，如下 :

![f9dbe33ff74a49edae77510cd4c00288.png][]

这时，**在“编辑连接”中更改当前的登录密码可以重新连接**。如下 :

![d48c30711f4f4beea8b2f1008d1c3599.png][]

![426e90a66baa4b3d85c64e6ff386a2d6.png][]

**④**尝试删除Ice用户。

删除前，可以在mysql.user表中看到Ice用户存在，如下图所示 :

![51947e2f892f452e9893527ddccfd4bd.png][]

使用DROP USER 指令删除Ice用户 :

DROP USER 'Ice'@'localhost' ;

删除成功后，Ice用户在user表中不存在。

![eeb2d2f48be5437e9a05e3897ad3e8f3.png][]

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

## 二、权限管理 ##

### 1.MySQL常见权限汇总 ： ###

*  **ALL \[PRIVILEGES\] ： 设置除GRANT OPTION之外的所有简单权限**
 *  **ALTER ： 允许使用ALTER TABLE**
 *  ALTER ROUTINE : 更改或取消已存储的子程序
 *  **CREATE : 允许使用CREATE TABLE**
 *  CREATE ROUTINE : 创建已存储的子程序
 *  CREATE TENPORARY TABLES : 允许使用CREATE TEMPORARY TABLE
 *  **CREATE USER : 允许使用CREATE USER，DROP USER，RENAME USER和REVOKE ALL PRIVILEGES。**
 *  **CREATE VIEW : 允许使用CREATE VIEW**
 *  **DELETE : 允许使用DELETE**
 *  **DROP : 允许使用DROP TABLE**
 *  EXECUTE : 允许用户运行已存储的子程序
 *  FILE : 允许使用SELECT..INTO OUTFILE和LOAD DATA INFILE
 *  **INDEX : 允许使用CREATE INDEX和DROP INDEX**
 *  **INSERT : 允许使用INSERT**
 *  LOCK TABLES : 允许对您拥有SELECT权限的表使用LOCK TABLES
 *  PROCESS : 允许使用SHOW FULL PROCESSLIST
 *  REFERENCES : 未被实施
 *  RELOAD : 允许使用FLUSH
 *  REPLICATION CLIENT : 允许用户询问从属服务器或主服务器的地址
 *  REPLICATION SLAVE : 用于复制型从属服务器（从主服务器中读取二进制日志事件)
 *  **SELECT : 允许使用SELECT**
 *  SHOW DATABASES : SHOW DATABASES显示所有数据库
 *  **SHOW VIEW : 允许使用SHOW CREATE VIEW**
 *  SHUTDOWN : 允许使用mysqladmin shutdown
 *  SUPER : 允许使用CHANGE MASTER，KILL，PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令;允许您连接(一次)，即使已达到max\_connectionso
 *  **UPDATE : 允许使用UPDATE**
 *  USAGE : “无权限”的同义词
 *  **GRANT OPTION : 允许授予权限**

### 2.相关指令 :  ###

> **1° 授予权限**
> 
> **GRANT** **purview\_list** **ON** **库.对象名** **TO** **'用户名'@'登录位置' \[IDENTIFIED BY 'pwd\_expr'\];  
> 注意事项——**
> 
>  **①权限列表中的多个权限使用逗号分隔开，eg :** 
> 
>  **eg1 : GRANT SELECT, DELETE, CREATE ON ...;**
> 
>  **eg2 : GRANT ALL ON...; //表示授予该用户在该对象上的全部权限。**
> 
>  **②****\*.\*代表本系统中的所有数据库的所有数据对象（表，视图，存储过程）；库.\*表示某个指定数据库中的所有数据对象(表，视图，存储过程）**。
> 
> **③IDENTIFIED BY 可写可不写。****若写出，用户存在则修改该用户的密码；用户不存在则创建该用户。**
> 
> **2° 回收权限**
> 
> **REVOKE** **purview\_list** **ON** **库.对象名** **FROM** **'用户'@'登录位置';**
> 
> **PS :** 
> 
> 若权限指令没有生效，可以使用**FLUSH PRIVILEGES;** 指令来刷新（一般来说都会立即生效，尤其对于高版本的MySQL)。

### 3.代码演示 :  ###

**要求如下——**  
 **①创建用户five@localhost；  
 ②在root用户下为five用户授予一定权限；  
 ③在five用户下测试被授予的权限是否生效；  
 ④测试five用户未被授予的权限是否生效；  
 ⑤回收five用户的权限并删除five用户。**

**演示如下——**

**①创建five用户 :**

CREATE USER 'five'@'localhost' IDENTIFIED BY '666';
    
    SELECT * FROM mysql.user;

![1bf80800cd2546cc87fbeeabd340d7bf.png][]

**②在root用户下为five用户分配权限 :**

假定我们可以使five用户对select\_ex数据库中的emp表进行访问，授予five用户查询，修改，和删除数据的权限。如下图所示 :

![0fd233b33654426d9d6e06aa03397737.png][]

GRANT SELECT,UPDATE,INSERT ON select_ex.emp TO 'five'@'localhost';

**③登录five用户，查看权限是否生效 :**

![cf0ff74c2a6f44999284a3b011bf6dd8.png][]

可以看到，five用户视角下，已经显示有select\_ex数据库以及emp表。  
继续，测试被授权的指令能否使用 :

INSERT INTO emp
    		VALUES 
    		(6666,'NewBee','Manage',1,'2002-011-11',99999.00,99999.00,10);
    		
    UPDATE emp
    		SET ecareer = 'Boss';
    		
    SELECT * 
    		FROM emp;

![bb079beef2fc4df095af3953eeaffc28.png][]

**④测试five用户能否删除表中的数据 :**

DELETE FROM emp
    		WHERE eno = 6666;

显然无法成功 :

![286f065e84974fe5b07e13f5066cc217.png][]

可以继续在root用户下追加权限，使five用户可以进行删除数据的操作 :

GRANT DELETE ON select_ex.emp TO 'five'@'localhost';

five用户再次尝试删除数据 :

DELETE FROM emp
    		WHERE eno = 6666;
    		
    SELECT * FROM emp;

![3cc79e3b1cb94a96974438bc46839131.png][]

**⑤回收five用户的权限并删除five用户 :**

REVOKE INSERT,DELETE,SELECT,UPDATE ON select_ex.emp FROM 'five'@'localhost';

**在five用户下刷新连接，将看不到selec\_ex数据库**，如下图所示 :

![53fdb2d482234e5997bad4cffabc84ff.png][]

**在root用户下删除five用户 :**

DROP USER 'five'@'localhost';

five用户将无法重新连接。

![c2d0e2d597b5443e89a925da94e62982.png][]

**System.out.println("END-------------------------------------------------------------------------------");**

[ddc48bb442fa4834ac2fba9852305732.png]: https://img-blog.csdnimg.cn/ddc48bb442fa4834ac2fba9852305732.png
[4aab8cc803b64cbbbc13b0701ccd4f3d.png]: https://img-blog.csdnimg.cn/4aab8cc803b64cbbbc13b0701ccd4f3d.png
[6785c25fd40b44b3a0f6a4d11458db46.png]: https://img-blog.csdnimg.cn/6785c25fd40b44b3a0f6a4d11458db46.png
[9dfab91a950c46b7bef30fdb8d6cae89.png]: https://img-blog.csdnimg.cn/9dfab91a950c46b7bef30fdb8d6cae89.png
[f9dbe33ff74a49edae77510cd4c00288.png]: https://img-blog.csdnimg.cn/f9dbe33ff74a49edae77510cd4c00288.png
[d48c30711f4f4beea8b2f1008d1c3599.png]: https://img-blog.csdnimg.cn/d48c30711f4f4beea8b2f1008d1c3599.png
[426e90a66baa4b3d85c64e6ff386a2d6.png]: https://img-blog.csdnimg.cn/426e90a66baa4b3d85c64e6ff386a2d6.png
[51947e2f892f452e9893527ddccfd4bd.png]: https://img-blog.csdnimg.cn/51947e2f892f452e9893527ddccfd4bd.png
[eeb2d2f48be5437e9a05e3897ad3e8f3.png]: https://img-blog.csdnimg.cn/eeb2d2f48be5437e9a05e3897ad3e8f3.png
[1bf80800cd2546cc87fbeeabd340d7bf.png]: https://img-blog.csdnimg.cn/1bf80800cd2546cc87fbeeabd340d7bf.png
[0fd233b33654426d9d6e06aa03397737.png]: https://img-blog.csdnimg.cn/0fd233b33654426d9d6e06aa03397737.png
[cf0ff74c2a6f44999284a3b011bf6dd8.png]: https://img-blog.csdnimg.cn/cf0ff74c2a6f44999284a3b011bf6dd8.png
[bb079beef2fc4df095af3953eeaffc28.png]: https://img-blog.csdnimg.cn/bb079beef2fc4df095af3953eeaffc28.png
[286f065e84974fe5b07e13f5066cc217.png]: https://img-blog.csdnimg.cn/286f065e84974fe5b07e13f5066cc217.png
[3cc79e3b1cb94a96974438bc46839131.png]: https://img-blog.csdnimg.cn/3cc79e3b1cb94a96974438bc46839131.png
[53fdb2d482234e5997bad4cffabc84ff.png]: https://img-blog.csdnimg.cn/53fdb2d482234e5997bad4cffabc84ff.png
[c2d0e2d597b5443e89a925da94e62982.png]: https://img-blog.csdnimg.cn/c2d0e2d597b5443e89a925da94e62982.png