Permission for MySQL users to execute stored procedures _ MySQL

MySQL user's permission to EXECUTE stored procedures bitsCN. comMySQL EXECUTE the stored procedure permission as 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 select, insert, delete, and update permissions 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)

Finally, I believe that through this series of processes, you should be able to clearly understand the execution permissions related to the MySQL stored procedure. In addition, you also need to have the create routine permission to define the stored procedure. In addition, you must have the alter routine permission to define the stored Procedure. (this is the stored procedure created in configdb by a Super User, the preceding permissions are granted). The user who calls the stored procedure must have the EXECUTE permission, the user who finally executes the stored procedure, that is, the creator of the stored procedure definition, must have various permissions related to the stored procedure definition statement.

MySQL permissions are divided into table permissions, column permissions, and process permissions. for details, see the MySQL official manual.

BitsCN.com