2

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.

QUESTIONS

  • Could somebody help me understand which specific part of this code raises this error, i.e. requires the said privilege?
  • Is there any way I could have corresponding functionality without a privileged call?

enter image description here

Community
  • 1

3 Answers3

3

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:

  1. How you you were allowed to authenticate
  2. 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 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:

GRANT EXECUTE ON medilife.* TO 'myuser'@'somedb';

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
0

Try permitting TRUNCATE on the table to the user who is not root. https://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

DROP, DELETE, EXECUTE are necessary.

Bimal Poudel
  • 147
  • 6
0

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

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

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.

More detailed info on GLOBAL_VARIABLES http://dev.mysql.com/doc/refman/5.1/en/variables-table.html

Hope it helps..

  • Thanks Hiren, I do not quite see where the INFORMATION_SCHEMA DB is being manipulated here, if that is what you were implying? –  Apr 08 '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.. –  Apr 08 '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.. –  Apr 08 '13 at 12:06