How to modify definer in mysql

Modification method: 1. Use "update mysql.proc set definer=..." to modify the definer of the function; 2. Use "update mysql.EVENT set definer=..." to modify the definer of the event.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to modify the definer of mysql

1. Modify the definer of function and procedure

select definer from mysql.proc;  -- 函数、存储过程
update mysql.proc set definer=‘user@localhost‘; -- 如果有限定库或其它可以加上where条件

2. Modify the definer of event

select DEFINER from mysql.EVENT; -- 定时事件
update mysql.EVENT set definer=‘ user@localhost ‘;

3. Modify the definer of the view

Compared with modifying the function, it is more troublesome:

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‘;

Just execute the query statement again.

4. Modify the trigger's definer

Currently there is no specific and convenient method. You can use tools such as HeidiSQL, sqlyog, etc. to modify them one by one. Note that it is necessary to lock the table before making changes, because if it is triggered by changes in other tables during the change process, it will cause data inconsistency.

Recommended learning: mysql video tutorial

The above is the detailed content of How to modify definer in mysql. For more information, please follow other related articles on the PHP Chinese website!