# 《MySQL核心知识》第13章：权限管理
大家好，我是冰河~~
今天是《MySQL核心知识》专栏的第13章，今天为大家系统的讲讲MySQL中的权限管理，希望通过本章节的学习，小伙伴们能够举一反三，彻底掌握MySQL中的权限管理相关的知识。好了，开始今天的正题吧。
# 权限概述
在mysql数据库中，有mysql_install_db脚本初始化权限表，存储权限的表有：
- user表
- db表
- host表
- table_priv表
- columns_priv表
- proc_priv表
MySQL存取控制包含2个阶段：
- 阶段1：服务器检查你是否允许连接。
- 阶段2：假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。例如，如果你从数据库中一个表精选(select)行或从数据库抛弃一个表，服务器确定你对表有select权限或对数据库有drop权限。
服务器在存取控制的两个阶段使用在mysql的数据库中的user、db和host表，在这些授权表中字段如下：
| 表名称 | user | db | host |
|---|---|---|---|
| 范围字段 | Host | Host | Host |
User | Db | Db | |
Password | User | ||
| 权限字段 | Select_priv | Select_priv | Select_priv |
Insert_priv | Insert_priv | Insert_priv | |
Update_priv | Update_priv | Update_priv | |
Delete_priv | Delete_priv | Delete_priv | |
Index_priv | Index_priv | Index_priv | |
Alter_priv | Alter_priv | Alter_priv | |
Create_priv | Create_priv | Create_priv | |
Drop_priv | Drop_priv | Drop_priv | |
Grant_priv | Grant_priv | Grant_priv | |
Reload_priv | |||
Shutdown_priv | |||
Process_priv | |||
File_priv |
对存取控制的第二阶段(请求证实)，如果请求涉及表，服务器可以另外参考tables_priv和columns_priv表。这些表的字段如下：
| 表名称 | tables_priv | columns_priv |
|---|---|---|
| 范围字段 | Host | Host |
Db | Db | |
User | User | |
Table_name | Table_name | |
Column_name | ||
| 权限字段 | Table_priv | Column_priv |
Column_priv | ||
| 其他字段 | Timestamp | Timestamp |
Grantor |
每个授权表包含范围字段和权限字段。
user表主要分为：用户列、权限列、安全列、资源控制列
host表主要分为：用户列、权限列
这里美中不足的是mysql.user 没有一个列是保存用户创建时间的
有时候排查用户问题的时候，比如某个客户在某个时间说连接不上数据库，我们在user表里只能查到是否存在那个用户
但是不知道这个用户的创建时间，也就是说客户说的那个时间究竟用户是否已经创建我们是不知道的
# 帐户管理
MYSQL提供许多语句用来管理用户帐号，这些语句可以用来包括登录和退出MYSQL服务器、创建用户、删除用户、密码管理、权限管理
MYSQL数据库的安全性，需要通过帐户管理来保证登录和退出MYSQL
mysql命令的常用参数
- -h：主机名或ip，默认是localhost，最好指定-h参数
- -u：用户名
- -p：密码，注意：该参数后面的字符串和-p不能有空格
- -P：端口号，默认为3306
- 数据库名：可以在命令最后指定数据库名
- -e：执行SQL语句，如果指定该参数，将在登录后执行-e后面的命令或sql语句并退出
命令执行完之后返回book表的结构，查询返回之后会自动退出MYSQL
# 用户
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']]
2
新建普通用户
CREATE USER 'jeffrey'@'localhost' identified BY 'mypass';
用户名部分为“jeffrey”，主机名默认为“%”（即对所有主机开放权限）
如果指定用户登录不需要密码，则可以省略identified BY部分，对于使用插件认证连接的用户，服务器调用指定名称的插件，客户端需要提供验证方法所需要的凭据。如果创建用户时或者连接服务器时，服务器找不到对应的插件，将返回一个错误。
identified with语法
CREATE user 'jeffrey'@'localhost' identified with my_auth_plugin;
identified with只能在MYSQL5.7.7及以上版本使用。
identified with和identified by是互斥的，所以对一个帐户来说只能使用一个验证方法。
CREATE USER语句的操作会被记录到服务器日志文件或者操作历史文件中
例如 ~/.mysql_history。这意味着对这些文件有读取权限的人，都可以读取到新添加用户的明文密码
一个办法就是新建用户的时候使用password关键字
CREATE user 'tom'@'localhost' identified BY password'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
SELECT password('mypass');
SELECT * FROM `mysql`.`user` WHERE `User` ='tom';
2
3
先查出密码的哈希值，然后在新建用户的时候输入哈希值
那么在日志里面就只能看到哈希值
# 使用GRANT语句创建新用户
GRANT USER语句可以用来创建帐户，通过该语句可以在user表中添加一条新记录。比起CREATE USER语句创建的新用户，还需要使用GRANT语句赋予用户权限。
使用GRANT语句创建新用户时必须有GRANT权限。
语法
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
2
3
4
5
6
7
8
9
10
11
使用GRANT语句创建一个新用户testUser，密码为testpwd，并授予用户对所有数据表的SELECT和UPDATE权限
GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'
SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv` FROM mysql.user WHERE `User` ='testUser';
2
执行结果显示执行成功，使用SELECT语句查询用户testUser的权限
查询结果显示SELECT和UPDATE权限字段均为Y
注意：User表中的user和host字段区分大小写，在查询的时候要指定正确的用户名或主机名
# 直接操作MYSQL用户表
不管是CREATE USER还是GRANT USER，在创建用户时，实际上都是在user表中添加一条新记录。使用INSERT语句向mysql.user表INSERT一条记录来创建一个新用户
插入的时候必须要有INSERT权限
INSERT INTO mysql.user(host,user,password,[privilegelist])
VALUES ('host','username',password('password'),privilegevaluelist)
2
使用INSERT创建一个新用户，其用户名称为customer1，主机名为localhost，密码为customer1
INSERT INTO mysql.user(host,user,password)
VALUES ('localhost','customer1',password('customer1'))
2
语句执行失败，查看警告信息如下：
show WARNINGS ;
因为ssl_cipher这个字段在user表中没有定义默认值，所以在这里提示错误信息。影响insert语句的执行，使用SELECT语句查看user表中的记录
可以看到，插入失败。
# 删除普通用户
使用DROP USER语句删除用户，也可以直接通过DELETE从mysql.user表中删除对应的记录来删除用户。DROP USER语句用于删除一个或多个MYSQL帐户。要使用DROP USER，必须拥有MYSQL数据库的全局CREATE USER 权限或DELETE权限。
删除testUser这个用户
DROP user 'testUser'@'localhost';
可以发现testUser这个用户已经删除了
使用delete语句删除用户
DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'
# root用户修改自己的密码
修改root密码的方式有多种
1、使用mysqladmin命令在命令行指定新密码
mysqladmin -u root -p password"rootpwd"
2、修改mysql数据库的user表
UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'
password('')函数用来加密用户密码。执行update之后需要执行flush privileges语句重新加载用户权限
3、使用SET语句修改root用户的密码
SET PASSWORD语句可以用来重新设置其他用户的登录密码或者自己使用的帐户密码，语法
SET PASSWORD=PASSWORD("ROOTPWD")
新密码必须用PASSWORD函数加密
使用root用户登录到mysql之后执行下面语句
SET password=password('123456')
执行之后需要使用执行flush privileges语句或者重启MYSQL重新加载用户权限
# root用户修改普通用户密码
1、使用SET语句修改普通用户的密码
SET PASSWORD FOR 'USER'@'HOST' =PASSWORD("ROOTPWD")
2、使用update语句修改普通用户的密码
UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'
执行完毕之后需要使用flush privileges语句或者重启MYSQL重新加载用户权限
3、使用GRANT语句修改普通用户密码
GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIFIED BY 'somepwd'
使用下面语句把testUser用户的密码改为123456
grant USAGE ON *testUser*TO 'localhost' identified BY '123456';
注意：使用GRANT语句和MYSQLADMIN设置密码，他们均会加密密码，这种情况下，不需要使用PASSWORD()函数
# 普通用户修改密码
使用SET语句修改自己的密码
SET password=password('newpassword');
比如修改testUser这个用户的密码，需要使用testUser这个用户登录到mysql，然后执行
SET password=password('123456');
# root用户密码丢失的解决办法
使用--skip-grant-tables选项启动MYSQL服务
使用--skip-grant-tables选项启动MYSQL时，服务器将不加载权限判断，任何用户都能访问数据库。
1.关闭MySQL服务器
service mysqld stop
2.使用--skip-grant-tables选项重启MySQL服务
./bin/mysqld_safe --skip-grant-tables --user=root &
其中--skip-grant-tables选项的意思是启动MySQL服务的时候跳过权限表认证。启动后，连接到MySQL的root将不需要命令。
3.用空密码的root用户连接到MySQ，并且更新root口令：
mysql -uroot
MySQL 5.7以下版本：
update mysql.user set password=password('新密码') where user='root' and host='localhost';
MySQL 5.7版本：
update user set authentication_string=password('新密码') where user='root' and Host='localhost';
4.刷新权限表，使得权限认证重新生效：
flush privileges;
5.重启MySQL数据库
service mysqld restart
6.登录MySQL
mysql -uroot
# 权限管理
MYSQL中的各种权限
对于GRANT和REVOKE语句，priv_type可以被指定为以下任何一种：
| 权限 | 意义 |
|---|---|
| ALL [PRIVILEGES] | 设置除GRANT OPTION之外的所有简单权限 |
| ALTER | 允许使用ALTER TABLE |
| ALTER ROUTINE | 更改或取消已存储的子程序 |
| CREATE | 允许使用CREATE TABLE |
| CREATE ROUTINE | 创建已存储的子程序 |
| CREATE TEMPORARY 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_connections。 |
| UPDATE | 允许使用UPDATE |
| USAGE | “无权限”的同义词 |
| GRANT OPTION | 允许授予权限 |
当从旧版本的MySQL升级时，要使用EXECUTE, CREATE VIEW, SHOW VIEW, CREATE USER, CREATE ROUTINE和ALTER ROUTINE权限
# 授权
授权就是为某个用户授予权限
授予的权限可以分为多个层级：
全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。
数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ONdb_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。
子程序层级
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
当后续目标是一个表、一个已存储的函数或一个已存储的过程时，object_type子句应被指定为TABLE、FUNCTION或PROCEDURE。当从旧版本的MySQL升级时，要使用本子句，必须升级授权表。使用GRANT语句创建一个新用户grantUser，密码为“grantpwd”。用户对所有的数据有查询、插入权限，并授予GRANT权限
GRANT SELECT ,INSERT ON *.*TO 'grantUser'@'localhost' identified BY '123456' WITH GRANT OPTION ;
查询显示grantUser被创建成功，并赋予了SELECT、INSERT、GRANT权限，其相应字段值为Y。被授予GRANT权限的用户可以登录MYSQL并创建其他用户帐户，在这里是grantUser的用户。
# 收回权限
收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号记录依然在user表中保存。
语法
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
2
3
4
5
使用REVOKE语句，必须拥有mysql数据库的全局CREATE权限或UPDATE权限
使用REVOKE语句取消用户grantUser的INSERT权限
REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';
可以看到grantUser用户的INSERT权限已经被收回了
注意：当从旧版本的MYSQL升级时，如果要使用EXECUTE、CREATE VIEW、SHOW VIEW、CREATE USER、CREATE ROUTINE、ALTER ROUTINE
权限，必须先升级授权表
# 查看权限
SHOW GRANT语句可以显示用户的权限信息
语法
show grants FOR 'user'@'host';
使用SHOW GRANT语句查询用户grantUser的权限信息
show grants FOR 'grantUser'@'localhost';
返回结果显示了user表中的帐户信息；接下来以为GRANT SELECT ON关键字开头，表示用户被授予了SELECT权限；
*.*表示SELECT权限作用于所有数据库的所有数据表；
IDENTIFIED BY 后面的为用户加密后的密码
在这里，只是定义了个别的用户权限，GRANT可以显示更加详细的权限信息，包括全局级的和非全局级的权限
如果表层级或者列层级的权限被授予用户的话，他们也能在结果中显示出来。
# 查看MYSQL里面匿名用户
如果有匿名用户，那么客户端就可以不用密码登录MYSQL数据库，这样就会存在安全隐患
检查匿名用户的方法
SELECT * FROM mysql.user WHERE `User`='';
如果查找到user字段值为空的那条记录，说明存在匿名用户，需要把这条记录删除。如果用匿名用户登录MYSQL就可以看到用户名是空的
删除语句
DELETE FROM mysql.user WHERE `User`=''; SELECT * FROM mysql.user WHERE `User`='';
好了，如果文章对你有点帮助，记得给冰河一键三连哦，欢迎将文章转发给更多的小伙伴，冰河将不胜感激~~
# 星球服务
加入星球，你将获得：
1.项目学习：微服务入门必备的SpringCloud Alibaba实战项目、手写RPC项目—所有大厂都需要的项目【含上百个经典面试题】、深度解析Spring6核心技术—只要学习Java就必须深度掌握的框架【含数十个经典思考题】、Seckill秒杀系统项目—进大厂必备高并发、高性能和高可用技能。
2.框架源码：手写RPC项目—所有大厂都需要的项目【含上百个经典面试题】、深度解析Spring6核心技术—只要学习Java就必须深度掌握的框架【含数十个经典思考题】。
3.硬核技术：深入理解高并发系列（全册）、深入理解JVM系列（全册）、深入浅出Java设计模式（全册）、MySQL核心知识（全册）。
4.技术小册：深入理解高并发编程（第1版）、深入理解高并发编程（第2版）、从零开始手写RPC框架、SpringCloud Alibaba实战、冰河的渗透实战笔记、MySQL核心知识手册、Spring IOC核心技术、Nginx核心技术、面经手册等。
5.技术与就业指导：提供相关就业辅导和未来发展指引，冰河从初级程序员不断沉淀，成长，突破，一路成长为互联网资深技术专家，相信我的经历和经验对你有所帮助。
冰河的知识星球是一个简单、干净、纯粹交流技术的星球，不吹水，目前加入享5折优惠，价值远超门票。加入星球的用户，记得添加冰河微信：hacker_binghe，冰河拉你进星球专属VIP交流群。
# 星球重磅福利
跟冰河一起从根本上提升自己的技术能力，架构思维和设计思路，以及突破自身职场瓶颈，冰河特推出重大优惠活动，扫码领券进行星球，直接立减149元，相当于5折， 这已经是星球最大优惠力度！
领券加入星球，跟冰河一起学习《SpringCloud Alibaba实战》、《手撸RPC专栏》和《Spring6核心技术》，更有已经上新的《大规模分布式Seckill秒杀系统》，从零开始介绍原理、设计架构、手撸代码。后续更有硬核中间件项目和业务项目，而这些都是你升职加薪必备的基础技能。
100多元就能学这么多硬核技术、中间件项目和大厂秒杀系统，如果是我，我会买他个终身会员！
# 其他方式加入星球
- 链接 ：打开链接 http://m6z.cn/6aeFbs (opens new window) 加入星球。
- 回复 ：在公众号 冰河技术 回复 星球 领取优惠券加入星球。
特别提醒： 苹果用户进圈或续费，请加微信 hacker_binghe 扫二维码，或者去公众号 冰河技术 回复 星球 扫二维码加入星球。
# 星球规划
后续冰河还会在星球更新大规模中间件项目和深度剖析核心技术的专栏，目前已经规划的专栏如下所示。
# 中间件项目
- 《大规模分布式定时调度中间件项目实战（非Demo）》：全程手撸代码。
- 《大规模分布式IM（即时通讯）项目实战（非Demo）》：全程手撸代码。
- 《大规模分布式网关项目实战（非Demo）》：全程手撸代码。
- 《手写Redis》：全程手撸代码。
- 《手写JVM》全程手撸代码。
# 超硬核项目
- 《从零落地秒杀系统项目》：全程手撸代码，在阿里云实现压测（已上新）。
- 《大规模电商系统商品详情页项目》：全程手撸代码，在阿里云实现压测。
- 其他待规划的实战项目，小伙伴们也可以提一些自己想学的，想一起手撸的实战项目。。。
既然星球规划了这么多内容，那么肯定就会有小伙伴们提出疑问：这么多内容，能更新完吗？我的回答就是：一个个攻破呗，咱这星球干就干真实中间件项目，剖析硬核技术和项目，不做Demo。初衷就是能够让小伙伴们学到真正的核心技术，不再只是简单的做CRUD开发。所以，每个专栏都会是硬核内容，像《SpringCloud Alibaba实战》、《手撸RPC专栏》和《Spring6核心技术》就是很好的示例。后续的专栏只会比这些更加硬核，杜绝Demo开发。
小伙伴们跟着冰河认真学习，多动手，多思考，多分析，多总结，有问题及时在星球提问，相信在技术层面，都会有所提高。将学到的知识和技术及时运用到实际的工作当中，学以致用。星球中不少小伙伴都成为了公司的核心技术骨干，实现了升职加薪的目标。
# 联系冰河
# 加群交流
本群的宗旨是给大家提供一个良好的技术学习交流平台，所以杜绝一切广告！由于微信群人满 100 之后无法加入，请扫描下方二维码先添加作者 “冰河” 微信(hacker_binghe)，备注：星球编号。
# 公众号
分享各种编程语言、开发技术、分布式与微服务架构、分布式数据库、分布式事务、云原生、大数据与云计算技术和渗透技术。另外，还会分享各种面试题和面试技巧。内容在 冰河技术 微信公众号首发，强烈建议大家关注。
# 视频号
定期分享各种编程语言、开发技术、分布式与微服务架构、分布式数据库、分布式事务、云原生、大数据与云计算技术和渗透技术。另外，还会分享各种面试题和面试技巧。
# 星球
加入星球 冰河技术 (opens new window)，可以获得本站点所有学习内容的指导与帮助。如果你遇到不能独立解决的问题，也可以添加冰河的微信：hacker_binghe， 我们一起沟通交流。另外，在星球中不只能学到实用的硬核技术，还能学习实战项目！
关注 冰河技术 (opens new window)公众号，回复 星球 可以获取入场优惠券。
← 第12章：触发器 第14章：数据备份与恢复 →
