MySQL存储过程创建用户 [英] MySQL Stored Procedure to create user
本文介绍了MySQL存储过程创建用户的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试创建一个存储过程以在mysql服务器5.6.21中创建用户,但是它引发以下错误:
I am trying to create a stored procedure to create user in mysql server 5.6.21 but it throws the following error:
错误代码:1445.不允许从存储的功能设置自动提交 或触发.
Error Code: 1445. Not allowed to set autocommit from a stored function or trigger.
以下代码:
CREATE PROCEDURE `add_User`(IN p_Name VARCHAR(45), IN p_Passw VARCHAR(200))
BEGIN
DECLARE flag_exist TINYINT(1) DEFAULT 0;
SELECT COUNT(*)
INTO flag_exist
FROM
`mysql`.`USER`
WHERE
`mysql`.`USER`.`User` = p_Name;
IF flag_exist = 0 THEN
CREATE USER p_Name;
GRANT ALL PRIVILEGES ON *.* TO p_Name;
SET PASSWORD FOR p_Name = PASSWORD (p_Passw);
FLUSH PRIVILEGES;
/* NO FOUNT
INSERT INTO user VALUES
('localhost',p_Name,PASSWORD(p_Passw),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
*/
END IF;
END
推荐答案
也许这会给你一些想法:
Maybe this will give you some ideas:
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_User`$$
CREATE PROCEDURE `add_User`(IN `p_Name` VARCHAR(45), IN `p_Passw` VARCHAR(200))
BEGIN
DECLARE `_HOST` CHAR(14) DEFAULT '@\'localhost\'';
SET `p_Name` := CONCAT('\'', REPLACE(TRIM(`p_Name`), CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\''),
`p_Passw` := CONCAT('\'', REPLACE(`p_Passw`, CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\'');
SET @`sql` := CONCAT('CREATE USER ', `p_Name`, `_HOST`, ' IDENTIFIED BY ', `p_Passw`);
PREPARE `stmt` FROM @`sql`;
EXECUTE `stmt`;
SET @`sql` := CONCAT('GRANT ALL PRIVILEGES ON *.* TO ', `p_Name`, `_HOST`);
PREPARE `stmt` FROM @`sql`;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
FLUSH PRIVILEGES;
END$$
DELIMITER ;
这篇关于MySQL存储过程创建用户的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文