Grant privileges to application user when schemas are created

[jsierles]

If an application user can create a schema, it must also be assigned privileges to use it. The following stored procedure and scheduled event could automate this process.

DELIMITER $$

CREATE PROCEDURE GrantPrivileges()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE schema_name VARCHAR(64);
  DECLARE cur CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO schema_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @grant_stmt = CONCAT('GRANT ALL PRIVILEGES ON ', schema_name, '.* TO ''your_username''@''localhost'';');
    PREPARE stmt FROM @grant_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END $$

DELIMITER ;

CREATE EVENT GrantPrivilegesEvent
ON SCHEDULE EVERY 1 MINUTE
DO
  CALL GrantPrivileges();