18.10. Stored Routine Privileges and Execution Security

Several privileges apply to the use of stored procedures and functions:

• To create a routine, you must have the CREATE ROUTINE privilege for it.
  
  
• To execute a routine, you must have the EXECUTE privilege for it.
  
  
• To drop a routine or alter its definition, you must have the ALTER ROUTINE privilege for it.
  
  
• To grant privileges for a routine, you must have the GRANT OPTION privilege for it.
  
  

When you create a stored routine, MySQL automatically grants to you the EXECUTE and ALTER ROUTINE privileges for it. These privileges enable you to invoke the routine or remove it later. (You can verify that you have these privileges by issuing a SHOW GRANTS statement after creating a routine.)

A GRANT statement that grants the ALL privilege specifier at the global or database level includes all stored routine privileges except GRANT OPTION. For example, the following statement grants the given account all privileges for the world database, including privileges to define, use, and drop stored routines:

 GRANT ALL ON world.* TO 'magellan'@'localhost'; 

To grant the GRANT OPTION privilege as well, include a WITH GRANT OPTION clause at the end of the statement.

The EXECUTE, ALTER ROUTINE, and GRANT OPTION privileges can be granted at the individual routine level, but only for routines that already exist. To grant privileges for an individual routine, name the routine qualified with a database name and preceded with the keyword PROCEDURE or FUNCTION to indicate the routine type. The following statement grants the given account permission to execute or alter the world_record_count() procedure in the world database:

 GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world.world_record_count   TO 'magellan'@'localhost';