1

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?

Skotti Bendler
  • 753
  • 1
  • 6
  • 17
  • Option one: [13.5 Prepared SQL Statement Syntax](https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html). Option two: [6.3.4 Using Roles](https://dev.mysql.com/doc/refman/8.0/en/roles.html). – wchiquito Jun 02 '18 at 10:35
  • @wchiquito, Great, thank you! I just used an old MySQL without any roles. – Skotti Bendler Jun 02 '18 at 11:19

0 Answers0