#4023 (ok 4.1.1) Requires wildcard EXECUTE/ALTER ROUTINE on DB to allow Procedures to be executed by user

4.1.0
fixed
1
2013-12-17
2013-07-16
Nicholi
No

Ubuntu 12.04
MySQL: 5.5.31-0ubuntu0.12.04.2
phpMyAdmin: 4.0.3-1.precise~ppa.2 (from ppa:nijel/phpmyadmin repo)
I also just did an upgrade of phpMyAdmin from 3.4.10.1-1 (last version in official Ubuntu 12.04 repos). Have seen no real issues after upgrade, just wanted to play with the new Procedure/Routine stuff and found this quirk.

The phpmyadmin UI will not allow another user to execute/edit a procedure unless they have wildcard EXECUTE, ALTER ROUTINE access to the entire DB. Giving the user explicit EXECUTE, ALTER ROUTINE for the procedure name itself will not work, even though the user can manually call the routine from the SQL execute area.

Quick example, running the following commands as root

CREATE DATABASE testproc;
use testproc;
DELIMITER //
CREATE PROCEDURE test()
BEGIN
SELECT 1;
END//
DELIMITER ;
CREATE USER 'procuser'@'localhost' IDENTIFIED BY 'test';
GRANT SELECT ON testproc.* TO 'procuser'@'localhost';
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE testproc.test TO 'procuser'@'localhost';

Obviously just creates a db, procedure, a user, and gives the user access to procedure. Logging in via phpmyadmin with this user they will be able to see the routine, but cannot execute or edit it through the UI. They can manually run the sql command call test(); with no problems.

If you give the user wildcard EXECUTE and ALTER ROUTINE on the database, plus SELECT access to mysql.proc for some reason, phpmyadmin will allow the user to execute and edit the routine in the UI.

GRANT SELECT ON mysql.proc TO 'procuser'@'localhost';
GRANT EXECUTE, ALTER ROUTINE ON testproc.* TO 'procuser'@'localhost';

I'm not sure why the first access would be necessary, as I would imagine if phpmyadmin needs access to anything it would be mysql.procs_priv. But clearly the second seems to be a pretty large limitation. Is this intended behavior?

Discussion

  • Marc Delisle

    Marc Delisle - 2013-11-29
    • assigned_to: Marc Delisle
     
  • Marc Delisle

    Marc Delisle - 2013-11-30
    • assigned_to: Marc Delisle --> nobody
     
  • Marc Delisle

    Marc Delisle - 2013-12-15
    • assigned_to: Marc Delisle
     
  • Marc Delisle

    Marc Delisle - 2013-12-15
    • summary: Requires wildcard EXECUTE/ALTER ROUTINE on DB to allow Procedures to be executed by user --> (ok 4.1.1) Requires wildcard EXECUTE/ALTER ROUTINE on DB to allow Procedures to be executed by user
    • status: open --> resolved
    • Group: 4.0.3 --> 4.1.0
    • Priority: 5 --> 1
     
  • Marc Delisle

    Marc Delisle - 2013-12-17
    • Status: resolved --> fixed