更新时间:2023-12-02 11:05:10
您可以通过创建数据库 API 来解决此问题.SQL 代码应该对您有所帮助.
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
创建mysql用户的过程.
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 ;
删除并检查 created_users 表以确保用户存在并删除正确检查的过程.
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 ;
并授予执行这些权利
GRANT EXECUTE ON PROCEDURE mysql.createUser TO '[user]'@'localhost';
GRANT EXECUTE ON PROCEDURE mysql.dropUser TO '[user]'@'localhost';
并且您可能希望在 mysql.proc 上为用户提供 select priv,以便他们可以查看程序背后的源代码并知道参数
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
您可以像这样使用数据库 API.
You can use the database API like this.
CALL mysql.createUser('user', 'password');
CALL mysql.dropUser('user');
请注意,root 帐户只能删除具有所有者 root@localhost 的 mysql.dropUser 用户