在MySQL执行grant修改用户权限后,都习惯性执行一条flush privileges命令想让修改生效。在刚接触MySQL的时候我也是严格遵守该规范,但似乎并没有去了解flush privileges后面的具体操作,以及这个命令是否真的必要

全局权限

全局权限作用于整个实例,这些权限信息保存在mysql.user表里。例如我们要赋予用户一个最高权限

grant all privileges on *.* to 'test'@'%';

对于这个grant语句,实际执行了两个动作

  • 磁盘上,对mysql.user表里用户的记录上的权限字段全部修改为Y
  • 内存上,从数组acl_users中找到用户对象,将access值修改为二进制的1

在grant执行完成后,如果有新的客户端通过用户连接成功,MySQL会创建一个线程对象,然后从acl_users中查询用户的权限,并将权限值拷贝到线程对象中。之后连接内执行的所有操作权限判断都基于线程对象内部保存的权限位。

因此,grant命令对于全局权限,同时更新了磁盘和内存。命令执行完成后立即生效,后续的连接都能获取该权限。对于一个已经存在的连接,它的全局权限不受grant命令的影响。

db权限

db权限作用于具体的某个DB,这些权限信息保存在mysql.db中。例如我们要赋予用户一个select库内所有对象的权限

SQL> grant all privileges on test.* to 'test'@'%';

对于这个grant语句,实际执行了两个动作

  • 磁盘上,对于mysql.db中插入一行记录,所有权限字段设置为Y
  • 内存上,增加一个对象到数组acl_dbs中,对象的权限位全部为1

grant修改db权限的时候,是同时对磁盘和内存生效的。每次要判断一个用户对一个数据库读写权限的时候,都需要遍历acl_dbs数组,根据user、host和db找到匹配的对象,再根据对象的权限位来判断

grant 操作对于已经存在的会话的影响,在全局权限和基于 db 的权限效果是不同的,可以通过一个简单的测试进行对照
grant test

通过观察上述例子,我们可以观察到T3时已经回收了super权限,但是T4执行set global的时候依旧成功了,也进一步验证了revoke全局权限不影响当前会话权限。而在T5回收db权限后,SESSION B再次重建表就会报错权限不足,这是因为acl_dbs是一个全局数组,revoke操作会立刻影响到SESSION B。

但我们会发现SESSION C的T6执行重建表还是成功了,这里存在一个特殊的逻辑,就是当我们通过USE切换到数据库中,会把对这个库的权限保存在会话变量里面,因此SESSION C就还保有之前的权限。

表权限和列权限

MySQL 支持更细粒度的表权限和列权限,表权限记录在mysql.table_priv中,列权限记录在mysql.columns_priv中。这两类权限组合放在内存中hash结构的column_priv_hash中。

跟db权限类似,这两个权限每次grant的时候会修改数据表以及内存hash结构。因此,也会影响到已存在的会话。

flush privileges命令会清空内存数据,然后从磁盘再次读取数据加载到内存中重新构建数组。也就是说,如果内存数据和磁盘数据相同的话,是不需要执行flush命令的。只有当我们手动对系统权限表进行DML,导致内存和磁盘数据不一致的情况下需要执行flush,例如我们直接update mysql.user修改表权限。