0

I'm using DBVisualizer with against my shared hosting database.

I'm not able to see the actual SP code, if I log from a different IP address, than the one from what I have created the SPs.

Any ideas? I was not able to find any option in CPanel that might change this behavior.

Thanks

user1359575
  • 99
  • 1
  • 3
  • 12

1 Answers1

1

What's the "DEFINER" of the Stored Procedure? Your user account might not have permissions from the second IP address. If you don't have the "SUPER" privilege, you can't specify any other value for "DEFINER" than your current user account, which if you're logging in from another IP address there's no guarantee that it's using the same account.

So try granting more permissions to the Stored Procedure.

Additionally, you might want to see the "SQL SECURITY INVOKER" option for MySQL. http://dev.mysql.com/doc/refman/5.0/en/stored-programs-security.html

darpified
  • 126
  • 3
  • You are right, the DEFINER value contains the previous IP address. Thank you. – user1359575 Apr 26 '12 at 20:33
  • Still it seems that I do not have the privileges to update the DEFINER. – user1359575 Apr 26 '12 at 20:36
  • Can you drop and recreate the Stored Procedure with more of a generic username, such as DEFINER='user'@'%'? – darpified Apr 26 '12 at 21:53
  • I had the same idea, but i'm really stuck. I dont have a backup from my latest changes, therefore the code. I've contacted the support team from my hosting service to update the mysql.proc table for me, but it seems they don't get the actual issue. – user1359575 Apr 27 '12 at 02:44
  • This thread http://stackoverflow.com/questions/3206820/mysql-change-the-stored-procedure-definer gives and example how to do it and I can do this locally but since it is a shared hosting I cant execute the query on the server. UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%' – user1359575 Apr 27 '12 at 02:47