GRANT in procedure with user as parameter

[stryga42]

Probably a newbie-question...
I try to use a procedure to grant privileges to users where the user name is an IN parameter of the procedure. Like

DELIMITER //
CREATE PROCEDURE mydb.CreateUserP(IN username TEXT)
BEGIN
GRANT Usage ON *.* TO username;
GRANT Update ON mydb.* TO username;
END;
//
DELIMITER ;

Lets assume user foo exist, a CALL mybd.CreateUserP('foo'); fails with

SQL Error [1133] [28000]: (conn=148) Can't find any matching row in the user table

If I modify the procedure to a hard-coded user name

DELIMITER //
CREATE PROCEDURE mydb.CreateUserP(IN username TEXT)
BEGIN
GRANT Usage ON *.* TO 'foo';
GRANT Update ON mydb.* TO 'foo';
END;
//
DELIMITER ;

and it works (although it doesn't make much sense).
Is there some magic about parameters or how GRANT treats the username?
The call is executed as root of the DB, so privileges should be in place.
Many thanks!

 

[akretschmer]

since this is a german speaking group it would increase the chance of a answer if you could switch to german. seems that possible?

 

[stryga42]

Sorry, kein Problem. (Englisch ist nur meine Default-Sprache bei SW Entwicklung ) Re-Post, da ich keine Edit-Option gefunden habe:
Ich versuche in einer stored procedure mittels GRANT Berechtigungen zu vergeben, wobei der Benutzername über einen IN Paramter bereitgestellt wird. Mein Ansatz:

DELIMITER //
CREATE PROCEDURE mydb.CreateUserP(IN username TEXT)
BEGIN
GRANT Usage ON *.* TO username;
GRANT Update ON mydb.* TO username;
END;
//
DELIMITER ;

Angenommen der Beutzer foo existiert, wundert es mich, dass CALL mybd.CreateUserP('foo'); mit der Fehlermeldung

SQL Error [1133] [28000]: (conn=148) Can't find any matching row in the user table

abbricht. Wenn ich den Benutzernamen direkt in den Sourcecode direkt einfüge

DELIMITER //
CREATE PROCEDURE mydb.CreateUserP(IN username TEXT)
BEGIN
GRANT Usage ON *.* TO 'foo';
GRANT Update ON mydb.* TO 'foo';
END;
//
DELIMITER ;

funktioniert es einwandfrei, ist aber natürlich reichlich nutzlos.
Verstehe ich den Parameter-Mechnismus von procedures falsche oder behandelt GRANT den Benutzer irgendwie besonders?
Der CALL Aufruf läuft unter DB-root, hat also wohl keine Berechtigungsprobleme.
Ist vielleicht eine "echte Anfängerfrage" - ich stehe derzeit aber auf der sprichwörtichen Leitung.

 

[BerndB]

Hallo stryga42 ,

es wundert mich ein wenig, das hier niemand antwortet oder gute Ratschläge gibt wie es in anderen Systemen funktioniert.
Hier aber eine Lösung für dich.

Bei MySQL / MariaDB ist es so das du einige Sachen in einem Query nicht durch Variablen ersetzen kannst (Felder, Tabellen,...).

Hier muss du einen kleinen Trick anwenden. Das Zauberwort heisst Prepared Statement bei dem ein Query als String ausgeführt werden kann.


So könnte deine SP aussehen:

DROP PROCEDURE IF EXISTS mydb.CreateUserP;

DELIMITER //
CREATE PROCEDURE mydb.CreateUserP(IN username TEXT, IN host TEXT)
BEGIN
    SET @sql = CONCAT("GRANT USAGE,UPDATE,INDEX,EXECUTE ON *.* TO `",username,"`@`",host,"`;");
     SELECT @SQL;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

Du musst auch nicht mehrere Grants machen, sondern kannst diese auch in einem Statement angeben. Usage lässt sich nicht entfernen und hat die Bedeutung : KEINE GRANTS


Hier der Test:

mysql> SHOW GRANTS FOR `foo`@`localhost`;
+-----------------------------------------+
| Grants for foo@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `foo`@`localhost` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> call CreateUserP("foo","localhost");
+---------------------------------------------------------------+
| @SQL                                                          |
+---------------------------------------------------------------+
| GRANT USAGE,UPDATE,INDEX,EXECUTE ON *.* TO `foo`@`localhost`; |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR `foo`@`localhost`;
+----------------------------------------------------------+
| Grants for foo@localhost                                 |
+----------------------------------------------------------+
| GRANT UPDATE, INDEX, EXECUTE ON *.* TO `foo`@`localhost` |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

In meinen Beispiel müsstest du noch die GRANTS abändern und du kannst SELECT @SQL; rauswerfen.
Dies ist nur zur Überprüfung des erzeugten Querys drin.

Ich hoffe es hilft die weiter

Gruß

Bernd

 

[stryga42]

Danke, das erklärt eine Menge Mir war nicht klar, dass user-, local-Variablen und Parameter von procedures doch so unterschiedlich sind. Bin wohl C / C++ "verseucht".