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 cPanel Created MySQL Users Can't Show Databases

Discussion in 'Database Discussion' started by GOT, Dec 23, 2018.

  1. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,484
    Likes Received:
    187
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    Some of my clients who use mysql command line and remote tools have noticed this and I'm not sure when this started or with what MySQL version, but when you create a mysql user in cPanel, like user_mydbuser, that this user no longer has the show databases grants. Not even on the databases that the user has been assigned to.

    It looks like this is intended as we see it on new deployments where we roll out generally MariaDB 10.2.

    Is there a cPanel sanctioned way to get this right granted to them? In some cases I have simply modified the grants by hand, but this gets to be a bit of a pain.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,529
    Likes Received:
    2,181
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hi @GOT,

    We do have an internal case open where the "MySQL Database Wizard" option doesn't properly assign all privileges to a MySQL user when ALL PRIVILEGES is selected, however the case shows this only affects cPanel & WHM version 78.

    Can you let me know the output from the following commands on an affected system?

    Code:
    cat /usr/local/cpanel/version
    mysql --version
    Also, what are the full step-by-step instructions I should follow to reproduce this behavior on a test system?

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,484
    Likes Received:
    187
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    [[email protected] ~]# cat /usr/local/cpanel/version
    11.76.0.14

    [[email protected] ~]# mysql --version
    mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper

    I've got two servers I can confirm this is a problem on. I do not use the wizard. I create a database, then create a user, then add that user to the database using the all privs, then try to access the database using MySQL command line:

    [[email protected] ~]# mysql -u fitpc2_utest -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 14451
    Server version: 5.7.24 MySQL Community Server (GPL)

    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> show databases;
    ERROR 1227 (42000): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation
    mysql>


    Have not tested this wide, but like I said, at least two servers, both running latest release cpanel. One server running mysql 5.7 and the other running MariaDB 10.2. One server was just rolled out a couple weeks ago.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,529
    Likes Received:
    2,181
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @GOT,

    I've been unable to reproduce this behavior on a test environment upon performing the same steps you provided. EX:

    Code:
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | cptest01_test1     |
    +--------------------+
    2 rows in set (0.00 sec)
    Can you open a support ticket so we can take a closer look at an affected system to see what's happening? You can post the ticket number here and I'll link this thread to it.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,484
    Likes Received:
    187
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    I created a ticket. ID: 11053755
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,484
    Likes Received:
    187
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    Just in case anyone comes across this, the problem was in a my.cnf template we were using and I did not realize it had skip-show-databases in it. That is what was causing the problem.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,529
    Likes Received:
    2,181
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hi @GOT,

    I'm glad to see the issue was solved. Thank for you taking the time to share the outcome with us.
     
    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