Problems with a procedure that grants permissions in a BD in MySQL

Question

Problems with a procedure that grants permissions in a BD in MySQL

Navigation

#1 by (0 votes)

0

I am doing a procedure stored in MySQL that allows me to give certain permissions to the user that receives in the parameters, however, it does not assign them to the user that sent it by parameters when calling it, but, it creates a user with the same name as the parameter (in this case 'user') and assign the permissions to that user. How could I do to create such a procedure that allows me to give permissions to a user? Here the code of the procedure:

    DELIMITER //
    CREATE PROCEDURE tracker_system_db.administrador (IN usuario VARCHAR(20))
    BEGIN
    GRANT ALL PRIVILEGES ON tracker_system_db.* TO usuario;
    flush PRIVILEGES;
    END;
    //

mysql permisos stored-procedure

asked by Tony 18.09.2018 в 21:58

source

1 answer

Write to CSV file from java Help with a Java program, NetBeans

score 0 · Answer 1

The following code creates a procedure stored in MySQL to assign all privileges to a user, the user's name must be passed as parameter and the procedure first: it determines if the user exists and then assigns the privileges.

Test and mark the answer if it meets your requirement.

CREATE DEFINER='root'@'localhost' PROCEDURE 'set_admin'(
     IN 'p_usuario' VARCHAR(50))
    READS SQL DATA
BEGIN

    DECLARE existe boolean DEFAULT FALSE;

    SELECT
      COUNT(USER) INTO existe
    FROM 'mysql'.'USER'
    WHERE 'mysql'.'USER'.'USER' = 'p_usuario';

    IF NOT existe THEN
        SELECT 'Este usuario no existe';
    ELSE
        SET @'sql' := CONCAT('GRANT ALL PRIVILEGES ON tracker_system_db.* TO ', 'p_usuario');
        PREPARE statement FROM @'sql';
        EXECUTE statement;                   -- Ejecutar query.
        DEALLOCATE PREPARE statement;        -- Eliminar query alojado en memoria.
        FLUSH PRIVILEGES;

    END IF;

END