SQL SECURITY DEFINER是CREATE VIEW(以及CREATE PROCEDURE、CREATE FUNCTION等)语句的另一个选项,用于指定视图(或存储过程、函数等)的安全上下文。当设置为DEFINER时,视图将以其定义者的权限来执行,而不是调用者的权限。这意味着即使调用者没有足够的权限来直接访问视图所依赖的表或列,只要定义者有足够的
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test --函数定义 CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER --存储过程定义 CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER --触发器定义 CREAT...
我们可以通过如下的SQL生成批量修改视图DEFINER的SQL: SELECT CONCAT("ALTER DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," AS ",VIEW_DEFINITION,";") FROM INFORMATION_SCHEMA.VIEWS WHERE DEFINER != 'root@%'; 1 2 SELECTCONCAT("ALTER DEFINER=`root`@`%` SQL SEC...
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFINER=`ddd_stowka`@`%` SQL SECURITY DEFINER VIEW `ddd_stowka`.`bpqz_view_winne' at line 1 during executing statement: CRE...