MySQL 5.0 introduced a few new privileges to manage stored programs. These privileges are:
Allows a user to create new stored programs.
Allows a user to alter the security mode, SQL mode, or comment for an existing stored program.
Allows a user to execute a stored procedure or function.
With these distinct privileges available, we can very granularly decide what we want to allow individual developers to be able to do (as in “Sam can run program X, but not make any changes to it.”).
To give a user permission to create a stored procedure,
function, or trigger, grant the
ROUTINE privilege to that user using the
GRANT statement. We can do this for a
specific database or for all databases on the server. For example,
gives the user
permission to create stored programs within the database
GRANT CREATE ROUTINE ON mydatabase.* TO sp_creator;
The ALTER ROUTINE privilege gives a user permission to change the security mode,
SQL mode, or comment for a stored procedure or function. However,
this privilege does not allow us to change the actual program code
of a procedure. To change the program code, we must
DROP and then
CREATE a new program. In the following
example, we change the security mode,
sql_mode setting, and comment for a
ALTER PROCEDURE simple_stored_proc ...