MySQL - Stored Procedure om gebruikers in bestaande databases aan te maken

Deze Stored Procedure kun je uitvoeren na de Stored Procedure van het artikel: MySQL - Stored Procedure om meerdere databases aan te maken

-- De gebruikers voor de databases aanmaken en rechten op die databases geven

DROP PROCEDURE IF EXISTS users_aanmaken;

DELIMITER $$

CREATE PROCEDURE users_aanmaken()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i <= 10 DO

SET @username = CONCAT('user00', i);

SET @password = CONCAT('password', i);

-- Create user with a unique name and password

SET @create_user = CONCAT('CREATE USER ''', @username, '''@''%'' IDENTIFIED BY ''', @password, ''';');

PREPARE stmt FROM @create_user;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

-- Grant privileges to allow users to work on their own database

        SET @grant_privileges = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON ', @username, '_db.* TO ''', @username, '''@''%'';');

PREPARE stmt FROM @grant_privileges;

-- PREPARE stmt FROM @create_db;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET i = i + 1;

END WHILE;

END$$;

DELIMITER ;

-- De Stored Procedure aanroepen

CALL users_aanmaken();

Het resultaat controleren