Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

3 Answers
3

The INFORMATION_SCHEMA database is a "pseudo database" containing server-generated views and as far as I know, contains only read-only data. If you need to alter a variable, you need to go the standard way, see Per's answer. From the

INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.

Thanks Hiren, I do not quite see where the INFORMATION_SCHEMA DB is being manipulated here, if that is what you were implying?
–
chrsvaApr 8 '13 at 11:54

hmmm... by closely looking at the code, I am still confused with the line "DECLARE" AND "SET".. But this seems working on my local.. let me try again..
–
Hiren PandyaApr 8 '13 at 12:01

ok.. Now the query you have added on the first line, truncates the selected tables. but those tables are selected from the information_schema for the truncate operation, which requires SUPER privileges..
–
Hiren PandyaApr 8 '13 at 12:06

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 ?

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 CURRENT_USER() is '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: