You can use this work around by creating an database API.
The SQL code should help you.
CREATE TABLE mysql.`created_users` (
`user_name` varchar(255) DEFAULT NULL,
`owner` varchar(255) DEFAULT NULL
)
The table hold the usernames and what user created them.
Note create the Procedures with your root account
Procedure to create an mysql user.
DROP PROCEDURE IF EXISTS mysql.createUser;
DELIMITER //
CREATE PROCEDURE mysql.createUser(IN userName VARCHAR(255), IN userPassword VARCHAR(255))
BEGIN
SET @createUserQuery = CONCAT('
CREATE USER "',userName,'"@"localhost" IDENTIFIED BY "',userPassword,'" '
);
PREPARE stmt FROM @createUserQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @createcreatedUserQuery = CONCAT('
INSERT INTO mysql.created_users (user_name, owner) VALUE("',userName,'", "',USER(),'")'
);
PREPARE stmt FROM @createcreatedUserQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Procedure to drop and with check on created_users table to make sure the user exists and delete right check.
DROP PROCEDURE IF EXISTS mysql.dropUser;
DELIMITER //
CREATE PROCEDURE mysql.dropUser(IN userName VARCHAR(255))
BEGIN
SET @canDeleteUser = 0;
SET @createCountUserQuery = CONCAT('
SELECT COUNT(*) FROM mysql.created_users WHERE user_name = "',userName,'" AND owner = "',USER(),'" INTO @canDeleteUser'
);
PREPARE stmt FROM @createCountUserQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @canDeleteUser = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The user is not known on the server or you dont have rights to delete this user';
END IF;
IF @canDeleteUser = 1 THEN
SET @createDropUserQuery = CONCAT('
DROP USER "',userName,'"@"localhost"'
);
PREPARE stmt FROM @createDropUserQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @createDeleteUserQuery = CONCAT('
DELETE FROM created_users WHERE user_name = "',userName,'" AND owner = "',USER(),'"'
);
PREPARE stmt FROM @createDeleteUserQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END //
DELIMITER ;
And to give rights to execute these
GRANT EXECUTE ON PROCEDURE mysql.createUser TO '[user]'@'localhost';
GRANT EXECUTE ON PROCEDURE mysql.dropUser TO '[user]'@'localhost';
And you may want to give the user select priv on mysql.proc so they can see the source code behind the procedures and know the parameters
You can use the database API like this.
CALL mysql.createUser('user', 'password');
CALL mysql.dropUser('user');
Note that root account can only remove users with mysql.dropUser that have the owner root@localhost
