Script for applying permission on all Routines in MySQL or MariaDB

Privileges used for Routines:

Routines or Procedure privileges:
ALTER ROUTINE (alter or drop),CREATE ROUTINE,EXECUTE

Check the Permission on Procedure or routines:

SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'ALTER ROUTINE';
SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'CREATE ROUTINE';
SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'EXECUTE';

Grant or Revoke only executes privilege for security no other will be able to check the code:

Syntax:
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;
Example:
GRANT EXECUTE ON FUNCTION calsalary TO 'username'@'localhost';

Syntax:
REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user;
Example:
REVOKE EXECUTE ON FUNCTION calsalary FROM 'username'@'localhost';

Grant or Revoke permission on all routines, procedures or functions present in database:

GRANT Execute ON  databasename.* to 'username'@'%';
REVOKE EXECUTE ON databasename.* to 'username'@'%';

Script to Grant Permission on all routines:

select case when ROUTINE_TYPE='PROCEDURE' then 
concat('GRANT EXECUTE ON PROCEDURE ',ROUTINE_SCHEMA,'.',ROUTINE_NAME,' to ''username''@''localhost'';') 
when ROUTINE_TYPE = 'FUNCTION' then 
concat('GRANT EXECUTE ON FUNCTION ',ROUTINE_SCHEMA,'.',ROUTINE_NAME,' to ''test''@''localhost'';') 
end as 'Command'
from information_schema.ROUTINES r  where routine_schema = 'classicmodels';

Command                                                                               |-----------------------------------------------------------------------------------+
GRANT EXECUTE ON PROCEDURE classicmodels.myprocedure to 'username'@'localhost';     |
GRANT EXECUTE ON PROCEDURE classicmodels.myprocedure1 to 'username'@'localhost';    |
GRANT EXECUTE ON PROCEDURE classicmodels.populate to 'username'@'localhost';        |
GRANT EXECUTE ON FUNCTION classicmodels.sf_escapechars to 'username'@'localhost';   |

Script to Revoke permission from all routines, procedures, or functions in Schema or user:

select case when ROUTINE_TYPE='PROCEDURE' then 
concat('REVOKE EXECUTE ON PROCEDURE ',ROUTINE_SCHEMA,'.',ROUTINE_NAME,' FROM ''username''@''localhost'';') 
when ROUTINE_TYPE = 'FUNCTION' then 
concat('REVOKE EXECUTE ON FUNCTION ',ROUTINE_SCHEMA,'.',ROUTINE_NAME,' FROM ''test''@''localhost'';') 
end as 'Command'
from information_schema.ROUTINES r  where routine_schema = 'classicmodels';

Command                                                                                  |------------------------------------------------------------------------------------+

REVOKE EXECUTE ON PROCEDURE classicmodels.myprocedure FROM 'username'@'localhost';   |
REVOKE EXECUTE ON PROCEDURE classicmodels.myprocedure1 FROM 'username'@'localhost';  |
REVOKE EXECUTE ON PROCEDURE classicmodels.populate FROM 'username'@'localhost';      |