Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

SOLVED Can't Access MySQL Procedures - Insufficient Privileges

Discussion in 'Database Discussion' started by jshwhitlow, Oct 24, 2017.

  1. jshwhitlow

    jshwhitlow Registered

    Joined:
    Oct 24, 2017
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Indiana
    cPanel Access Level:
    Root Administrator
    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 <secret>
    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?
     
  2. jshwhitlow

    jshwhitlow Registered

    Joined:
    Oct 24, 2017
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Indiana
    cPanel Access Level:
    Root Administrator
    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.
     
  3. jshwhitlow

    jshwhitlow Registered

    Joined:
    Oct 24, 2017
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Indiana
    cPanel Access Level:
    Root Administrator
    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.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,711
    Likes Received:
    1,791
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    I'm happy to see you were able to solve the issue. Thank you for taking the time to share the outcome.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice