How to grant privileges to user inside a stored procedure in MySQL?

210 Views Asked by At

I want to create several stored procedures in my MySQL database and use them to grant privileges to users ( kind of implementation of roles). For example, I want to have admin1(), admin2(), user_type1(), user_type2() procedures, pass there user names and grant privileges.

This is the example of a function:

DELIMITER //
CREATE PROCEDURE grant_rights
(IN user varchar(100))
BEGIN
   grant update on Table2 to user;
END //
DELIMITER ;

Then I call it like this :

mysql> call grant_rights('newuser1');

Before that I tried to create newuser1 with multiple ways ( with or without quotes):

create user 'newuser1' identified by 'root';
create user newuser1 identified by 'root';

or

create user 'newuser1'@'localhost' identified by 'root';

Anyway, when I grant rights like this:

grant update on Table2 to 'newuser1';

everything works fine, but when calling a procedure (like it is shown above), I always get a error:

ERROR 1133 (42000): Can't find any matching row in the user table

What is the correct solution of this problem? How should I create a user, pass it to procedure and use it there?

0

There are 0 best solutions below