Bug #91221 REVOKE ALL PRIVILEGES, GRANT OPTION does not work in stored procedure
Submitted: 12 Jun 2018 7:45 Modified: 12 Jun 2018 10:26
Reporter: Heiki Laaniste Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:innodb_version = 5.7.21, 5.7.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: PROCEDURE, revoke, revoke all, stored routine

[12 Jun 2018 7:45] Heiki Laaniste
Description:
I need a stored routine (a procedure) for revoking all the privileges from a user so I can generate new privileges from scratch.
The command to do this is:
revoke all privileges, grant option from `user`@`host`;
The documentation says it is possible to execute this command in a prepared statement:
https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
But mysql server tells me: "This command is not supported in the prepared statement protocol yet"

How to repeat:
mysql> delimiter ;;
mysql> CREATE PROCEDURE `testProc`(pUserName varchar(255), pHost varchar(255))
    -> begin
    ->     SET @s = concat('REVOKE ALL PRIVILEGES, GRANT OPTION FROM `', pUserName, '`@`', pHost,'`');
    ->     PREPARE stmt FROM @s;
    ->     EXECUTE stmt;
    ->     DEALLOCATE PREPARE stmt;
    -> end;
    -> ;;
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> call testProc('user1','%');
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
mysql> select @s;
+------------------------------------------------------+
| @s                                                   |
+------------------------------------------------------+
| REVOKE ALL PRIVILEGES, GRANT OPTION FROM `user1`@`%` |
+------------------------------------------------------+
1 row in set (0.05 sec)

mysql>
[12 Jun 2018 10:26] Umesh Shastry
Hello Heiki,

Thank you for the report.

Thanks,
Umesh