MySQL中的DEFINER与SQL SECURITY

今天测试项目时，连接远程Mysql数据库项目正常运行，把远程数据库拷贝到本地数据库，项目无法登陆，一直考虑是同步数据库出了问题。确认几遍后发现数据没问题。最后发现是某条sql出现问题指定定义者的用户不存在。而该sql中调用了视图，最后才发现是视图权限问题，运行sql报The user specified as a definer ('xxx'@'%') does not exist。指定定义者的用户不存在。而该sql中调用了视图，最后才发现是视图权限问题。运行show create view xxx，显示CREATE ALGORITHM=UNDEFINED DEFINER=`xx`@`%` SQL SECURITY DEFINER VIEW `user_role` AS select `ue`.`user_id` AS `user_id`,`pr`.`role_id` AS `role_id` from ((`user_employee` `ue` left join `post_employee` `pe` on((`ue`.`employee_id` = `pe`.`employee_id`))) left join `post_role` `pr` on((`pe`.`post_id` = `pr`.`post_id`))) where (`pr`.`role_id` > 0)。发现创建视图时指定了DEFINER并且加入了SQL SECURITY。只能按照定义者的权限来。

我们在mysql创建view、trigger、function、procedure、等时都需要定义以下几个属性

DROP VIEW
IF EXISTS `xxxx`;

CREATE ALGORITHM = UNDEFINED 
DEFINER = `xx`@`localhost` 
SQL SECURITY DEFINER 
VIEW `xxxx` AS 

ALGORITHM=UNDEFINED：指定视图的处理算法；①

DEFINER=`root`@`localhost`：指定视图创建者；

SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；②

任意用户X访问此VIEW时，能否成功取决于X是否有调用该VIEW的权限，以及definer是否有view中的SELECT的权限。

只需要修改创建同名用户或者修改definer即可。

alter DEFINER = 'xx'@'localhost' view xxxx as ……

①：

ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。

如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。

对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

对于UNDEFINED，MySQL自己选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，

这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

②：

DEFINER 表示按定义者拥有的权限来执行

INVOKER 表示用调用者的权限来执行。默认情况下，系统指定为DEFINER