Reputation: 11
I dont know how to fix it:
drop trigger if exists insertusuario;
CREATE TRIGGER insertusuario
BEFORE INSERT ON usuario
FOR EACH ROW
begin
GRANT ALL ON *.* TO ',new.nombre,'@'%' IDENTIFIED BY ',new.contraseña,' WITH GRANT OPTION;
end;
Upvotes: 0
Views: 1361
Reputation: 24002
Change your Grant
command statement preparation like below:
SELECT
CONCAT( 'GRANT ALL ON *.* TO ''',
new.nombre,
'''@''%'' IDENTIFIED BY ''',
new.contraseña,
''' WITH GRANT OPTION'
) INTO @temp_grant_sql_string;
Use prepared statement to execute this generated grant statement;
PREPARE stmt FROM @temp_grant_sql_string;
EXECUTE stmt;
deallocate prepare stmt; -- or drop prepare stmt;
Let me hope you know about delimiter
part before registering a stored procedure.
Use: delimiter $$
before trigger definition. And,
use: delimiter ;
after trigger definition, to reset to default.
Finally, your trigger should be reading like this:
delimiter $$;
drop trigger if exists insertusuario
$$
CREATE TRIGGER insertusuario BEFORE INSERT ON usuario
FOR EACH ROW BEGIN
SELECT
CONCAT( 'GRANT ALL ON *.* TO ''',
new.nombre,
'''@''%'' IDENTIFIED BY ''',
new.contraseña,
''' WITH GRANT OPTION'
) INTO @temp_grant_sql_string;
PREPARE stmt FROM @temp_grant_sql_string;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; -- or DROP PREPARE stmt;
END;
$$
delimiter ;
Upvotes: 1