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>
