mysql用户管理权限分配详解

迈不过友情╰ 2022-05-29 06:26 779阅读 0赞

## 前言 ##

> 如果想成为一个技术总监，技术经理。我觉得对于数据库用户的权限分配还是很重要的。比如，阿里巴巴，一个刚来的实习生，他可能连真实数据库都接触不到。等到终于熬成可以操作下数据库了，但是发现自己还是只有读取权限，并没有其他任何对数据库造成影响的权限。

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

> 这就是技术经理，技术总监。技术组长。所做的事情，维护好公司的利益，最为重要。

## 用户权限管理作用： ##

1.  可以限制用户访问哪些库、哪些表
2.  可以限制用户对哪些表执行CREATE、DELETE、(ALTER、UPDATE)、SELECT等操作
3.  可以限制用户登录的IP或域名
4.  可以限制用户自己的权限是否可以授权给别的用户

## 1、user表的组成 ##

> mysql中所有的用户都是存放在user表中的，user表有39个字段。这些字段可以分为4类：  
> • 用户列；  
> • 权限列；  
> • 安全列；  
> • 资源控制列；

### 1.1、用户列 ###

> 用户列包括Host、User、Password，  
> 　　User：要创建用户的名字。  
> 　　Host：主机名：表示要这个新创建的用户允许从哪台机登陆  
> 　　Password：新创建用户的登陆数据库密码，如果没密码可以不写。

### 1.2、权限列 ###

> user表的权限列是以priv结尾的字段。  
> 　　这些字段的值只有Y和N。Y表示该权限可以用到所有数据库上；N表示该权限不能用到所有数据库上；

<table> 
 <tbody> 
  <tr> 
   <td>列</td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td>Select_priv</td> 
   <td>确定用户是否可以通过SELECT命令选择数据</td> 
  </tr> 
  <tr> 
   <td>Insert_priv</td> 
   <td>确定用户是否可以通过INSERT命令插入数据</td> 
  </tr> 
  <tr> 
   <td>Update_priv</td> 
   <td>确定用户是否可以通过UPDATE命令修改现有数据</td> 
  </tr> 
  <tr> 
   <td>Delete_priv</td> 
   <td>确定用户是否可以通过DELETE命令删除现有数据</td> 
  </tr> 
  <tr> 
   <td>Create_priv</td> 
   <td>确定用户是否可以创建新的数据库和表</td> 
  </tr> 
  <tr> 
   <td>Drop_priv</td> 
   <td>确定用户是否可以删除现有数据库和表</td> 
  </tr> 
  <tr> 
   <td>Reload_priv</td> 
   <td>确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表</td> 
  </tr> 
  <tr> 
   <td>Shutdown_priv</td> 
   <td>确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时，都应当非常谨慎</td> 
  </tr> 
  <tr> 
   <td>Process_priv</td> 
   <td>确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程</td> 
  </tr> 
  <tr> 
   <td>File_priv</td> 
   <td>确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令</td> 
  </tr> 
  <tr> 
   <td>Grant_priv</td> 
   <td>确定用户是否可以将已经授予给该用户自己的权限再授予其他用户</td> 
  </tr> 
  <tr> 
   <td>References_priv</td> 
   <td>目前只是某些未来功能的占位符；现在没有作用</td> 
  </tr> 
  <tr> 
   <td>Index_priv</td> 
   <td>确定用户是否可以创建和删除表索引</td> 
  </tr> 
  <tr> 
   <td>Alter_priv</td> 
   <td>确定用户是否可以重命名和修改表结构</td> 
  </tr> 
  <tr> 
   <td>Show_db_priv</td> 
   <td>确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库</td> 
  </tr> 
  <tr> 
   <td>Super_priv</td> 
   <td> <p>确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET&nbsp;GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令</p> </td> 
  </tr> 
  <tr> 
   <td>Create_tmp_table_priv</td> 
   <td>确定用户是否可以创建临时表</td> 
  </tr> 
  <tr> 
   <td>Lock_tables_priv</td> 
   <td>确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改</td> 
  </tr> 
  <tr> 
   <td>Execute_priv</td> 
   <td>确定用户是否可以执行存储过程</td> 
  </tr> 
  <tr> 
   <td>Repl_slave_priv</td> 
   <td>确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信</td> 
  </tr> 
  <tr> 
   <td>Repl_client_priv</td> 
   <td>确定用户是否可以确定复制从服务器和主服务器的位置</td> 
  </tr> 
  <tr> 
   <td>Create_view_priv</td> 
   <td>确定用户是否可以创建视图</td> 
  </tr> 
  <tr> 
   <td>Show_view_priv</td> 
   <td>确定用户是否可以查看视图或了解视图如何执行</td> 
  </tr> 
  <tr> 
   <td>Create_routine_priv</td> 
   <td>确定用户是否可以更改或放弃存储过程和函数</td> 
  </tr> 
  <tr> 
   <td>Alter_routine_priv</td> 
   <td>确定用户是否可以修改或删除存储函数及函数</td> 
  </tr> 
  <tr> 
   <td>Create_user_priv</td> 
   <td>确定用户是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户</td> 
  </tr> 
  <tr> 
   <td>Event_priv</td> 
   <td>确定用户能否创建、修改和删除事件</td> 
  </tr> 
  <tr> 
   <td>Trigger_priv</td> 
   <td>确定用户能否创建和删除触发器</td> 
  </tr> 
 </tbody> 
</table>

### 1.3、安全列 ###

> user表的安全列有4个字段：  
> • ssl\_type；  
> • ssl\_cipher；  
> • x509\_issuer；  
> • x509\_subject；  
> 　　ssl用于加密；x509标准可以用来标识用户。普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE 'have\_openssl’语句来查看是否具有ssl功能。如果取值为DISABLED，那么则没有ssl加密功能。

SHOW VARIABLES LIKE 'have_openssl'

![WX20180319-101525][]

### 1.、资源控制列 ###

> user表的4个资源控制列是：  
> • max\_questions：每小时可以允许执行多少次查询；  
> • max\_updates：每小时可以允许执行多少次更新；  
> • max\_connections：每小时可以建立多少连接；  
> • max\_user\_connections：单个用户可以同时具有的连接数。  
> **默认值为0，表示无限制。**

## 2、db表 ##

> db表存储了某个用户对一个数据库的权限。  
> 主要分为2个，用户列和权限列

![WX20180319-102641][]

### 2.1、用户列 ###

> db表的用户列有3个字段：  
> • Host：主机名；  
> • Db：数据库名；  
> • User：用户名；

### 2.2、权限列 ###

> 举例  
> Create\_routine\_priv：是否具有创建存储过程权限；  
> Alter\_routine\_priv：是否具有修改存储过程权限；

### 2.3、user表和db表的关联以及级别关系 ###

> user表中的权限是针对所有数据库的，如果user表中的Select\_priv字段取值为Y，正常情况下该用户可以查询所有数据库中的表；  
> 　　假如最终结果是为某个用户值设置了查询一个库中表的权限，但是user表的Select\_priv字段的取值为N。那么db表中的Select\_priv字段的取值将会是Y。  
> 　　  
> 原因：用户先根据user表的内容获取权限，然后再根据db表的内容获取权限。

## 3、tables\_priv表和columns\_priv表 ##

> tables\_priv：可以对单个表进行权限设置：  
> • tables\_priv表包含8个字段：  
> • Host：主机名；  
> • DB：数据库名；  
> • User：用户名；  
> • Table\_name：表名  
> • Table\_priv：对表进行操作的权限

Table_priv：
    set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')

• Column\_priv：对表中的数据列进行操作的权限

set('Select','Insert','Update','References')

•	Timestamp：修改权限的事件
    •	Grantor：权限的设置者

![WX20180319-105812][]

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

> columns\_priv：可以对单个数据列进行权限设置，有7个列，作用同上：  
> 　　Host、Db、User、Table\_name、Column\_name、Column\_priv、Timestamp。  
> 　  
> 　　![WX20180319-105943][]

### 3.1、真正的权限分配顺序 ###

> MySQL权限分配是按照  
> **user表(所有数据库)-> db表 (某个数据库)-> table\_priv表(某个表) -> columns\_priv表(某表的某列)的顺序进行分配的。**

在数据库系统中，先判断user表中的值是否为’Y’，如果user表中的值是’Y’，就不需要检查后面的表。如果user表为N，则一次检查后面的表。

## 4、开始真正的账户管理吧，朋友们  ##

### 4.1、新建普通用户 ###

> 正常来说一共有三种方式  
> • 使用create user语句来创建新的用户；  
> • 直接在mysql.user表中INSERT用户；  
> • 使用grant语句来新建用户；

#### 4.1.1、create user （建议使用） ####

> 使用create user语句创建用户，必须要拥有create user权限。

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

示例：

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin'

其中，user参数表示新建用户的账户，user由用户名(User)和主机名(Host)构成；IDENTIFIED BY关键字用来设置用户的密码；password参数表示用户的密码；  
　　**格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0 IP段登录**  
　　  
　　执行之后user表会增加一行记录\*\*，但权限暂时全部为‘N’。\*\*

#### 4.1.2、用INSERT语句新建普通用户（一般不用） ####

> 可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。  
> 　　另外，ssl\_cipher、x509\_issuer、x509\_subject没有值，必须要设置值，否则INSERT语句无法执行。  
> 　　示例：

INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES('%','newuser1',PASSWORD('123456'),'','','')

执行INSERT之后，要使用下面的命令：来使用户生效。

FLUSH PRIVILEGES

#### 4.1.3、用GRANT语句来新建普通用户 （一般用不到） ####

> 个人认为一般也用不上。如果需要用的话，百度下呗，或者后期如果博主用到的话，会继续再这里讲解的哦

### 4.2 删除用户 ###

#### 1、DROP USER语句删除普通用户 ####

> 需要拥有DROP USER权限。语法如下：,user是需要删除的用户，由用户名(User)和主机名(Host)构成。

DROP USER 'admin'@'%'

#### 2、DELETE语句删除普通用户 ####

可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。

DELETE FROM mysql.user WHERE Host = '%' AND User = 'admin'

删除完成后，`FLUSH PRIVILEGES` 进行生效

## 5、真实场景 ##

### 5.1 查看数据库中所有的用户 ###

muysql> select u.host,u.user from user u;

### 5.2 创建用户并授权 ###

正常情况下实习生，只给select权限即可。
    
    mysql> grant all privileges on *.* to 'admin'@'%' identified by 'yangxin123456' with grant option;
    
    GRANT SELECT ON *.* TO netUser@'%'

all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。

on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“\*”表示所有数据库，所有表。如果我要指定将权限应用到test库的test表中，可以这么写：test.test。这里是针对的某个库的某个表。可以`test.*` 表示test库中的所有表

to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。  
identified by：指定用户的登录密码  
with grant option：表示允许用户将自己的权限授权给其它用户

### 5.3 、刷新权限(flush) ###

对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

mysql> flush privileges;

### 5.4、回收权限(revoke) ###

删除yangxin这个用户的create权限，该用户将不能创建数据库和表。

mysql> revoke create on *.* from 'yangxin@localhost';
    mysql> flush privileges;

### 5.5、删除用户 ###

mysql> select host,user from user;
    +---------------+---------+
    | host          | user    |
    +---------------+---------+
    localhost	HealerJean
    localhost	mysql.session
    localhost	mysql.sys
    localhost	root
    
    8 rows in set (0.00 sec)
    mysql> drop user 'HealerJean'@'localhost';

### 5.6、用户重命名 ###

mysql> rename user 'test3'@'%' to 'test1'@'%';

### 5.7、修改密码 ###

mysql> set password for 'root'@'localhost'=password('123456')

![ContactAuthor][]

[WX20180319-101525]: /images/20220529/00f022c367324ee5bd50ec88df15a08d.png
[WX20180319-102641]: /images/20220529/85e07c578f8f4f3eb15272890dbe6d9f.png
[WX20180319-105812]: /images/20220529/a7ec27b059b141f18bb4a6fbb25d554d.png
[WX20180319-105943]: https://raw.githubusercontent.com/HealerJean123/HealerJean123.github.io/master/blogImages/WX20180319-105943.png
[ContactAuthor]: /images/20220529/4cb582513086451b9590526d5b5e7199.png