Access denied; you need the SUPER privilege for this operation

User, date Message
Written by kalvaro
3 years ago
Category: Import/Export
442 posts since Thu, 29 Nov 07 		I have two databases on the same server. Each one has its own user with full privileges inside its database (and nothing else outside) and its own saved session within session manager.

When I use the "Export database as SQL" feature to copy a complete database into the other, the functions and procedures generate this error:

SQL Error 1227: Access denied; you need the SUPER privilege for this operation



While the error message is self-explanatory (if I assign such privilege the operation completes flawlessly) I can't understand why it's required since I don't need it to create the objects in the first place. If I run the CREATE code manually in the target database MySQL doesn't complaint.

This is the code for one of the routines:

CREATE PROCEDURE `actualizar_categoria_nivel`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT 'Actualiza los valores de la tabla categoria_nivel'
BEGIN
/*
* Reconstruye los datos de la tabla categoria_nivel
*
* http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/
*/
DELETE FROM categoria_nivel;

INSERT INTO categoria_nivel (categoria_id, categoria_padre_id, nivel)
SELECT hi.categoria_id, categoria_padre_id, nivel
FROM (
SELECT categoria_connect_by_parent_eq_prior_id(categoria_id) AS categoria_id, @nivel AS nivel
FROM (
SELECT @start_with := 0,
@categoria_id := @start_with,
@nivel := 0
) vars, categoria
WHERE @categoria_id IS NOT NULL
) ho
JOIN categoria hi ON hi.categoria_id = ho.categoria_id;
END
Written by ansgar
3 years ago
3976 posts since Fri, 07 Apr 06 		Probably it's not the CREATE PROCEDURE command which is rejected but some other stuff? Look into the lower SQL log where you can also see the error message. The prior line to that error is the one which fails.
Written by kalvaro
3 years ago
442 posts since Thu, 29 Nov 07 		The log panel shows this:

SELECT 'infocap_infocap_web' AS `Database`, 'actualizar_categoria_nivel' AS `Table`, -1 AS `Rows`, 0 AS `Duration`;
[- test @ localhost (5.1)] /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
[- test @ localhost (5.1)] USE `test`;
[- test @ localhost (5.1)] DROP PROCEDURE IF EXISTS `actualizar_categoria_nivel`;
SHOW CREATE PROCEDURE `infocap_infocap_web`.`actualizar_categoria_nivel`;
[- test @ localhost (5.1)] CREATE DEFINER=`ap0042`@`%` PROCEDURE `actualizar_categoria_nivel`() SQL SECURITY INVOKER COMMENT 'Actualiza los valores de la tabla categoria_nivel' BEGIN /* * Reconstruye los datos de la tabla categoria_nivel * * http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ */ DELETE FROM categoria_nivel; INSERT INTO categoria_nivel (categoria_id, categoria_padre_id, nivel) SELECT hi.categoria_id, categoria_padre_id, nivel FROM ( SELECT categoria_connect_by_parent_eq_prior_id(categoria_id) AS categoria_id, @nivel AS nivel FROM ( SELECT @start_with := 0, @categoria_id := @start_with, @nivel := 0 ) vars, categoria WHERE @categoria_id IS NOT NULL ) ho JOIN categoria hi ON hi.categoria_id = ho.categoria_id; END;
/* [- test @ localhost (5.1)] SQL Error (1227): Access denied; you need the SUPER privilege for this operation */
[- test @ localhost (5.1)] /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/* [- test @ localhost (5.1)] Connection to 127.0.0.1 closed at 2010-07-15 11:23:44 */



I can see it's issuing a CREATE DEFINER=`ap0042`@`%` PROCEDURE command. The DEFINER=`ap0042`@`%` part does now show up in the "CREATE code" tab. It appears to be the output of SHOW CREATE PROCEDURE `actualizar_categoria_nivel` so now I can see the problem: MySQL adds information about the user who created the object even though it's irrelevant for me (but of course, MySQL doesn't know what).

What could/should be done?
Written by kalvaro
3 years ago
442 posts since Thu, 29 Nov 07 		Two remarks about my previous post:

- Typo: The DEFINER=`ap0042`@`%` part does NOT show up in the "CREATE code" tab
- Of course it is the output of SHOW CREATE PROCEDURE: it's right there in the log :)
Written by ansgar
3 years ago
3976 posts since Fri, 07 Apr 06 		Hm, would be nice if Heidi strips the DEFINER=xyz clause from the SHOW CREATE PROCEDURE when exporting to SQL. Or would that imply any security issue?
Written by kalvaro
3 years ago
442 posts since Thu, 29 Nov 07 		It appears that the creator is only relevant when you want to set SQL SECURITY DEFINER but, honestly, I haven't even read that manual page...

Whatever, an export is basically a copy, isn't it? And copies, in general, imply that new objects get the permissions granted to the user that makes the copy, not the original owner. (I'm extrapolating from file copying.) With this point of view, the potential security breach would come from the fact that you might not be aware that you are using different credentials to read and write.

And we also have to consider that HeidiSQL allows to perform a live copy (from server to server) and a file dump.

I did not provide any solution, did I? evilgrin
Written by ansgar
3 years ago
3976 posts since Fri, 07 Apr 06 		> I did not provide any solution, did I?

Oh, even better - real analytics - well done. Pushes me to the opinion that there is no security issue when we do that.
 

Please login to leave a reply, or register at first.