mysql如何修改所有的definer
mysql中的definer是什么，有什么作⽤？
我们在mysql创建view、trigger、function、procedure、event时都会定义⼀个Definer=‘xxx’，类似如下：
CREATE
ALGORITHM = UNDEFINED
DEFINER =    SQL SECURITY DEFINER
VIEW `v_ questions` AS
SELECT
`q`.`id` AS `id`,
`q`.`title` AS `title`
FROM
Test q;
或者像这样的：
CREATE DEFINER=`root`@`%` PROCEDURE `user_count`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select count(*) from mysql.user;
END
加红的部分SQL SECURITY 其实后⾯有两个选项，⼀个为DEFINER，⼀个为INVOKER
SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执⾏。DEFINER 表⽰按定义者拥有的权限来执⾏
INVOKER 表⽰⽤调⽤者的权限来执⾏。默认情况下，系统指定为DEFINER
以存储过程为例：
（1）MySQL存储过程是通过指定SQL SECURITY⼦句指定执⾏存储过程的实际⽤户；
（2）如果SQL SECURITY⼦句指定为DEFINER，存储过程将使⽤存储过程的DEFINER执⾏存储过程，验证调⽤存储过程的⽤户是否具有存储过程的execute权限和DEFINER⽤户是否具有存储过程引⽤的相关对象的权限；
（3）如果SQL SECURITY⼦句指定为INVOKER，那么MySQL将使⽤当前调⽤存储过程的⽤户执⾏此过程，并验证⽤户是否具有存储过程的execute权限和存储过程引⽤的相关对象的权限；
（4）如果不显⽰的指定SQL SECURITY⼦句，MySQL默认将以DEFINER执⾏存储过程。
我们来看下⾯⼏个⼩例⼦。
先授权⼀个：
grant all on testdb.*to'user1'@'%' identified by'000000'with grant option;
然后我们创建⼀个存储过程如下：
USE `testdb`;
DROP procedure IF EXISTS `user_count`;
DELIMITER $$
USE `testdb`$$
CREATE DEFINER=`root`@`%` PROCEDURE `user_count`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
select count(*) from mysql.user;
END$$
DELIMITER ;
⽤root帐号登陆：
mysql>use testdb;
Database changed
mysql> call user_count();
+----------+
|count(*) |
+----------+
|3|
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
可以正常查询出来。
我们再⽤user1进⾏登陆：
mysql>use testdb;
Database changed
mysql> call user_count();
mysql存储过程使用ERROR 1142 (42000): SELECT command denied to user'user1'@'localhost'for table'user'
发现系统报错查询不到了，这是因为我们在上述定义的SQL SECURITY值为INVOKER，存储过程执⾏过程中会以user1具有的权限来执⾏，其中调⽤到了mysql的库，⽽我们的user1帐户只有testdb库的使⽤权限，所以会返回失败。
我们把上⾯的invoker改为definer再来试⼀下：
update mysql.proc set security_type='DEFINER'where db='testdb'and name='user_count';
再次⽤user1进⾏登陆：
mysql>use testdb;
Database changed
mysql> call user_count();
+----------+
|count(*) |
+----------+
|3|
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
发现可以查询出来了，因为user1对存储过程user_count有执⾏的权限，虽然它依旧没有权限直接操作mysql库，由于我们定义的SQL SECURITY为DEFINER，所以在执⾏时是以root的⾝份执⾏的，所以可以正常查询出来。
如果⽅便修改mysql中所有已经定义到的definer？
由于前期在测试库上开发的缘故，我们经常定义到的definer为`root`@`%`，后来搬移到⽣产库上⼜得改回来，存在着⼤量的更新，上百个的视图，函数等⼀个个改不免太⿇烦并且也可能遗漏。如下为总结出的⽅便修改所有definer的⽅法，可以直到查漏补缺的作⽤。
现在在mysql涉及的definer有view、trigger、function、procedure、event。我们⼀个个作介绍。
1.修改function、procedure的definer
select definer from mysql.proc;  -- 函数、存储过程
update mysql.proc set definer='user@localhost'; -- 如果有限定库或其它可以加上where条件
2.修改event的definer
select DEFINER from mysql.EVENT; -- 定时事件
update mysql.EVENT set definer=' user@localhost ';
3.修改view的definer
相⽐function的修改⿇烦点：
select DEFINER from information_schema.VIEWS;
select concat("alter DEFINER=`user`@`localhost` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as
",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER<>'user@localhost';
查询出来的语句再执⾏⼀遍就好了。
4.修改trigger的definer
⽬前还没有具体⽅便的⽅法，可以借助⼯具端如HeidiSQL、sqlyog等来⼀个个修改。注意改前有必要锁表，因为如果改的过程中有其它表改变⽽触发，会造成数据不⼀致。
Flush tables with readlock
Unlock tables
如果有到⽅法的⽅法，记得留⾔，相互学习。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。