Permission for MySQL users to execute stored procedures

In MySQL, the user executes the stored procedure as EXECUTE. For example, we created the following stored procedure in the database named configdb. The stored procedure is defined as user_adminuseconfigdb; dropprocedureifexistssp_dev_test_user_add; delimiter $ CREATEDEFINER 'user _ admin' @ '%' PROCEDURE's

In MySQL, the user's permission to EXECUTE the stored procedure is EXECUTE. For example, we created the following stored procedure in the database named configdb. The stored procedure is defined as user_admin use configdb; drop procedure if exists sp_dev_test_user_add; delimiter $ create definer = 'user _ admin' @ '%' PROCEDURE's

In MySQL, the user's permission to EXECUTE stored procedures is EXECUTE.

For example, you have created the following stored procedure in a database named configdb. The stored procedure is defined as user_admin.

Use configdb;

Drop procedure if exists sp_dev_test_user_add;

Delimiter $

Create definer = 'user _ admin' @ '%' PROCEDURE 'SP _ dev_test_user_add '(

In var_user varchar (30 ),

In var_ip varchar (15 ),

In var_username varchar (30 ),

In var_email varchar (30 ),

In var_orginfo varchar (30)

)

BEGIN

Create temporary table errors (error varchar (500 ));

If exists (select user from mysql. user where user = var_user) then

Insert into errors values (concat ('user name "', var_user,'" already exists! '));

End if;

If exists (select * from errors) then

Select error from errors;

Else

Set @ user = concat (var_user, '@ '', var_ip ,''');

Set @ s = concat ('create user', @ user, 'identified by ''12345 '';');

Prepare cmd from @ s;

Execute cmd;

Set @ s = concat ('Grant select on 'mysql'. 'func' TO ', @ user ,';');

Prepare cmd from @ s;

Execute cmd;

Set @ s = concat ('Grant select on 'mysql'. 'proc' TO ', @ user ,';');

Prepare cmd from @ s;

Execute cmd;

Replace into dev_test_userinfo values (var_user, var_username, var_email, var_orginfo );

End if;

Drop temporary table errors;

END

$

Delimiter;

Try to create a common user user_test1

Mysql> create user user_test1 identified by '20140901 ';

View its permissions

Mysql> show grants for user_test1;

+ Shards +

| Grants for user_test1 @ % |

+ Shards +

| Grant usage on *. * TO 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 '|

+ Shards +

Grant selectinsertdeleteupdate permission to configdb

Mysql> grant select, insert, delete, update on configdb. * to 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9'

Mysql> show grants for user_test1;

+ Shards +

| Grants for user_test1 @ % |

+ Shards +

| Grant usage on *. * TO 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 '|

| Grant select, INSERT, UPDATE, delete on 'configdb'. * TO 'user _ test1' @ '%' |

+ Shards +

Use this user to log on to MySQL and execute the stored procedure just defined

Mysql> use configdb;

Mysql> call sp_dev_test_user_add ('app _ yzz ', '192. 16. %', 'yz', 'yzz @ e-mail ', 'mysql DBA ');

ERROR 1370 (42000): execute command denied to user 'user _ test1' @ '%' for routine 'configdb. sp_dev_test_user_add'

It seems that the permission is insufficient. Continue to grant the execute permission on configdb.

Mysql> grant execute on configdb. * to 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 ';

Mysql> show grants for user_test1;

+ Shards +

| Grants for user_test1 @ % |

+ Shards +

| Grant usage on *. * TO 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 '|

| Grant select, INSERT, UPDATE, DELETE, execute on 'configdb'. * TO 'user _ test1' @ '%' |

+ Shards +

Use this user to log on to MySQL and execute the stored procedure just defined.

Mysql> use configdb;

Mysql> call sp_dev_test_user_add ('app _ yzz ', '192. 16. %', 'yz', 'yzz @ e-mail ', 'mysql DBA ');

ERROR 1449 (HY000): The user specified as a definer ('user _ admin' @ '%') does not exist

This time, the stored procedure can be called, but the system prompts that the definer In the stored procedure definition does not exist. It is far from enough that only the user connected to the MySQL server has the permission to execute the stored procedure, in the end, you must use the definer specified in the Stored Procedure Definition to execute the stored procedure.

Create user_admin '@' % 'and grant corresponding permissions to configdb.

Mysql> create user user_admin identified by '20160301 ';

Mysql> grant select, insert, delete, update on configdb. * to 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 ';

Mysql> show grants for user_admin;

+ Shards +

| Grants for user_admin @ % |

+ Shards +

| Grant usage on *. * TO 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 '|

| Grant select, INSERT, UPDATE, delete on 'configdb'. * TO 'user _ admin' @ '%' |

+ Shards +

Use the 'user _ test1' @ '%' user to log on to MySQL and execute the stored procedure just defined.

Mysql> use configdb;

Mysql> call sp_dev_test_user_add ('app _ yzz ', '192. 16. %', 'yz', 'yzz @ e-mail ', 'mysql DBA ');

ERROR 1370 (42000): execute command denied to user 'user _ admin' @ '%' for routine 'configdb. sp_dev_test_user_add'

It seems that not only the user connecting to the MySQL server needs to have the execution permission on the stored procedure, but also the Stored Procedure definer needs this permission.

Mysql> grant execute on configdb. * to 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 ';

Mysql> show grants for user_admin;

+ Shards +

| Grants for user_admin @ % |

+ Shards +

| Grant usage on *. * TO 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 '|

| Grant select, INSERT, UPDATE, DELETE, execute on 'configdb'. * TO 'user _ admin' @ '%' |

+ Shards +

Use the 'user _ test1' @ '%' user to log on to MySQL and execute the stored procedure just defined.

Mysql> use configdb;

Mysql> call sp_dev_test_user_add ('app _ yzz ', '192. 16. %', 'yz', 'yzz @ e-mail ', 'mysql DBA ');

ERROR 1044 (42000): Access denied for user 'user _ admin' @ '%' to database 'configdb'

The stored procedure can be executed, but the system prompts that the permission is insufficient. You can view the definition of the stored procedure carefully. The Stored Procedure contains statements for creating users and granting permissions, however, neither 'user _ test1' @ '%' user nor 'user _ admin' @ '%' have such permissions.

Grant 'user _ test1' @ '%' the permission to create and grant permissions to users and create temporary tables.

Mysql> grant create user on *. * to 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 'with grant option;

Mysql> grant create temporary tables on configdb. * to 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 ';

Mysql> show grants for 'user _ test1' @ '% ';

+ Domains ---- +

| Grants for user_test1 @ % |

+ Domains ---- +

| Grant create user on *. * TO 'user _ test1' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 'with grant option |

| Grant select, INSERT, UPDATE, DELETE, create temporary tables, execute on 'configdb'. * TO 'user _ test1' @ '%' |

+ Domains ---- +

Use the 'user _ test1' @ '%' user to log on to MySQL and execute the stored procedure just defined.

Mysql> use configdb;

Mysql> call sp_dev_test_user_add ('app _ yzz ', '192. 16. %', 'yz', 'yzz @ e-mail ', 'mysql DBA ');

ERROR 1044 (42000): Access denied for user 'user _ admin' @ '%' to database 'configdb'

By the way, no matter what account you use to log on to MySQL, The definer of the stored procedure is used to execute the stored procedure. Therefore, the User Creation and authorization permissions should be paid to definer, here is the user_admin '@' % 'account.

Grant 'user _ admin' @ '%' the permission to create and grant permissions to users.

Mysql> grant create user on *. * to 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 'with grant option;

Mysql> grant create temporary tables on configdb. * to 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 ';

Mysql> show grants for 'user _ admin' @ '% ';

+ Domains ---- +

| Grants for user_admin @ % |

+ Domains ---- +

| Grant create user on *. * TO 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 'with grant option |

| Grant select, INSERT, UPDATE, DELETE, create temporary tables, execute on 'configdb'. * TO 'user _ admin' @ '%' |

+ Domains ---- +

Use the 'user _ test1' @ '%' user to log on to MySQL and execute the stored procedure just defined.

Mysql> use configdb;

Mysql> call sp_dev_test_user_add ('app _ yzz ', '192. 16. %', 'yz', 'yzz @ e-mail ', 'mysql DBA ');

ERROR 1142 (42000): SELECT command denied to user 'user _ admin' @ '%' for table 'user'

Oh, in addition to the configdb database, you must have the permissions for the user table in the mysql database. It seems that the permission problem is really tricky ~

Mysql> grant select, insert, delete, update on mysql. * to 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 ';

Mysql> show grants for 'user _ admin' @ '% ';

+ Domains ---- +

| Grants for user_admin @ % |

+ Domains ---- +

| Grant create user on *. * TO 'user _ admin' @ '%' identified by password' * 00a51f3f481_c7d4e8908980d443c29c69b60c9 'with grant option |

| Grant select, INSERT, UPDATE, DELETE, CREATE, create temporary tables, execute on 'configdb'. * TO 'user _ admin' @ '%' |

| Grant select, INSERT, UPDATE, delete on 'mysql'. * TO 'user _ admin' @ '%' |

+ Domains ---- +

Use the 'user _ test1' @ '%' user to log on to MySQL and execute the stored procedure just defined.

Mysql> use configdb;

Mysql> call sp_dev_test_user_add ('app _ yzz ', '192. 16. %', 'yz', 'yzz @ e-mail ', 'mysql DBA ');

Query OK, 0 rows affected (0.05 sec)