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

目录

1 MySQL 权限管理概念

2 用户管理

2.1 创建用户

2.2 查看当前登录用户

2.3 修改用户名

2.4 删除用户

3 授予权限

3.1 授予用户管理员权限

3.2 授予用户数据库权限

3.3 授予用户表权限

3.4 授予用户列权限

4 查询权限

5 回收权限

---

1 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 的权限分布，权限分布就是针对表可以设置什么权限，针对列可以设置什么权限等等

权限分布

可能的设置的权限

表权限

'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'

列权限

'Select', 'Insert', 'Update', 'References'

过程权限

'Execute', 'Alter Routine', 'Grant'

2 用户管理 2.1 创建用户

创建用户的格式

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'

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

2.3 修改用户名

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

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

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

3 授予权限

授权格式

grant [权限1,权限2,权限3] on [数据库].[表名] to [用户名]@[访问地址] 3.1 授予用户管理员权限 # 授予 'test1'@'localhost' 管理员权限 grant all privileges on *.* to 'test1'@'localhost' with grant option;

all privileges：表示将所有权限授予给用户。也可指定具体的权限，如SELECT、CREATE、DROP 等。on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写 “*” 表示当前数据库的所有表，“*.*” 表示所有数据库的所有表。如果要指定将权限应用到 test1 库的 user 表中，可以这么写：test1.userto：将权限授予哪个用户with grant option：允许被授权的用户把得到的权限继续授给其它用户 3.2 授予用户数据库权限 数据库权限说明select表示授予用户可以使用 select 语句访问特定数据库中所有表和视图的权限insert表示授予用户可以使用 insert 语句向特定数据库中所有表添加数据行的权限delete表示授予用户可以使用 delete 语句删除特定数据库中所有表的数据行的权限update表示授予用户可以使用 update 语句更新特定数据库中所有数据表的值的权限references表示授予用户可以创建指向特定的数据库中的表外键的权限create表示授权用户可以使用 create table 语句在特定数据库中创建新表的权限alter表示授予用户可以使用 alter table 语句修改特定数据库中所有数据表的权限show view表示授予用户可以查看特定数据库中已有视图的视图定义的权限create routine表示授予用户可以为特定的数据库创建存储过程和存储函数的权限alter routine表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限execute routine表示授予用户可以调用特定数据库的存储过程和存储函数的权限index表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限drop表示授予用户可以删除特定数据库中所有表和视图的权限create temporary tables表示授予用户可以在特定数据库中创建临时表的权限create view表示授予用户可以在特定数据库中创建新的视图的权限lock tables表示授予用户可以锁定特定数据库的已有数据表的权限all 或 all privileges表示以上所有权限# 创建用户 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';

3.3 授予用户表权限​​​​​​​ # 创建用户 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';

3.4 授予用户列权限 列权限说明select(字段）表示授予用户可以使用 select 语句访问特定列的权限insert(字段）表示授予用户可以使用 insert 语句向一个特定列插入数据的权限update(字段）表示授予用户可以使用 update 语句更新特定列的权限# 创建用户 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';

4 查询权限

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

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

5 回收权限

回收权限格式

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';