You need to examine the privileges of the connect user. The next time you connect run this:
SHOW GRANTS FOR CURRENT_USER();
This will tell you two things:
- How you you were allowed to authenticate
- What privileges you have with that user
Look at the beginning of the Stored Procedure definition
CREATE DEFINER = 'root'@'localhost' ...
The fact that it says
DEFINER (the SQL Security) means that you assume the privileges of 'root'@'localhost' when you call the Stored Procedure. Nevertheless, there is a catch: you need the EXECUTE privilege. Why ?
According to the MySQL Documentation on Stored Procedures:
Definer and invoker security contexts differ as follows:
A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example, EXECUTE to call a stored procedure or SELECT to select from a view), but when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the DEFINER account is highly privileged (such as a root account), the object can perform powerful operations no matter who invokes it.
A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.
Therefore, whatever you get as
CURRENT_USER(), simply grant
EXECUTE to that user. For example, if
'myuser'@'somedb', login as
'root'@'localhost' and run
GRANT EXECUTE ON *.* TO 'myuser'@'somedb';
Then, that user can run any Stored Procedure. If you want to restrict it to just the Stored Procedures in the
medilife database, then do:
GRANT EXECUTE ON medilife.* TO 'myuser'@'somedb';
Give it a Try !!!