Re: VIEW DEFINTION permission

[Devart Team]

Let us do it step by step.

1. Connect as 'root' (for example), create a stored procedure and a user:

DELIMITER $$

CREATE DEFINER = 'root'@'localhost'
PROCEDURE test.procedure1()
BEGIN
  SELECT 1;
END$$

DELIMITER;

CREATE USER 'user1'@'%';
GRANT Select ON *.* TO 'user1'@'%';[/code]

2. Connect as 'user1':

Execute SHOW CREATE PROCEDURE command -

SHOW CREATE PROCEDURE test.procedure1;

Field `Create Procedure` contains definition text -

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure1`()
BEGIN
  SELECT 1;
END

Or refer to information schema -

SELECT ROUTINE_DEFINITION FROM information_schema.routines WHERE ROUTINE_SCHEMA = 'test' AND ROUTINE_NAME = 'procedure1';

Field `ROUTINE_DEFINITION` contains body -

BEGIN
  SELECT 1;
END

Devart Company,
MySQL management tools
http://www.devart.com/dbforge/mysql/