The MySQL grant system takes stored routines into account as follows:
CREATE ROUTINEprivilege is needed to create stored routines.
ALTER ROUTINEprivilege is needed to alter or drop stored routines. This privilege is granted automatically to the creator of a routine if necessary, and dropped from the creator when the routine is dropped.
EXECUTEprivilege is required to execute stored routines. However, this privilege is granted automatically to the creator of a routine if necessary (and dropped from the creator when the routine is dropped). Also, the default
SQL SECURITYcharacteristic for a routine is
DEFINER, which enables users who have access to the database with which the routine is associated to execute the routine.
automatic_sp_privilegessystem variable is 0, the
ALTER ROUTINEprivileges are not automatically granted to and dropped from the routine creator.
The creator of a routine is the account used to execute the
CREATEstatement for it. This might not be the same as the account named as the
DEFINERin the routine definition.
The account named as a routine
DEFINERcan see all routine properties, including its definition. The account thus has full access to the routine output as produced by:
The contents of the Information Schema
SHOW CREATE FUNCTIONand
SHOW CREATE PROCEDUREstatements.
SHOW FUNCTION CODEand
SHOW PROCEDURE CODEstatements.
SHOW FUNCTION STATUSand
SHOW PROCEDURE STATUSstatements.
For an account other than the account named as the routine
DEFINER, access to routine properties depends on the privileges granted to the account:
SHOW_ROUTINEprivilege or the global
SELECTprivilege, the account can see all routine properties, including its definition.
EXECUTEprivilege granted at a scope that includes the routine, the account can see all routine properties except its definition.