Permissions Required for Stored Programs

MySQL 5.0 introduced a few new privileges to manage stored programs. These privileges are:

CREATE ROUTINE

Allows a user to create new stored programs.

ALTER ROUTINE

Allows a user to alter the security mode, SQL mode, or comment for an existing stored program.

EXECUTE

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.").

18.1.1. Granting Privileges to Create a Stored Program

To give a user permission to create a stored procedure, function, or trigger, grant the CREATE 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, the following GRANT statement gives the user sp_creator permission to create stored programs within the database mydatabase:

GRANT CREATE ROUTINE ON mydatabase.* TO sp_creator;

18.1.2. Granting Privileges to Modify a Stored Program

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 procedure:

ALTER PROCEDURE simple_stored_proc SQL SECURITY INVOKER READS SQL DATA COMMENT 'A simple stored procedure';

18.1.3. Granting Privileges to Execute a Stored Program

The EXECUTE privilege gives a user permission to execute a stored procedure or function. (For triggers, see Chapter 11.) EXECUTE privileges should be granted selectively, especially if the program is created with the "definer rights" security setting (see the section "The SQL SECURITY Clause" later in this chapter). The syntax for this form of the GRANT statement is:

GRANT EXECUTE [ON {PROCEDURE|FUNCTION}] database.program_name TO user

You can omit the ON PROCEDURE or ON FUNCTION clause if you are performing a wildcard grant, as in the following example:

GRANT EXECUTE ON mydatabase.* TO sp_creator;

If you are granting access to a specific program, you must specify ON PROCEDURE or ON FUNCTION explicitly; it is possible for a stored procedure and a stored function to have the same name, and it is unacceptable to issue an ambiguous security command. To grant the EXECUTE privilege on the procedure mydatabase.test1, issue the following statement:

GRANT EXECUTE ON PROCEDURE mydatabase.test1 TO sp_creator;