How mysql modifies definer

This article mainly introduces the relevant knowledge of "how mysql modifies definer". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to modify definer in mysql" can help you solve the problem.

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

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

How does mysql modify definer

1. Modify the definer of function and procedure

Select definer from mysql.proc;-- functions, stored procedures update mysql.proc set definer='user@localhost';-- if limited libraries or other conditions can be added with where conditions

two。 Modify the definer of event

Select DEFINER from mysql.EVENT;-timed event update mysql.EVENT set definer=' user@localhost'

3. Modify the definer of view

The trouble with the modification of function is as follows:

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 definer of trigger

At present, there is no specific and convenient method, which can be modified one by one with the help of tools such as HeidiSQL, sqlyog and so on. Note that it is necessary to lock the table before the change, because if other table changes are triggered during the change process, it will cause data inconsistency.

This is the end of the introduction to "how mysql modifies definer". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.