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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.