How to modify all definer in mysql

This article is to share with you about how to modify all the definer in mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What is the definer in mysql and what is its function?

First authorize one: grant all on testdb.* to 'user1'@'%' identified by' 000000' with grant option; and then we create a stored procedure as follows: 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

Log in with your root account:

Mysql > use testdb;Database changedmysql > call user_count (); +-+ | count (*) | +-+ | 3 | +-+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) can be queried normally. We log in again with user1: mysql > use testdb;Database changedmysql > call user_count (); ERROR 1142 (42000): SELECT command denied to user' user1'@'localhost' for table 'user'

It is found that the system error query is not available, because the SQL security value defined above is INVOKER, and the stored procedure execution will be executed with the permission of user1, in which the mysql library is called, and our user1 account only has the right to use the testdb library, so a failure will be returned.

If it is convenient to modify all the defined definer in mysql?

The definer involved in mysql now includes view, trigger, function, procedure, and event. Let's make introductions one by one.

1. Modify the definer of function and procedure

Update mysql.proc set definer='user@localhost';-if limited libraries or other conditions can be added with where conditions

4. Modify the definer of trigger

Flush tables with readlock