MySQL视图定义者和安全性definer/invoker 的一次问题

最近服务器迁移,同时关闭了mysql数据库远程访问,就是root只能本地访问,但是同事在访问一个视图的时候一直报错
The user specified as a definer('root'@'%') does not exist

MySQL视图定义者和安全性definer/invoker 的一次问题
文章图片


原本只想打开访问权限,后面是想办法把%改为localhost,一种方法是直右键视图名,点击设计选择高级把%改为localhost就可以
MySQL视图定义者和安全性definer/invoker 的一次问题
文章图片

或者是直接用SQL语句修改:
ALTERALGORITHM=UNDEFINEDDEFINER=`root`@`localhost`SQL SECURITY DEFINERVIEW `view_all_aa` AS ………………………………………………………………;

参考文档:http://pdf.us/2018/02/24/679.html

昨天在做MySQL数据库用户权限规范的时候,将原来业务帐号'user'@'%'修改为'user'@'192.168.1.%'时,忽然发现业务系统部分业务报404错误,后面找到程序员排查原因,发现是因为报错的业务在调用数据库时,使用了视图,而视图的定义者是原来的'user'@'%',并且安全性设置的是definer,现在这个用户不存在了,虽然新用户'user'@'192.168.1.%’拥有和原来相同的数据库权限,但是依然不能使用这些视图。
找到了原因,那么解决办法也就出来了:一是将所有视图的定义者修改为'user'@'192.168.1.%';二是将安全性定义由definer设置为invoker。因为是生产环境,不能随便搞,所以采用的是第一种方法,第二种方法没有再试。
MySQL视图定义者和安全性definer/invoker 的一次问题
文章图片

原理
definer和invoker的区别
在创建视图或者是存储过程的时候,是需要定义安全验证方式的(也就是安全性SQL SECURITY),其值可以为definer或invoker,表示在执行过程中,使用谁的权限来执行。
definer:由definer(定义者)指定的用户的权限来执行
invoker:由调用这个视图(存储过程)的用户的权限来执行
definer
当定义为DEFINER时,必须数据库中存在DEFINER指定的用户,并且该用户拥有对应的操作权限,才能成功执行。与当前用户是否有权限无关。
示例:
CREATE DEFINER=dev@% PROCEDURE p_user_login(IN u_name VARCHAR(25), IN u_password VARCHAR(100))
BEGIN
SELECT u.id, u.name, u.tid, u.status, u.is_report FROM v_user u WHERE u.name=u_name AND u.password=u_password AND u.status=1;
END;
invoker
当定义为INVOKER时,只要执行者有执行权限,就可以成功执行。
示例:
CREATE DEFINER=dev@% PROCEDURE p_user_login(IN u_name VARCHAR(25), IN u_password VARCHAR(100))
SQL SECURITY INVOKER
BEGIN
SELECT u.id, u.name, u.tid, u.status, u.is_report FROM v_user u WHERE u.name=u_name AND u.password=u_password AND u.status=1;
END;
【MySQL视图定义者和安全性definer/invoker 的一次问题】转载请注明:轻风博客 ? MySQL视图定义者和安全性definer/invoker的一次踩坑经历

    推荐阅读