Mysql – nother/better way to create and maintain large amounts of procedures

MySQLscriptingstored-procedures

So I've decided to take a step back, and scrutinize the way I implement procedures on my databases.

I'll list what I have done, give my reason for doing so and hopefully I will be told that I'm a moron, and should be doing it a better way

All my procedures are stored in an SQL file, in the the following way:

SELECT 'Creating guestuser user' AS ' ';
DROP USER 'guestuser'@'appserver';
CREATE USER 'guestuser'@'appserver' IDENTIFIED BY 'Password';

SELECT 'Dropping mydatabase guest procedures' AS ' ';
DROP PROCEDURE IF EXISTS myProcOne;
DROP PROCEDURE IF EXISTS myProcTwo;

SELECT 'Creating mydatabase procedures' AS ' ';
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcOne`(_id BIGINT)
    DETERMINISTIC
    COMMENT 'selects name from fluffy cats table given id'
BEGIN
    SELECT id FROM FLUFFYCATS WHERE id=_id;
END
$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcTwo`(_name varchar(100))
    COMMENT 'inserts a new name into fluffy cats'
BEGIN
    INSERT INTO FLUFFYCATS (name) VALUES (_name);
END
$$
DELIMITER ;

SELECT 'Creating mydatabase guest permissions' AS ' ';
GRANT EXECUTE ON PROCEDURE db_mydatabase.myProcOne TO 'guestuser'@'appserver';
GRANT EXECUTE ON PROCEDURE db_mydatabase.myProcTwo TO 'guestuser'@'appserver';

I'll start from the top. I drop and create the user, so that it looses all it's procedure permissions. As you can see, I don't give the use any permissions until the end of the file. The reason I have chosen to do this, is so that the permissions are as restrictive as possible (And therefore more secure?) …

Then I drop the procedures (If they exist) so that they will be created with any edits I have made

Then onto the actual creation of the procedures … I don't think I've done anything out of the ordinary here, but always open for suggestions.

Then the script finishes with giving the user the permissions for each procedure.

Of course this is just an example file, but my real one has just reached the 100th procedure —Brings out cake— … And would be grateful for any suggestions as to how I could maybe reduce the workload when creating a procedure (Drop,Create,Permission) …

Best Answer

I'm trying some ways to make something that could work for you but MySQL has limitations as I see. I'm trying to make a Stored Procedure that you could read all the information of the procedures from a table.

Example:

enter image description here

The Stored Procedure:

    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
    IN eProcedureID int(11),
    IN eUser VARCHAR(50),
    IN ePass VARCHAR(50),
    IN eHost VARCHAR(50))
    sp_np:BEGIN
    /*  | Variable Declaration | */
        DECLARE vProcedureID int(11) DEFAULT 0;
        DECLARE vUser VARCHAR(50) DEFAULT '';
        DECLARE vPass VARCHAR(50) DEFAULT '';
        DECLARE vHost VARCHAR(50) DEFAULT '';
        DECLARE vReg INT(8) DEFAULT 0;
    /*  | Variable Asignation | */
        SET vProcedureID=eProcedureID;
        SET vUser       =eUser;
        SET vPass       =ePass;
        SET vHost       =eHost;
    /*  | WorkPlace |   */
    SET vReg=(SELECT COUNT(*) FROM PRUEBA.new_table WHERE new_table.id=vProcedureID);
    IF (vReg>0) THEN # If Exists EXECUTE
        SET @DROP_USER=CONCAT(" DROP USER ","'",vUser,"'@'",vHost,"';");
        SET @CREATE_USER=CONCAT("CREATE USER ","'",vUser,"'@'",vHost,"'"," IDENTIFIED BY '",vPass,"';");
        SELECT
            new_table.create,
            new_table.proname,
            new_table.prodb
        INTO @PROCEDURE_CREATION,@PRONAME,@PRODB
        FROM PRUEBA.new_table WHERE new_table.id=vProcedureID;
        SET @GPRIVILEGES=CONCAT(" GRANT EXECUTE ON PROCEDURE ",@PRODB,'.',@PRONAME," TO '",vUser,"'@'",vHost,"';");

        SELECT vProcedureID,vUser,vPass,vHost,@PRODB,@PRONAME,@DROP_USER,@CREATE_USER,@GPRIVILEGES;

        PREPARE DROPUSER FROM @DROP_USER; EXECUTE DROPUSER; DEALLOCATE PREPARE DROPUSER;
        PREPARE CREATE_USER FROM @CREATE_USER; EXECUTE CREATE_USER; DEALLOCATE PREPARE CREATE_USER;
        PREPARE PROCEDURE_CREATION FROM @PROCEDURE_CREATION; EXECUTE PROCEDURE_CREATION; DEALLOCATE PREPARE PROCEDURE_CREATION;
        PREPARE GPRIVILEGES FROM @GPRIVILEGES; EXECUTE GPRIVILEGES; DEALLOCATE PREPARE GPRIVILEGES;

        LEAVE sp_np;
    ELSE
        SELECT '01' AS sERROR,'ProcedureID doesnt exists.' AS sDESC_ERROR;
        LEAVE sp_np;
    END IF;
    END;

As MySQL documentation says:

SQL syntax for prepared statements cannot be used within stored routines (procedures or functions), or triggers. This restriction is lifted as of MySQL 5.0.13 for stored procedures, but not for stored functions or triggers. However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.

And all you had to do it was:

    CALL PRUEBA.new_procedure(
    '0000000001', #ProcedureID int(11),
    'guestuser', #User VARCHAR(50),
    'Password', #Pass VARCHAR(50),
    'appserver'); #Host VARCHAR(50)

Hope the SP helps you to find one better way.

Related Question