42 grant与flush privileges
在mysql里， grant是给用户赋权的，一些文档中经常提到在grant执行后，马上执行一个flush privileges，才能使赋权语句生效，那么，grant之后真的需要执行flush privileges吗？如果没有执行这个flush操作，赋权语句能否生效？
(root@localhost:mysql.sock) [(none)]> create user 'ua'@'%' identified by 'pa';
Query OK, 0 rows affected (0.06 sec)
语句逻辑创建一个用户’ua’@’%’，密码pa，在mysql里，user+host才表示一个用户。
--在磁盘上，往mysql.user表中插入一行，由于没有指定权限，在这行数据上表示权限的字段都是N
--内存里，往数组acl_users里插入一个acl_user对象，对象的值为0
全局权限
全局权限，作用于整个mysql实例，信息保存在mysql.user里，赋予最高权限。
(root@localhost:mysql.sock) [(none)]> grant all privileges on *.* to 'ua'@'%' with grant option; Query OK, 0 rows affected (0.00 sec)
这个命令的动作
--磁盘上，在mysql.user表里，把’ua’@’%’这一行对应权限的值都修改为Y
--内存里，把acl_users对应对象的值改为1
--1 grant命令对于全局权限，同时更新了磁盘和内存，命令完成后即使生效，接下来新创建的连接会使用新的权限
--2 对于一个已经存在的连接，它的全局权限不受grant的影响。
(root@localhost:mysql.sock) [(none)]> grant all privileges on db1.* to 'ua'@'%' with grant option; Query OK, 0 rows affected (0.00 sec)
回收上面的权限
db权限
除了全局权限，mysql还支持db级的权限定义，
(root@localhost:mysql.sock) [(none)]> grant all privileges on db1.* to 'ua'@'%' with grant option; Query OK, 0 rows affected (0.00 sec)
让用户’ua’@’%’用于db1的所有权限。基于db级的权限，保存在mysql.db表中，
--磁盘上，在mysql.db表中，对应的权限修改为Y
--内存里，增加一个对象到acl_dbs中，值为1
每次需要判断一个用户对一个数据库读写权限的时候，都要遍历一次acl_dbs数组，根据userhostdb找到匹配的对象，然后根据对象的权限位来判断。
也就是说，grant修改db权限的时候，是同时对磁盘和内存生效的。
|
SESSION A |
SESSION B |
SESSION C |
|
|
T1 |
Connect(root,root) Create database db1; Create user ‘ua’@’%’ identified by ‘pa’; Grant super on *.* to ‘ua’@’%’; Grant all privileges on db1.* to ‘ua’@’%’; |
||
|
T2 |
Connect(ua,pa) Set global sync_binlog=1; Create table db1.t(c int); |
Connect(ua,pa) Use db1; |
|
|
T3 |
Revoke super on *.* from ‘ua’@’%’; |
||
|
T4 |
Set global sync_binlog=1; Alter table db1.t engine=innodb; |
Alter table db1.t engine=innodb; |
|
|
T5 |
Revoke all privileges on db1.* from ‘ua’@’%’; |
||
|
T6 |
Set global sync_binlog=1; Alter table db1.t engine=innodb;(command denied) |
Alter table db1.t engine=innodb; |
需要说明的是，set global sync_binlog需要super权限。
可以看到，虽然用ua的super权限在t3时刻被收回，但是在t4时刻执行set global的时候，权限认证还是通过了，这是因为super是全局权限，这个权限信息再线程对象中，而revoke操作影响不了这个线程对象。
在t5时刻去掉了ua对db1的所有权限，在t6时刻执行db1库的表就会报权限不足，因为acl_dbs是一个全局数组，所有线程判断db权限都用这个数组，这样revoke操作影响了session B
如果当前会话已经处于某一个db里面，之前use这个库的时候拿到的权限会保存在会话变量中。
表权限和列权限
除了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;
Flush privileges命令会清空acl_users数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_users数组。对应db权限、表权限、列权限做了同样的处理。
因此正常情况下，grant命令之后，没有必要跟着执行flush privileges命令。
Flush privileges使用场景
Flush privileges语句用来重建内存数据，比如直接用dml操作系统权限表，就需要flush privileges来刷新权限。
|
SESSION A |
SESSION B |
|
|
T1 |
Connect(root,root) Create user ‘ua’@’%’ identified by ‘pa’; |
|
|
T2 |
Connect(ua,pa) Disconnect |
|
|
T3 |
Delete from mysql.user where user=’ua’ |
|
|
T4 |
Connect(ua,pa) Disconnect |
|
|
T5 |
Flush privileges |
|
|
T6 |
Connect(ua,pa)--access denied |
在T3时刻，用delete语句删除了用户ua，但是在t4时刻，ua用户仍让可以连接，原因就是这个时候内存中acl_users数组中还有这个用户，
在T5时候，经过flush刷新内存后，T6时刻在用ua来登录，就会被拒绝。
直接操作系统表是规范的操作，这个不一致状态会导致一些异常的错误。
|
Client A |
|
|
T1 |
Connect(root,root) Create user ‘ua’@’%’ identified by ‘pa’; |
|
T2 |
|
|
T3 |
delete from mysql.user where user='ua'; |
|
T4 |
grant super on *.* to 'ua'@'%' with grant option; ERROR 1133 (42000): Can't find any matching row in the user table |
|
T5 |
create user 'ua'@'%' identified by 'pa'; ERROR 1396 (HY000): Operation CREATE USER failed for 'ua'@'%' |
由于在t3时刻直接删除了数据表的记录，而内存的数据还存在，就导致了T4时刻给用于ua赋权失效，在mysql.user表中找不到这条记录，在t5时刻，重新创建这个用户，因为在内存中判断，会认为这个用户还存在。