db user missing from Privileges column
I'm almost done with a Wordpress cloning bash script that I've designed around being on a cPanel server. The last thing I cannot figure out is how to make the database user appear in the "Privileged Users" column within the MySQL Database section in cPanel. I understand I can just assign the user to the database in cPanel itself, but I want to know if it's possible to do this via command line.
I've got all the hosts for the database user:
hosts=$(mysql -e "select Host from mysql.user where User='$newDBUser';"|tail -n+2)
Then I loop through them and GRANT ALL on the database for that user/host:
for host in $HOSTS; do mysql -e "GRANT ALL ON \`$newDB\`.* TO '$newDBUser'@'$host' IDENTIFIED BY PASSWORD '$(mysql -e "SELECT Password from mysql.user WHERE Host='$host' AND User='$newDBUser';"|tail -n+2)'; FLUSH PRIVILEGES;"; done
After I do this and check for the Privileged User in cPanel, it is still blank, yet my site works. Obviously I'm missing something, but am not sure what. Any assistance to help resolve this would be great! Thank you for your time!
-
Hello :) You may find the database mapping tool helpful: /usr/local/cpanel/bin/dbmaptool user1 --mysql --dbs 'db1'--dbusers 'virt1'
Thank you.0 -
Hi Michael Not trying to hijack the thread, but this seems very related, and Adam's direct method may be harmful to his system? Back in 2010, David Neimeyer (cPanelDavidN), indicates using direct statements, and not the cPanel API, will basically fubar your system. >
The dbmaptool script does the last one (Yeah!). Although the docs do not make clear if the cPanel account name should or should not be pre-pended to the dbs or the dbusers. cPanel Account: act001 DB: act001_db001 DBUser: act001_dbu01 Regular Command line:mysqldump --opt "user=act001_db001 --password=abcde --host=localhost act001_dbu01 > ...
So, is proper syntax:/usr/local/cpanel/bin/dbmaptool act001 --mysql --dbs 'act001_db001' --dbusers 'act001_dbu01'
or/usr/local/cpanel/bin/dbmaptool act001 --mysql --dbs 'db001' --dbusers 'dbu01'
# # # But, I wasn't able to find anything else in the WebHost Manager" (WHM") User Guide for the others, nor did scanning /usr/local/cpanel/bin/ show any promising file names. Are there cPanel command line ways to create DBs and DBUsers, that doesn't create "serious misconfiguration" as per cPanelDavidN indicated? Thanks, Michael PS: If cPanel has dropped the abstraction layer between MySQL and cPanel account resources, those blog posts and threads need to be updated/closed/removed.0 -
Unfortunately that document has errors and it's examples don't run. An example script ships with cPanel & WHM in the /usr/local/cpanel/base/frontend/x3/test.live.php file.
Same results running as root or account owner.# php -f /usr/local/cpanel/base/frontend/x3/test.live.phpFatal error: Uncaught exception 'RuntimeException' with message 'There was a problem fetching the env variablecontaining the path to the socket' in /usr/local/cpanel/php/cpanel.php:146 Stack trace: #0 /usr/local/cpanel/base/frontend/x3/test.live.php(8): CPANEL->__construct() #1 {main} thrown in /usr/local/cpanel/php/cpanel.php on line 146
Save files to the /usr/local/base/frontend/theme/ directory, where theme is the cPanel theme, or create a symlink to the appropriate files.
#ll /usr/local/base/frontend ls: cannot access /usr/local/base/frontend: No such file or directory
Taking the correct path to be /usr/local/cpanel/base/frontend, and assuming we're talking about the WHM theme, I have no theme "x" to use. Brand new build, less than a week old, CENTOS 6.7 x86_64 standard " srv07 WHM 11.50.0 (build 30): WHM " Home " Themes " Change WHM ThemeCurrently selected theme: x Only one theme (x) is installed.
I'd guess that's suppose to be x3, but usr/local/cpanel/base/frontend/x3/test.live.php failed, so no? # # # What exactly does one need to do to get the usr/local/cpanel/base/frontend/x3/test.live.php test file to run? Which user(s) are acceptable to run it? What ENV does it need? What $PATH is required? Anything else? Thanks, Michael Edit #1 Personally I'd just rather have the 3 variations of /usr/local/cpanel/bin/dbmaptool needed to do this, do they exist? Edit #2 cat /usr/local/cpanel/bin/dbmaptoolsub usage { print "$0 cpuser --type mysql|pg --dbusers 'user1, user2' --dbs 'db1, db2'\n These flags are treated individually. This tool will not map a virtual user to a database. /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbs 'db1' --dbusers 'virt1' /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbs 'db1' && /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbusers 'virt1' The above two commands are analogous; result in cpuser 'user1' having privileges for 'db1' and having a virtual user named 'virt1.'\n";
> This tool will not map a virtual user to a database. Okay, so this didn't provide the 4th command, GRANT ALL PRIVILEGES like I thought.0 -
Hi Michael, Trying to weed out the extraneous issues :) Past history indicates we're both bad about going off on tangents. # # # Goal is for a non-destructive cPanel command line method for: 1) Drop and Create DATABASE 2) Drop and Create DATABASE-USER 3) GRANT ALL PRIVILEGES on DATABASE to DATABASE-USER 4) Map DATABASE and DATABASE-USER to CPANEL-USER If using the raw mysql commands, as you indicated Adam could do, doesn't create "serious misconfiguration," then is it acceptable to use the raw commands I posted above and afterword, if I use dbmaptool, will dbmaptool do ALL of the internal cPanel needed configuration(s) that the UI normally handles? Being that the "serious misconfiguration" statement is 5 years old, is it valid anymore beyond needing to map something to a cpanel user? Apologies for not asking it this way the first time. Best, Michael 0 -
PS: Your signature link to Migration Services no longer works. 0 -
Thank you all for the input! I've tried the dbmap tool a few times, but the "Privileged Users" field is still blank, and it typically tells me that the cPanel user already owns that database. I'm guessing there are some other files that get updated when you grant privileges via the GUI. I will definitely be checking out that Guide to UAPI, thank you cPanelMichael! 0 -
Unfortunately that document has errors and it's examples don't run.
0 -
Please feel free to open a support ticket so we can take a closer look. You can post the ticket number here so we can update this thread with the outcome. Thank you. 0 -
Support Request Id 7167791 0 -
Hello :) Here are some paraphrases referenced in the support ticket: 1. There are no current command line methods of setting database user privileges within our software. The database mapping tool does not pull any privileges information from the MySQL server. It simply adds databases and database users to a cPanel account. 2. You can not use the LiveAPI via the command line as mentioned in this thread: https://forums.cpanel.net/threads/liveapi-connections-to-cpsrvd-are-failing.281792 3. Some of the UAPI functions you were attempting to use are only available in cPanel version 11.52. It's currently only available in the "Current" and "Edge" build tiers. Feel free to update this thread with any additional findings with your custom script now that the support ticket is closed. Thank you. 0
Please sign in to leave a comment.
Comments
12 comments