Restore Database With Stored Procedures Requires SUPER Privileges ?
I am working with an ISP and trying to restore a mysql database. I recently added stored procedures to this database, and when i tried to restore it i got this message.
#1227 - Access denied; you need the SUPER privilege for this operation
The DEFINER is set to the current user account (the one i logged in with). When i talked to the ISP tech support they said they turned off stored procs in MySql because they require SUPER privileges to run. Here is the text of our online chat..
Brian [9:25:29 PM]: I am trying to restore a mysql database that has stored procedures in it. But the restore is failing on the create for the stored procs saying that i must have 'super' privileges to do that. How can i restore this database?
Keene [9:25:48 PM]: We do not support stored procedures on our servers. Sorry :(
Brian [9:26:02 PM]: you are kidding me
[9:26:30 PM]: why not?
Keene [9:28:11 PM]: Because they require super-user privledges. We've been trying to re-write MySQL so it doesn't, but because of the way it's coded and designed, it's simply not possible in a shared hosting environment, at least not yet.
Brian [9:30:39 PM]: it doesn't require super-user to execute them though, right?
Keene [9:31:29 PM]: It actually does. That's the kicker. There's no way to implement it without root access.
Brian [9:32:03 PM]: this is from the mysql manual for 5.03...
[9:32:04 PM]: If you specify the DEFINER clause, these rules determine the legal DEFINER user values:
*
If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
[9:32:59 PM]: super is required to specify another definer according to this, but not if the definer is the current user acct
Keene [9:35:20 PM]: Yes, the documentation does say that, but the code doesn't actually execute like that. It still requires root access to run the stored procedure, even if the definer is the current user. Although it doesn't act like it's using the root privileges, it does use them in the background, which opens up the possibility to create a stored procedure that grants root access to anyone.
[9:35:36 PM]: So, we've disabled them.
[9:35:58 PM]: At least until MySQL can create a patch that processes them differently.
+++++++++++++++++++++++++++++++
So my question is - is this true? Is there a bug that requires SUPER privileges to create/execute a stored proc in mysql?
View Replies !
TRACKER
