在MySQL中,grant语句为了给用户赋予权限,很多文档都提到过使用grant语句后,要跟着一个flush privileges命令,才能使语句生效
为什么,而且有必要吗,今天就来讲一下 grant语句和flush privileges语句的关系
首先,为了说明,创建了一个用户
create user ‘ua@%’ identified by ‘pa’
创建了一个用户 ua@%,密码是pa,在MySQL中,用用户名 user和地址host表示一个用户, ua@ip1和 ua@ip2表示的是两个不同的用户
这个命令的内部操作流程为:
1.往mysql.user表中插入一行 由于没有指定权限,这行中所有的表示权限的值都是N
2.内存中,在MySQL维护的一个acl_users数组中插入一个acl_user对象
当新创建一个用户的时候,在用户表中是
全局的权限和数据库库的权限划分
全局权限,作用于整个MySQL实例,保存在mysql的user表中,如果需要赋予全部权限的话,可以写为
grant all privileges on *.* to ‘ua@%’ with grant option
这个grant做了两个动作
磁盘上,将mysql_user表中,将用户中所有的权限改为 Y
内存中,找到acl_users中对应的对象,access改为了 全1;
当然,最初的时候曾经说过,一个用户一旦登陆成功了,就会权限拷贝到这个线程对象中,之后所有关于权限的判断,都直接使用线程对象内部保存的权限位
于是,可以得到总结,对于grant命令,会在执行完成后,更新磁盘和内存中的数据,并且对于一个已经存在的连接,全局权限并不会受到grant命令的影响
当然,建议对于非root用户,一般来说不能赋予一个用户全部的IP地址和全部的权限
对于权限的收回,使用revoke命令
revoke all privileges on *.* from ‘ua@%’;
revoke相当于相反的走了一次grant命令,
先将所有标示权限的字段的值修改为N
然后,内存中,从数组acl_users中找到这个用户对应的对象,access的值表示为0;
为了判断一个用户对于一个数据库的权限时候,需要判断遍历一次acl_dbs数组,根据user host db确定user对象,然后查看权限
grant修改的时候,对磁盘和内存是同时生效的
如下的三个session
执行如上的语句时候,可以按照上限的流程分析语句
1.用户在T3的时候被收回了super权限,但是仍然权限验证通过了,这是因为权限存储到了线程对象中
2.T5 去掉了对象对db1库的执行流程,在sessionB执行,导致了权限不足的错误,在sessionC中,执行成功,这是因为在T2时刻设置了use db1,
拿到了这个库的权限,保存在了线程中,在切换db1库之前,一直有权限
表级权限和列权限
除了db级别的区别,MySql支持表级别和列级别,列权限定义放在mysql.columns.priv中,表在mysql.tables.priv
两个之间的赋值语句分别为
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权限类似,每次修改都是修改完数据库后修改内存
那么从上面的全局 数据库 表 列来看,都是即时生效的,不需要执行flush privileges语句啊!
的确是这样的
flush privileges命令,会清空数据库,然后从mysql.user表中读取数据重新加载,构造一个acl_users数组
但是对于一些直接修改数据用户表的操作,flush privilegs命令是很有用的
比如如下的两个情景
T3时刻,用delete语句删除了用户,但是t4时刻还是能连接的,因为内存中acl_users有这个用户
T5时刻刷新后,就没有了
还有一种情况
因为T3时候删除了数据,导致内存和硬盘数据不一致
T4时刻赋予权限时,因为无法在用户表中找到用户导致失败
T5时刻创建用户也失败了,因为内存中还存在这个用户
于是flush privileges可以使用在一些不规范的DML语句之后,当然,如果使用了grant语句赋权后,可能出现这样的写法,grant super on *.* to ‘ua’@’%’ identified by ‘pa’
代表着,如果用户 ‘ua’@ ‘%’不存在,就创建这个用户,密码是pa
如果用户 ua 已经存在,就将密码修改为pa
在使用命令 行转列的时候可以加上几个常用的语句
\G 行转列并发送到server mysql
\g 等同于
\! 执行系统命令
\q exit
\c 清除当前SQL 不执行
\s mysql status信息
对于用户的删除,比起直接删除用户表上的数据,更擅长的语句应该是drop语句
drop user ‘ua@%’
这样就是同时从内存和硬盘中删除用户信息了,delete只是从磁盘删除
有一个小技巧就是
使用grant是支持通配符的,”_”表示是一个任意的字符,”%”表示是一个任意字符串,如果在一个分库分表方案汇总,有多个db,可以利用这个技巧去批量赋权