MySQL案例-show fileds from 提示无权限

-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------

背景:
MySQL-5.7.12, 开发用的环境出现show fields的报错;

场景:
业务方反馈在执行show fields的命令时报错了,
在测试环境构造了报错的场景, 实际报错内容类似于:

点击(此处)折叠或打开

ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'role_id' in table 'xxxxx'

直观上看, 很容易就能发现是权限的问题,
不过有一个疑点:
报错内容的用户那一栏显示为空;

分析:
首先能肯定的是这是由权限引起的, 错误信息中很明显;
第二点就是用户栏显示为空, 这个有可能是指用户不存在, 也有可能是有关联的用户信息存在一些问题,导致无法显示;

排查:
1.排查登录用户的权限, 登录到线上环境, 发现业务账号确实会报错, 然后换成root账号也是一样......

而业务账号是有view对应db, 以及view 的select中对应db的ALL权限的, 何况root也不行, 说明这个权限相关的报错, 并不是由登录用户引起的;

2.既然不是登录用户的权限问题, 那么就看看视图本身的权限;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
相关资料:
MySQL-5.7中, 视图的权限控制是由视图创建时自行指定的, 如测试环境中, 有问题的视图的创建语句:

点击(此处)折叠或打开

CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY DEFINER VIEW test_view_xxx_roles AS select xxx;

红色部分:
定义了这个view的创建者(定义者), 代表了这个view在创建时用到的权限是 `test`@`%`提供的;

绿色部分:
定义了这个view在访问的时候, 校验权限时, 检查哪个用户, DEFINER或者是INVOKER;

------------------------------------------------------------------------------------------------------------------------------------------------------------------

从相关资料可以了解到, show fields from view_stack会报无权限的错误, 就是因为根据 SQL SECURITY 去校验用户的权限时, 发现权限没了;
从view的内容来看, 只需要对应库的select权限就好了, 那么看看现在库中, 创建这个view的test用户的权限:

是的, 真的没有看错, 开发用的环境里面, 这个view对应的用户已经没了....._(:з」∠)_

那么创建这个用户以后, 看看是不是就不会报错了:

果然, 只要加上这个definer就不会再报错了;

处理方式:
最终的处理方式是修改了相关view的definer用户(需要 super权限才能修改view的这个字段);

重要的额外信息:
比较奇怪的是这个现象无法在"干净 "的库里面复现, 只有把开发环境的 数据库导入到测试环境才会出现这个问题, 可能有一些其他的因素影响到了mysql的处理逻辑;

相关文章：