MySQL OCP888题解041-删除存储过程的权限

1、原题

1.1、英文原题

The following grants were executed:
GRANT CREATE ROUTINE ON sales.* TO ‘webadmin’@‘%’;
GRANT ALTER ROUTINE ON sales.* 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.

1.2、中文翻译

执行了以下语句：
GRANT CREATE ROUTINE ON sales.* TO ‘webadmin’@‘%’;
GRANT ALTER ROUTINE ON sales.* TO ‘webadmin’@‘%’;
用户以webadmin身份成功连接到数据库，并创建了名为get_reports的存储过程。第二天，用户再次以webadmin身份登录，并希望删除名为get_reports的存储过程，因此发出以下语句：
USE sales;
DROP PROCEDURE IF EXISTS get_reports;
执行语句的结果是什么？
A、 用户将收到一个错误，因为他或她没有使用ALTER语句删除存储过程。
B、 用户将收到一个错误，因为他或她没有将数据库名称放在存储过程名称前面。
C、 将删除名为get_reports的存储过程。
D、 用户将收到一个错误，因为他或她没有删除存储过程的权限。

1.3、答案

C

2、题目解析

2.1、题干解析

本题考察的是存储过程的权限管理，特别是删除权限。

2.2、选项解析

1. ROUTINE在MySQL中指的是存储过程和函数。ALTER ROUTINE有创建、修改和删除ROUTINE的权限，所以选项C正确。
2. 注意ALTER ROUTINE很特殊，一般的ALTER是没有删除表的权限的。

3、知识点

3.1、知识点1：GRANT和REVOKE的允许权限

ROUTINE（例程）在MySQL中指的是存储过程和函数。

官方参考文档

4、实验

4.1、实验1

4.1.1、实验目的

验证ALTER ROUTINE有删除存储过程的权限，而ALTER没有删除表的权限

4.1.2、实验前准备

已安装并运行MySQL5.7

4.1.3、实验步骤

1. 使用root用户登录，创建用户webadmin，创建数据库sales，赋予其在sales库ALTER和CREATE PROCEDURE的权限，赋予其在sales库ALTER和CREATE的权限。创建测试表t1。

mysql> CREATE USE webadmin@'%' IDENTIFIED BY '000000';
mysql> CREATE DATABASE sales;
mysql> GRANT CREATE ROUTINE ON sales.* TO 'webadmin'@'%';
mysql> GRANT ALTER ROUTINE ON sales.* TO 'webadmin'@'%';
mysql> GRANT CREATE ON sales.* TO 'webadmin'@'%';
mysql> GRANT ALTER ON sales.* TO 'webadmin'@'%';
mysql> CREATE TABLE t1(id INT);

2. 创建存储过程get_reports：

DELIMITER $$
CREATE PROCEDURE get_reports(count INT UNSIGNED)
BEGIN
SET @c=0;
WHILE @c < count DO
SET @c = @c +1;
END WHILE;
END;
$$
DELIMITER ;

3. 使用webadmin登录

$ mysql -uwebadmin -p
Enter password: 

4. 尝试删除存储过程get_reports，可以成功：

mysql> DROP PROCEDURE IF EXISTS sales.get_reports;
Query OK, 0 rows affected (0.01 sec)

5. 尝试删除表t1，失败。说明ALTER ROUTINE有删除存储过程的权限，而ALTER没有删除表的权限。

mysql> drop table sales.t1;
ERROR 1142 (42000): DROP command denied to user 'webadmin'@'localhost' for table 't1'

6. 使用root用户赋予webadmin对sales库的drop权限：

$ mysql -uroot -p
mysql> GRANT DROP ON sales.* TO 'webadmin'@'%';

7. 在之前已经打开的webadmin会话删除表t1无法成功。

mysql> drop table sales.t1;
ERROR 1142 (42000): DROP command denied to user 'webadmin'@'localhost' for table 't1'

8. 打开一个新的会话，用webadmin登录，发觉可以将t1表删除。说明修改权限后，已登录的用户权限不会变更，需要重新登录。

$ mysql -uwebadmin -p
mysql> drop table sales.t1;
Query OK, 0 rows affected (0.01 sec)

4.1.4、实验结论

ALTER ROUTINE有删除存储过程的权限，而ALTER没有删除表的权限。

5、总结

1. ROUTINE在MySQL中指的是存储过程和函数。ALTER ROUTINE有创建、修改和删除ROUTINE的权限。
2. 注意ALTER ROUTINE很特殊，一般的ALTER是没有删除表的权限的。