The role and example of Definer in MySQL stored routine (storage routines)

To create the routine syntax see https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

The syntax for creating procedure is as follows

CREATE    [DEFINER = { user | CURRENT_USER }]    PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

The role of Definer is to control only the super privilege or the specified procedure creator to perform this procedure only super users can use Definer syntax

Create a simple instance I created under [email protected]

mysql> delimiter #mysql> CREATE DEFINER=`hee`@`localhost` PROCEDURE `simpleproc`(OUT param1 INT)    begin select count(*) INTO param1 from `categories`;  end #mysql> delimiter ;#调用的时候直接mysql> call simpleproc(@a);mysql> select @a;+------+| @a |+------+| 6 |+------+1 row in set (0.00 sec)

Now I switch to [email protected]

I should have done simpleproc, because the current user is [email protected] but still the failure code is as follows

#I first created the [email protected]User"In [email protected]BelowCreate "mysql> create user [email protected]ost identified by" ABC ";Given a subset of permissions to [email protected]grant insert,update,select on ' api_db '.  categories ' to [email protected];#  Why didn't I give all privileges directly Span class= "Zh-hans" to [email protected]  Not all cases can be given to all Privileges   I aim to illustrate  Execute procedure  permissions #  switch to [email protected]mysql> call Simpleproc (@a); ERROR 1370 (42000): Execute command denied to user  ' hee ' @ ' localhost ' for routine  ' api_db.simpleproc ' mysql> Select current_user;+---------------+| Current_User |+---------------+| [email protected] |+---------------+1 row in Set (0.00 sec)        

Why can't you execute Simpleproc?

Because additional permissions are required

Refer to grant Permissions List Https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_execute

Extract

The Execute privilege is required to execute stored routines (procedures and functions). To execute procedure must have EXECUTE permission this can be viewed in the Mysql.user table again

EXECUTE is loaded on a database so be authorized to use

on `api_db`.* to [email protected];Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

But still in another terminal ([email protected] Login terminal) or Execute call Simpleproc (@a) failure just log in to MySQL again.

mysql> call simpleproc1(@a) ;Query OK, 1 row affected (0.00 sec)mysql> select @a    -> ;+------+| @a   |+------+|    1 |+------+1 row in set (0.00 sec)

Roles and instances of Definer in MySQL stored routine (storage routines)