MySQL45讲读书笔记 42讲grant之后要跟着flushprivileges吗_grant dba to here_sf; flush-程序员宅基地

技术标签: MYSQL  授权  mysql  

一 序

  本文属于极客时间MySQL45讲读书笔记系列。授权操作是开发同学了解,应该还是dba日常操作。

 在MySQL里面,grant语句是用来给用户赋权的。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语句操作系统权限表导致的,所以我们尽量不要使用这类语句。

******************

开发同学接触的很少,了解下就好。大的公司也会有脚本平台的自动处理。

 

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/bohu83/article/details/105938800

智能推荐

微信小程序 多选框的使用_微信小程序多选框两列布局-程序员宅基地

文章浏览阅读8.1k次,点赞5次,收藏37次。微信小程序 多选框的使用需求需求![在这里插入图片描述](https://img-blog.csdnimg.cn/20181229092342945.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RzZngwNTE0MzVhZHNs,..._微信小程序多选框两列布局

初投稿:关于Volley请求的乱码处理-程序员宅基地

文章浏览阅读210次。在Volley请求中,我们会遇到通过Volley请求时返回的数据是乱码的,这个问题对于我来说非常地头疼,于是我到网上寻找合适的解决方案。终于发现一个比较简单的解决方法,只需一个方法就可以解决请求乱码了。现在,我就给大家分享一下关于Volley请求返回数据乱码处理,大家可以参考一下。protected Response<String> parseNetworkResponse(Netw...

TensorFlow1.2~2.1各个GPU版本CUDA和cuDNN对应版本整理_cuda对应的tensorflow-gpu2.0版本-程序员宅基地

文章浏览阅读1.2k次。要搭建TensorFlow的GPU版本,首先需要的必备条件就是一块能够支持CUDA的NVIDIA显卡,因为在搭建TensorFlow的GPU版本时,首先需要做的一件事就是安装其基础支持平台CUDA和其机器学习库cuDNN,然后在此基础上搭建TensorFlow GPU版本。其次还要了解一下不同的TensorFlow版本所需要对应安装的CUDA和cuDNN版本是多少,因为在TensorFlow的G..._cuda对应的tensorflow-gpu2.0版本

留言板v2.0(添加了一个简单登录功能php+mysql)_完成留言板首页indexc.php及登录页面login.html。-程序员宅基地

文章浏览阅读5.2k次,点赞7次,收藏20次。简述:在之前基础上添加了一个非常简单的登录功能,不涉及数据库,本地判断。第一步:建立数据库。(之前写过,在写一遍。)第二步:登录界面代码login.php 留言板登录 .center{text-align: center;} .login-page { width: 360px; padding: 8% 0 0; margin: auto; } _完成留言板首页indexc.php及登录页面login.html。

android:一个listview多个item布局时,需注意重写getViewTypeCount()方法_android 适配器里getitemcount()多布局怎么判断-程序员宅基地

文章浏览阅读634次。当一个listview拥有多个item布局时,需要重写public int getItemViewType(int position)public int getViewTypeCount()两个方法,其中,第一个方法的返回值应从0开始;第二个方法的返回值应大于等于布局数量。_android 适配器里getitemcount()多布局怎么判断

deepin 下没有日志文件syslog messages 解决方法_没有insrall.log.syslog-程序员宅基地

文章浏览阅读1.5k次。一条命令即可:sudo apt-get install rsyslog_没有insrall.log.syslog

随便推点

X11 Wayland 及 Mir 比较_mir x11-程序员宅基地

文章浏览阅读1.1w次,点赞3次,收藏8次。MirCanonical 2013年3月宣布开发自己的显示服务器 Mir之后,引发了开源界的大量谴责,很多人指责Canonical为什么不采用被设计用来取代X11的Wayland,Wayland的开发者也表示Wayland完全能够满足Canonical的需求,指责Canonical搞分裂的行为。而 Canonical 则表示现在的 X 以及未来的 Wayland 无法满足未来横跨桌面、手机_mir x11

Linux 连接sftp 影响下载速度的因素_sftp速度受什么限制-程序员宅基地

文章浏览阅读5k次。  最近公司的一个项目需要使用sftp来下载文件到Linux服务器,然后再通过AES解密,RSA验签,解压等操作读取数据然后插入到数据库中。  公司的sftp部署到了公网上,刚开始传输小文件的时候。sftp都是正常,没有发现什么异常情况。但是当文件达到600M时候,sftp就卡着不动了。我自己电脑上跑项目下载速度还是可以的,大约10min就可以了。但是Linux中卡死了,下载不完。  首先分析..._sftp速度受什么限制

UVM中的driver组件-程序员宅基地

文章浏览阅读932次。一般UVM环境中的Driver组件,派生自uvm_driver。uvm_dirver派生自uvm_component。 class uvm_driver #(type REQ = uvm_sequence_item, type RSP = REQ) extends uvm_component其中定义了两个Ports:seq_item_port,dri..._uvm中什么组件直接驱动设计的输入

vue进阶-程序员宅基地

文章浏览阅读1.2k次,点赞3次,收藏16次。1,模板语法1,单次绑定v-once,仅渲染一次,随后的重新渲染会被跳过<span v-once>{{sex}}</span>2,v-html和v-text相当于element.innerHTML 和innerText<div v-html="gethtml"></div>data(){return{ gethtml: '<span>来吧</span>'}}3,v-bind的两种写法&l._vue进阶

JAVA之输入行数打印倒直角三角形_java打印倒直角三角形-程序员宅基地

文章浏览阅读1.2w次,点赞2次,收藏13次。JAVA之输入行数打印倒直角三角形_java打印倒直角三角形

《数据结构小实验》一元多项式计算器_已知一元多项式a和b,键盘输入系数和指数-程序员宅基地

文章浏览阅读2.7k次,点赞7次,收藏78次。从键盘输入运算指令(相加、相减、相乘),根据运算指令进行相应运算 ,每种运算结果以多项式形式输出,要输出升幂和降幂两种情况。结果多项式中无重复阶项、无零系数项,输出多项式时请采用易读形式。实现一个简单的交互式界面,包括系统菜单、输入提示等。多项式运算前首先判定多项式特点,根据多项式是否稀疏来选用合适的存储结构;根据多项式不同的运算要求选择合适的存储结构;上机编辑、调试出完整正确的程序,包括相加、相减、相乘运算。_已知一元多项式a和b,键盘输入系数和指数