Mysql – grant permission to SHOW FIELDS

MySQLpermissions

I create a user to make a dump twice a week but when I try to execute mysqldump I have this error

mysqldump: Couldn't execute 'SHOW FIELDS FROM `contabilidad_amortizaciones_completas_por_id_vista`': execute command denied to user 'dump'@'%' for routine 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' (1370)

The grants to this user are these

GRANT SELECT, SHOW DATABASES, LOCK TABLES, EVENT ON *.* TO 'dump'@'%'

The funcion call 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' have this code:

CREATE DEFINER = 'root'@'%'
FUNCTION 
bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc(_id_amortizacion int)
RETURNS decimal(10,2)
  BEGIN
    SET @resultado = 0; 
    ( SELECT SUM(R.acumulado) FROM (
             SELECT MAX(`cadm`.`acumulado`) AS `acumulado`,
              cadm.id_amortizacion
              FROM `contabilidad_amortizaciones_detalles_meses` `cadm`
              WHERE ((`cadm`.`id_amortizacion` = _id_amortizacion)
              AND (`cadm`.`contabilizado` = 1))
              GROUP BY `cadm`.`anio_detalle`, `cadm`.`contabilizado`
            ) AS R
         GROUP BY R.id_amortizacion INTO @resultado
    );
 RETURN @resultado;
 END

I have other instances with other users-dump with this permissions and I dont have any problem.

Thanks

Best Answer

mysqldump

will dump the CREATE VIEW. However, it shows it twice. See the second version for the correct output.

Including the option --no-data is optional.

You do need SHOW VIEW permissions to when using mysqldump or SHOW CREATE VIEW.