MySQL 基础知识（八）之用户权限管理

        关于 MySQL 的权限简单的理解就是 MySQL 允许你做你权利以内的事情，不可以越界。比如只允许你执行 select 操作，那么你就不能执行 update 操作。存储账户权限信息的表主要有四个：user 表、db 表、tables_priv  表、columns_priv 表。

用户权限信息表：

• user 表：user 表存储了允许连接到服务器的用户信息，并且它指定这些用户有哪种全局（超级用户、管理员）权限。在 user 表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了 DELETE 权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。
• db 表：db 表存储了用户对某个数据库的操作权限，在这里指定的权限适用于一个数据库中的所有表。
• tables_priv 表：tables_priv 表存储了用户对某个表的操作权限，在这里指定的一个权限适用于一个表的所有列。
• columns_priv 表：columns_priv 表存储了用户对表中某列的操作权限，在这里指定的权限适用于一个表的特定列。

MySQL 用户权限管理主要有以下作用：

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

MySQL 权限信息表的验证过程分为两个阶段：连接权限验证和执行权限验证

• 先从 user 表中的 Host、User、Password （MySQL 8.0 版本没有 Password，有 authentication_string，它是对密码加密后得到的密文）这 3 个字段中判断连接的 IP、用户名、密码是否存在，存在则通过验证

• 通过连接权限验证后，进行权限分配，按照 user、db、tables_priv、columns_priv 的顺序进行验证

  • 校验 user 表，对于全局权限是 ok → 直接执行
  • 检验 db 表，对于某个特定的数据库有权限 → 执行
  • 检验 tables_priv 表，对于特定数据库下的某些表是有权限 → 执行
  • 检验 columns_priv 表，对于特定表中的某些列有权限 → 执行 

 MySQL 的权限分布，权限分布就是针对表可以设置什么权限，针对列可以设置什么权限等等

创建用户的格式

create user [用户名]@[访问地址] identified by [密码]

       访问地址（登录主机名）表示可以在哪些网络地址上登录账户连接（访问）数据库，可以为 'localhost'、'%' 或 '196.68.%' 这个网络下的地址等

• 'localhost' 表示本机，IPv4 中 localhost 的地址为 '127.0.0.1'
• '%' 通配符表示任何主机都可以连接

        设置相同的用户名，不同的访问地址（主机名），这样我们可以在不同的主机（网络地址）上登录账户连接数据。根据本文 1 MySQL 权限管理概念中的连接权限验证，只有当访问地址、用名名、密码验证通过后才能登录账户连接数据库。

# 创建本地登录的用户 test1，密码为 123456
create user 'test1'@'localhost' identified by '123456';
 
# 创建任何主机都可以连接的用户 test2，密码为 123456
create user 'test2'@'%' identified by '123456';
 
# 创建访问地址为'127.0.0.2' 的用户 test3，密码为 123456
create user 'test3'@'127.0.0.2' identified by '123456';
 
# 查看账号、访问地址、密码
select user, host, authentication_string from mysql.user;

在本地登录 test3 ，结果是：拒绝连接，因为没有 'test'@'localhost'

# 查看当前登录的用户
select user();
 
select current_user();

rename 除了用来更改用户名外，还可以更改访问地址

# 更改用户名和访问地址
rename user 'test1'@'localhost' to 'test'@'%';
 
#查看用户名和访问地址
select user, host from mysql.user;

# 删除用名为 test2、访问地址为 '%' 的用户
drop user 'test2'@'%';

授权格式

grant [权限1,权限2,权限3] on [数据库].[表名] to [用户名]@[访问地址]

# 授予 'test1'@'localhost' 管理员权限
grant all privileges on *.* to 'test1'@'localhost' with grant option;

• all privileges：表示将所有权限授予给用户。也可指定具体的权限，如SELECT、CREATE、DROP 等。
• on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写 “*” 表示当前数据库的所有表，“*.*” 表示所有数据库的所有表。如果要指定将权限应用到 test1 库的 user 表中，可以这么写：test1.user
• to：将权限授予哪个用户
• with grant option：允许被授权的用户把得到的权限继续授给其它用户

# 创建用户 test
create user 'test'@'localhost' identified by '123456';
 
# 授予用户 test 在 study 数据库中查询、创建、修改、删除表的权限以及创建视图的权限
grant select, create, alter, drop, create view on study.* to 'test'@'localhost';
 
# 查询用户 test 权限
show grants for 'test'@'localhost';

# 创建用户 learn1
create user 'learn1'@'localhost' identified by '123456';
 
# 授予用户 learn1 查询、创建、修改 study 数据库的 goods 表的权限
grant select, create, alter on study.goods to 'learn1'@'localhost';
 
# 查询用户 learn1 权限
show grants for 'learn1'@'localhost';

# 创建用户 learn2
create user 'learn2'@'localhost' identified by '123456';
 
# 授予用户 learn2 查询、插入、更新 study 数据库的 goods 表的 name 列的权限
grant select(name), insert(name), update(name) on study.goods to 'learn2'@'localhost';
 
# 查询用户 learn2 权限
show grants for 'learn2'@'localhost';

​

使用 show grants; 查询 root 的权限时会出错

# 查询当前用户权限
# show grants()；
 
# 查询特定用户权限，如 learn2
show grants for 'learn2'@'localhost';

​

回收权限格式

revoke [权限1,权限2,权限3] on [数据库].[表名] from [用户名]@[访问地址]

注意回收权限是回收已经授予用户的权限，不能回收用户没有的权限

# 回收用户 learn2 插入、更新 study 数据库的 goods 表的 name 列的权限
revoke insert(name), update(name) on study.goods from 'learn2'@'localhost';
 
# 查询你 用户 learn2 的权限
show grants for 'learn2'@'localhost';

​