The MySQL grant system takes stored routines into account as follows:
The
CREATE ROUTINE
privilege is needed to create stored routines.The
ALTER ROUTINE
privilege 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.The
EXECUTE
privilege 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 defaultSQL SECURITY
characteristic for a routine isDEFINER
, which enables users who have access to the database with which the routine is associated to execute the routine.If the
automatic_sp_privileges
system variable is 0, theEXECUTE
andALTER ROUTINE
privileges are not automatically granted to and dropped from the routine creator.The creator of a routine is the account used to execute the
CREATE
statement for it. This might not be the same as the account named as theDEFINER
in the routine definition.The account named as a routine
DEFINER
can 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
ROUTINES
table.The
SHOW CREATE FUNCTION
andSHOW CREATE PROCEDURE
statements.The
SHOW FUNCTION CODE
andSHOW PROCEDURE CODE
statements.The
SHOW FUNCTION STATUS
andSHOW PROCEDURE STATUS
statements.
For an account other than the account named as the routine
DEFINER
, access to routine properties depends on the privileges granted to the account:With the
SHOW_ROUTINE
privilege or the globalSELECT
privilege, the account can see all routine properties, including its definition.With the
CREATE ROUTINE
,ALTER ROUTINE
orEXECUTE
privilege granted at a scope that includes the routine, the account can see all routine properties except its definition.