O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

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;

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required