Fix Disabled Edit or Execute Options for Stored Procedures in phpMyAdmin

When Edit or Execute options for stored procedures are unavailable in phpMyAdmin. This article explains why these options may be disabled.

When we access phpMyAdmin from cPanel, it uses either of the below-mentioned users to log in to the DB server: 

1) The CPanel username if the cPanel login happened using cPanel credentials. 
[OR]
2) The temporary "cpsess_" user if the cPanel login has happened from cPanel Session URL or if it was accessed from WHM.

You might have seen phpMyAdmin not allowing you to Edit or Execute the stored procedures. The options for Edit and Execute will be greyed out.

The issue is due to the lack of privileges on Stored Procedures for DB user used to access phpMyAdmin. Please note that the Edit and Execute options for Stored Procedures will be available for the DB user who created those routines. That DB user is called the "Definer" of the routine. Only the Definer will be able to edit or execute the Stored Procedures.

How to find the Definer for any Routine

1) Go to cPanel >> phpMyAdmin >> SQL tab

2) Run the Query given below: 

SELECT `ROUTINE_NAME`, `DEFINER` FROM information_schema.ROUTINES

3) You will see the Routine name and Definer getting displayed. 

4) If you want to see the Definer for any particular Routine Modify the above-mentioned SQL query as follows: 

SELECT `ROUTINE_NAME`, `DEFINER` FROM information_schema.ROUTINES where ROUTINE_NAME="<Name_of_the_Routine_here>"


You have to login to phpMyAdmin or any MySQL client using the credentials of Definer DB user in order to Edit/Execute the Stored Procedure. Since cPanel does not allow you to log in to phpMyAdmin via MySQL DB users, you may need to login to the DB server via any MySQL clients like MySQL Toad. You can even download phpMyAdmin and install it under a subfolder to access the login page. 

1) Download the phpMyAdmin from the URL: https://www.phpmyadmin.net/downloads/

2) Then Upload and extract the zip file into a subfolder like https://mydomain.tld/ppmadmin/


You can search in the configuration files in  public_html to know the password of the Definer_DB_USER using the command given below: 

# sudo grep -irl <Definer_DB_USER> /home/user/public_html/

If you find the Login password for Definer_DB_USER from the configuration file, you can use the credentials to log in via https://mydomain.tld/ppmadmin/ and you should be able to see the Edit/Execute option for Stored Procedures.