Mega Code Archive

 

Categories / MySQL Tutorial / Procedure Function

 

Stored Procedure Permissions

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.