Cursor in Mysql has other rights than user?

Question

i have two databases, db1 and db2.

User man1 had all privileges to db1 but only executing provileges for all functions and procedures in db2.

Calling function db2.getValue from db1 is successful and returns correct values, also if call for db2.getValue is nested in a stored procedure.

Now i create a stored Procedure proc1 and within this procedure i use a cursor to fetch values (_val1, _val2,--) from a table in db1 and call db2.getValue(_val1).

Now somthing really strange happens:

The first loop from the cursor finish, but the value from db2.getValue is empty. At the end of this loop, the loop ends and stops.

Does anyone have a hint where the problem could be here? Calling db1.getValue(_val1) instead of db2.getValue(_val1) works fine.

Therefore my idea was that within the cursor, the privileges for db2 might be not the same?

Kind Regards Solick

Answer 1

Yes, by design, a cursor can behave differently than the same SELECT query might behave if it were executed by the user who called the procedure.

Stored programs and views are defined prior to use and, when referenced, execute within a security context that determines their privileges. These privileges are controlled by their DEFINER attribute, and, if there is one, their SQL SECURITY characteristic.

^{— http://dev.mysql.com/doc/refman/5.6/en/stored-programs-security.html}

If you don't specify a DEFINER when you create a stored program (proc, function, trigger, or event) or a view, then the object, when accessed, runs with the privileges of the user who originally defined it, not the user who invoked it.

You have three options, here:

• Verify or possibly modify the permissions of the current DEFINER user if appropriate; or,
• Specify a different DEFINER user when defining the stored program or view... you can do this as long as you (the person creating the object) have the SUPER privilege, and users invoking (accessing) the object will temporarily have the rights of that DEFINER user instead; or,
• Add SQL SECURITY INVOKER to the definition of procedures, functions, and views (though not triggers or events), causing the object to run with the privileges of the user who invoked it, instead of the definer, which is the default behavior.

To see the permissions the existing definer has, for example if you see DEFINER=`someguy`@`localhost`:

mysql> SHOW GRANTS FOR 'someguy'@'localhost';

You can find the current definer in the definition of the procedure, with SHOW CREATE PROCEDURE procedure_name;.