MySQL5.7OCP 第十六题 关于存储过程的删除和执行权限

16、USE sales;DROP PROCEDURE IF EXISTS get_reports
C The stored procedure named get_reports will be dropped

The following grants were executed:
GRANT CREATE ROUTING ON sales.* TO ‘webadmin’@’%’;
GRANT ALTER ON PROCEDURE sales.myproc TO ‘webadmin’@’%’;
A user successfully connects to the database as webadmin and created a stored procedure named get_reports.
The next day,the user logs in again as webadmin and wants to delete the stored procedure named get_reports,and therefore,issues the following statement:
USE sales;
DROP PROCEDURE IF EXISTS get_reports;
What is the result of executing the statement?
A、 The user will get an error because he or she did not use the ALTER statement to drop the stored procedure;
B、 The user will get an error because he or she did not put the database name in front of the stored procedure name;
C、 The stored procedure named get_reports will be dropped;
D、 The user will get an error because he or she does not have the permission to drop stored procedures;
Answer:C;
执行了以下授权:
GRANT CREATE ROUTING ON sales.* TO ‘webadmin’@’%’;
GRANT ALTER ON PROCEDURE sales.myproc TO ‘webadmin’@’%’;
用户以webadmin身份成功连接到数据库，并创建了名为get_reports的存储过程。第二天，用户再次以webadmin身份登录，并希望删除名为get_reports的存储过程，因此发出以下语句:
USE sales;
DROP PROCEDURE IF EXISTS get_reports;
执行语句的结果是什么?
C、 名为get_reports的存储过程将被删除;
解析：如果用户有 CREATE ROUTine(原题有误)权限并且建立了存储过程,则对该存储过程自动拥有删除和执行的权限,
当授予 create routine 时， 自动授予 EXECUTE, ALTER ROUTINE 权限给它的创建者。 可以使用{alter |drop} {procedure|function}