mysql> DROP PROCEDURE IF EXISTS CreateMyUsr;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE CreateMyUsr()
    -> BEGIN
    -> SELECT host,user FROM mysql.user;
    -> CREATE USER tets IDENTIFIED BY 'mypass'; -- создание пользователя test с паролем mypass
    -> SELECT host,user FROM mysql.user;
    -> DROP USER tets;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL CreateMyUsr;
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| localhost | mysql.sys |
| localhost | root      |
+-----------+-----------+
2 rows in set (0.01 sec)

+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | tets      |
| localhost | mysql.sys |
| localhost | root      |
+-----------+-----------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> DROP PROCEDURE CreateMyUsr;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user FROM mysql.user;
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| localhost | mysql.sys |
| localhost | root      |
+-----------+-----------+
2 rows in set (0.00 sec)