Skip to main content

Can't Access MySQL Procedures - Insufficient Privileges

jshwhitlow
I suddenly am experiencing an issue where I cannot edit or access Stored Procedures on my Database. I checked privileges and got: GRANT USAGE ON *.* TO 'REDACTED'@'REDACTED' IDENTIFIED BY PASSWORD GRANT ALL PRIVILEGES ON 'REDACTED' TO 'REDACTED'@'REDACTED' I confirmed its the right database and correct IP. The IP has been added to the Remote MySQL section. (We recently moved offices) I'm not sure what the issue is but can see this issue has been brought up before: You don't have necessary privilege to create a routine I feel like this issue has to be related to a recent update. Any help?

Comments

3 comments

Date Votes
  • jshwhitlow
    UPDATE 1: It appears it may related to the DEFINER for all the stored procedures. Its the same user but a different IP that originally created the procedures and now we moved and have a different IP. I have thus far been unable to figure out how to alter the DEFINER. I don't believe the user I log into has SUPER privileges as cPanel was set up by my hosting provider. Does anyone know how to do this through the terminal? I have root access to my system.
    0
  • jshwhitlow
    SOLUTION: When moving offices, our external IP address changed. The Definer for stored procedures stores the user and external IP such as 'user@192.168.1.31', this means the definer must be changed if we move. (Although I'm curious how big organizations handle definers for people across multiple locations) In order to change the definer you need SUPER privileges. Normally you can change this through phpmyadmin, but if you have a cPanel / WHM setup like us, the Privileges tab is removed in favor of using the built in cPanel functionality that allows you to grant all privileges EXCEPT granting SUPER privileges. --> phpMyAdmin USERS tab gone --> Increase privileges for user account In order to grant them you need to login as the Root user for MySQL through terminal. However, once you achieve this, you can just you the terminal to change the definer without the need to make yourself SUPER privileges. By default, WHM sets a random character string for the MySQL root password, you can change it from WHM and then login and then you are good to go! --> MySQL Root Password - Documentation - cPanel Documentation I've added lots of links for my sources here as this took me most of the day to figure out, its ridiculous that this stuff is that hard to find.
    0
  • cPanelMichael
    Hello, I'm happy to see you were able to solve the issue. Thank you for taking the time to share the outcome.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post