Stored Procedure Permissions : Procedure « Procedure Function « MySQL Tutorial

Specific to procedures, the MySQL permissions scheme has the CREATE ROUTINE, ALTER ROUTINE, and EXECUTE privilege.

The permissions required for working with stored procedures are as follows:

Viewing permissions

To view stored procedures with SHOW PROCEDURE STATUS, you must have SELECT access to the mysql.proc table.

Or you must have the ALTER ROUTINE privilege for that particular procedure.

Calling permissions

To call a stored procedure

You need the ability to connect to the server and have the EXECUTE permission for the procedure.

EXECUTE permissions can be granted globally in the mysql.user table.

EXECUTE permissions can be granted at the database level in the mysql.db table

EXECUTE permissions can be granted for a specific routine in the mysql.procs_priv table.

Creating and altering permissions

To govern creating and altering a stored procedure, MySQL uses the CREATE ROUTINE and ALTER ROUTINE privilege.

Permissions for creating or changing procedures can be granted globally in the mysql.user table.

Permissions for creating or changing procedures can be granted at the database level in the mysql.db table.

Permissions for creating or changing procedures can be granted for a specific routine in the mysql.procs_priv table.

Dropping permissions

To drop a procedure, you must have the ALTER ROUTINE privilege.

Permissions for dropping procedures can be granted globally in the mysql.user table.

Permissions for dropping procedures can be granted at the database level in the mysql.db table.

Permissions for dropping procedures can be granted for a specific routine in the mysql.procs_priv table.