SOLVED cPanel Created MySQL Users Can't Show Databases

[GOT]

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.

 

[cPanelMichael]

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?

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.

 

[GOT]

[root@fpc ~]# cat /usr/local/cpanel/version
11.76.0.14

[root@fpc ~]# 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:

[root@fpc ~]# 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.

 

[cPanelMichael]

Hello @GOT,

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

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.

 

[GOT]

I created a ticket. ID: 11053755

 

[GOT]

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.

 

[cPanelMichael]

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.

Hi @GOT,

I'm glad to see the issue was solved. Thank for you taking the time to share the outcome with us.