MySQL实战45讲学习笔记:第四十二讲 grant之后要跟着flushprivileges吗
在MySQL⾥⾯,grant语句是⽤来给⽤户赋权的。不知道你有没有⻅过⼀些操作⽂档⾥⾯提到,grant之后要⻢上跟着执⾏⼀个 flush privileges命令,才能使赋权语句⽣效。我最开始使⽤MySQL的时候,就是照着⼀个操作⽂档的说明按照这个顺序操作 的。
那么,grant之后真的需要执⾏flush privileges吗?如果没有执⾏这个flush命令的话,赋权语句真的不能⽣效吗?
接下来,我就先和你介绍⼀下grant语句和flush privileges语句分别做了什么事情,然后再⼀起来分析这个问题。
为了便于说明,我先创建⼀个⽤户:
create user 'ua'@'%' identified by 'pa';
这条语句的逻辑是创建⼀个⽤户’ua’@’%’,密码是pa。注意,在MySQL⾥⾯,⽤户名(user)+地址(host)才表示⼀个⽤户,因此 ua@ip1 和 ua@ip2代表的是两个不同的⽤户。
这条命令做了两个动作:
1. 磁盘上,往mysql.user表⾥插⼊⼀⾏,由于没有指定权限,所以这⾏数据上所有表示权限的字段的值都是N;
2. 内存⾥,往数组acl_users⾥插⼊⼀个acl_user对象,这个对象的access字段值为0。
图1就是这个时刻⽤户ua在user表中的状态。
图1 mysql.user 数据⾏
在MySQL中,⽤户权限是有不同的范围的。接下来,我就按照⽤户权限范围从⼤到⼩的顺序依次和你说明。
全局权限
全局权限,作⽤于整个MySQL实例,这些权限信息保存在mysql库的user表⾥。如果我要给⽤户ua赋⼀个最⾼权限的话,语句 是这么写的:
grant all privileges on *.* to 'ua'@'%' with grant option;
这个grant命令做了两个动作:
1. 磁盘上,将mysql.user表⾥,⽤户’ua’@’%'这⼀⾏的所有表示权限的字段的值都修改为‘Y’;
2. 内存⾥,从数组acl_users中找到这个⽤户对应的对象,将access值(权限位)修改为⼆进制的“全1”。
在这个grant命令执⾏完成后,如果有新的客户端使⽤⽤户名ua登录成功,MySQL会为新连接维护⼀个线程对象,然后从 acl_users数组⾥查到这个⽤户的权限,并将权限值拷⻉到这个线程对象中。之后在这个连接中执⾏的语句,所有关于全局权 限的判断,都直接使⽤线程对象内部保存的权限位。
基于上⾯的分析我们可以知道:
1. grant 命令对于全局权限,同时更新了磁盘和内存。命令完成后即时⽣效,接下来新创建的连接会使⽤新的权限。
2. 对于⼀个已经存在的连接,它的全局权限不受grant命令的影响。
需要说明的是,⼀般在⽣产环境上要合理控制⽤户权限的范围。我们上⾯⽤到的这个grant语句就是⼀个典型的错误示范。如 果⼀个⽤户有所有权限,⼀般就不应该设置为所有IP地址都可以访问。
如果要回收上⾯的grant语句赋予的权限,你可以使⽤下⾯这条命令:
revoke all privileges on *.* from 'ua'@'%';
这条revoke命令的⽤法与grant类似,做了如下两个动作:
1. 磁盘上,将mysql.user表⾥,⽤户’ua’@’%'这⼀⾏的所有表示权限的字段的值都修改为“N”;
2. 内存⾥,从数组acl_users中找到这个⽤户对应的对象,将access的值修改为0。
db权限
除了全局权限,MySQL也⽀持库级别的权限定义。如果要让⽤户ua拥有库db1的所有权限,可以执⾏下⾯这条命令:
grant all privileges on db1.* to 'ua'@'%' with grant option;
基于库的权限记录保存在mysql.db表中,在内存⾥则保存在数组acl_dbs中。这条grant命令做了如下两个动作:
1. 磁盘上,往mysql.db表中插⼊了⼀⾏记录,所有权限位字段设置为“Y”;
2. 内存⾥,增加⼀个对象到数组acl_dbs中,这个对象的权限位为“全1”。
图2就是这个时刻⽤户ua在db表中的状态。
图2 mysql.db 数据⾏
每次需要判断⼀个⽤户对⼀个数据库读写权限的时候,都需要遍历⼀次acl_dbs数组,根据user、host和db找到匹配的对象, 然后根据对象的权限位来判断。
也就是说,grant修改db权限的时候,是同时对磁盘和内存⽣效的。
grant操作对于已经存在的连接的影响,在全局权限和基于db的权限效果是不同的。接下来,我们做⼀个对照试验来分别看⼀ 下。
图3 权限操作效果
需要说明的是,图中set global sync_binlog这个操作是需要super权限的。
可以看到,虽然⽤户ua的super权限在T3时刻已经通过revoke语句回收了,但是在T4时刻执⾏set global的时候,权限验证还 是通过了。这是因为super是全局权限,这个权限信息在线程对象中,⽽revoke操作影响不到这个线程对象。
⽽在T5时刻去掉ua对db1库的所有权限后,在T6时刻session B再操作db1库的表,就会报错“权限不⾜”。这是因为acl_dbs是 ⼀个全局数组,所有线程判断db权限都⽤这个数组,这样revoke操作⻢上就会影响到session B。
这⾥在代码实现上有⼀个特别的逻辑,如果当前会话已经处于某⼀个db⾥⾯,之前use这个库的时候拿到的库权限会保存在会话变量中。
你可以看到在T6时刻,session C和session B对表t的操作逻辑是⼀样的。但是session B报错,⽽session C可以执⾏成功。这 是因为session C在T2 时刻执⾏的use db1,拿到了这个库的权限,在切换出db1库之前,session C对这个库就⼀直有权限。
表权限和列权限
除了db级别的权限外,MySQL⽀持更细粒度的表权限和列权限。其中,表权限定义存放在表mysql.tables_priv中,列权限定 义存放在表mysql.columns_priv中。
这两类权限,组合起来存放在内存的hash结构column_priv_hash中。
这两类权限的赋权命令如下:
create table db1.t1(id int, a int); grant all privileges on db1.t1 to 'ua'@'%' with grant option; GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;
跟db权限类似,这两个权限每次grant的时候都会修改数据表,也会同步修改内存中的hash结构。因此,对这两类权限的操 作,也会⻢上影响到已经存在的连接。
看到这⾥,你⼀定会问,看来grant语句都是即时⽣效的,那这么看应该就不需要执⾏flush privileges语句了呀。
答案也确实是这样的。
flush privileges命令会清空acl_users数组,然后从mysql.user表中读取数据重新加载,重新构造⼀个acl_users数组。也就是 说,以数据表中的数据为准,会将全局权限内存数组重新加载⼀遍。
同样地,对于db权限、表权限和列权限,MySQL也做了这样的处理。
也就是说,如果内存的权限数据和磁盘数据表相同的话,不需要执⾏flush privileges。⽽如果我们都是⽤grant/revoke语句来 执⾏的话,内存和数据表本来就是保持同步更新的。
因此,正常情况下,grant命令之后,没有必要跟着执⾏flush privileges命令。
flush privileges使⽤场景
那么,flush privileges是在什么时候使⽤呢?显然,当数据表中的权限数据跟内存中的权限数据不⼀致的时候,flush privileges语句可以⽤来重建内存数据,达到⼀致状态。
这种不⼀致往往是由不规范的操作导致的,⽐如直接⽤DML语句操作系统权限表。我们来看⼀下下⾯这个场景:
图4 使⽤flush privileges
可以看到,T3时刻虽然已经⽤delete语句删除了⽤户ua,但是在T4时刻,仍然可以⽤ua连接成功。原因就是,这时候内存中 acl_users数组中还有这个⽤户,因此系统判断时认为⽤户还正常存在。
在T5时刻执⾏过flush命令后,内存更新,T6时刻再要⽤ua来登录的话,就会报错“⽆法访问”了。
直接操作系统表是不规范的操作,这个不⼀致状态也会导致⼀些更“诡异”的现象发⽣。⽐如,前⾯这个通过delete语句删除⽤ 户的例⼦,就会出现下⾯的情况:
图5 不规范权限操作导致的异常
可以看到,由于在T3时刻直接删除了数据表的记录,⽽内存的数据还存在。这就导致了:
1. T4时刻给⽤户ua赋权限失败,因为mysql.user表中找不到这⾏记录;
2. ⽽T5时刻要重新创建这个⽤户也不⾏,因为在做内存判断的时候,会认为这个⽤户还存在。
⼩结
今天这篇⽂章,我和你介绍了MySQL⽤户权限在数据表和内存中的存在形式,以及grant和revoke命令的执⾏逻辑。
grant语句会同时修改数据表和内存,判断权限的时候使⽤的是内存数据。因此,规范地使⽤grant和revoke语句,是不需要随 后加上flush privileges语句的。
flush privileges语句本身会⽤数据表的数据重建⼀份内存权限数据,所以在权限数据可能存在不⼀致的情况下再使⽤。⽽这种 不⼀致往往是由于直接⽤DML语句操作系统权限表导致的,所以我们尽量不要使⽤这类语句。
另外,在使⽤grant语句赋权时,你可能还会看到这样的写法:
grant super on *.* to 'ua'@'%' identified by 'pa';
这条命令加了identified by ‘密码’, 语句的逻辑⾥⾯除了赋权外,还包含了:
1. 如果⽤户’ua’@’%'不存在,就创建这个⽤户,密码是pa;
2. 如果⽤户ua已经存在,就将密码修改成pa。
这也是⼀种不建议的写法,因为这种写法很容易就会不慎把密码给改了。
“grant之后随⼿加flush privileges”,我⾃⼰是这么使⽤了两三年之后,在看代码的时候才发现其实并不需要这样做,那已经是 2011年的事情了。
去年我看到⼀位⼩伙伴这么操作的时候,指出这个问题时,他也觉得很神奇。
因为,他和我⼀样看的第⼀份⽂档就是这么写 的,⾃⼰也⼀直是这么⽤的。
所以,今天的课后问题是,请你也来说⼀说,在使⽤数据库或者写代码的过程中,有没有遇到过类似的场景:误⽤了很⻓时间 以后,由于⼀个契机发现“啊,原来我错了这么久”?
你可以把你的经历写在留⾔区,我会在下⼀篇⽂章的末尾选取有趣的评论和你分享。感谢你的收听,也欢迎你把这篇⽂章分享 给更多的朋友⼀起阅读。
总结表格如下: