MySQL深入38-grant和flush privileges
# MySQL深入38-grant和flush privileges
grant语句是用来给用户赋权的，由于用户的权限是在连接时候拿到的，如果在连接后，即使该用户的权限该改变了，但是对于这个连接权限还是没有变的。本文主要对grant和flush privileges进行深入分析。
在案例分析之前，先创建一个用户：
create user 'ua'@'%' identified by 'pa';
这条语句的逻辑是创建一个用户’ua’@’%’，密码是 pa。这条命令做了两件事情：
- 磁盘上，往 mysql.user 表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是 N；
- 内存里，往数组 acl_users 里插入一个 acl_user 对象，这个对象的 access 字段值为 0。
在 MySQL 里面，用户名 (user)+ 地址 (host) 才表示一个用户，因此 ua@ip1 和 ua@ip2 代表的是两个不同的用户。
select * from mysql.`user` where user = 'ua'
# 全局权限
全局权限，作用于整个MySQL实例，这些权限信息保存在mysql库的user表里。现在我们给用户ua赋予一个最高的权限：
grant all privileges on *.* to 'ua'@'%' with grant option;
这个grant命令做了两个操作：
- 磁盘上，将 mysql.user 表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’；
- 内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。
该命令执行完成之后，如果有新的客户端使用该用户名登录，MySQL新连接会维护一个线程对象，然后查找该用户对应的权限，并将权限值拷贝到这个线程对象中。
对于该案例的分析我们可以知道：
- grant 命令对于全局权限，同时更新了磁盘和内存。命令完成后即时生效，接下来新创建的连接会使用新的权限。
- 对于一个已经存在的连接，它的全局权限不受 grant 命令的影响。
一般在生产环境上要合理控制用户权限的范围，如果一个用户有所有的权限，一般就不应该设置未所有IP可以访问。
如果要回收上面赋予的权限，可以使用下面命令：
revoke all privileges on *.* from 'ua'@'%';
参考grant命令，我们可以知道revoke做了两个操作：
- 磁盘上，将 mysql.user 表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为“N”；
- 内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 的值修改为 0。
# db 权限
如果要让用户ua拥有库db1的所有权限：
grant all privileges on db1.* to 'ua'@'%' with grant option;
基于库的权限记录保存在mysql.db表中，在内存里则保存在数组acl_dbs 中。这条 grant 命令做了如下两个动作：
- 磁盘上，往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；
- 内存里，增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”
select * from mysql.db where user = 'ua'
每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断。
到这里，我们可以知道grant修改db权限的时候，也是同时对磁盘和内存生效的。
但是db权限和全局权限对于已经存在的链接的影响是不同的：
- db权限修改之后对已经存在的链接会立即生效
- 全局是新的链接才会生效。
原因就是：全局的权限，线程会保存之前的权限直到释放，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 结构。因此，对这两类权限的操作，也会马上影响到已经存在的连接。
# flush privileges
定义
flush privileges 命令会清空 acl_users 数组，然后从 mysql.user 表中读取数据重新加载，重新构造一个 acl_users 数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。
如果内存的权限数据和磁盘数据表相同的话，不需要执行 flush privileges。因此，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。
使用场景
当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的。