The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

db user missing from Privileges column

Discussion in 'Database Discussions' started by Adam Fortman, Sep 15, 2015.

  1. Adam Fortman

    Adam Fortman Registered

    Joined:
    Jul 24, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Michigan
    cPanel Access Level:
    Root Administrator
    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!
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You may find the database mapping tool helpful:

    The dbmaptool script

    For example, if you wish to grant ownership of the db1 MySQL® database with the virt1 database user to the user1 cPanel user, run the following command:

    Code:
    /usr/local/cpanel/bin/dbmaptool user1 --mysql --dbs 'db1'--dbusers 'virt1'
    Thank you.
     
  3. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
    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.

    > https://forums.cpanel.net/threads/mysql-add-user-to-database.162518/#post-680630

    He provides API examples, which if I've dug far enough are now completely deprecated.

    Having to create dozens of DBs/DBUsers through the UI is painful :(

    I'm looking for the proper cPanel command line way to do these normal command line MySQL activities:

    Code:
    mysql -u root -p'rootspassword' mysql -e "DROP DATABASE IF EXISTS $DBase;CREATE DATABASE $DBase;"
    mysql -u root -p'rootspassword' mysql -e "DROP USER '$DBUser'@'localhost';FLUSH PRIVILEGES;FLUSH TABLES"
    mysql -u root -p'rootspassword' mysql -e "CREATE USER '$DBUser'@'localhost' IDENTIFIED BY '$DBPass';"
    mysql -u root -p'rootspassword' mysql -e "GRANT ALL PRIVILEGES ON $DBase.* TO $DBUser IDENTIFIED BY '$DBPass';FLUSH PRIVILEGES;"
    
    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:

    Code:
    mysqldump --opt –user=act001_db001 --password=abcde --host=localhost act001_dbu01 > ...
    So, is proper syntax:

    Code:
    /usr/local/cpanel/bin/dbmaptool act001 --mysql --dbs 'act001_db001' --dbusers 'act001_dbu01'
    or

    Code:
    /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.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You can utilize the following document:

    Guide to UAPI

    There's a section for MySQL that explains how to create database and database users.

    Thank you.
     
  5. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
    Unfortunately that document has errors and it's examples don't run.

    Same results running as root or account owner.
    Code:
    # php -f /usr/local/cpanel/base/frontend/x3/test.live.php
    <html>
    <pre>
    Fatal 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
    
    Code:
    #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 Theme
    Code:
    Currently 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/dbmaptool

    Code:
    sub 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.
     
    #5 Michael-Inet, Sep 17, 2015
    Last edited: Sep 17, 2015
  6. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
    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
     
  7. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
    #7 Michael-Inet, Sep 17, 2015
    Last edited by a moderator: Sep 18, 2015
  8. Adam Fortman

    Adam Fortman Registered

    Joined:
    Jul 24, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Michigan
    cPanel Access Level:
    Root Administrator
    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!
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I'm not sure I understand your previous response about the UAPI functions. For instance, you can try using the Perl example found at:

    UAPI Functions - Mysql::create_database

    Thank you.
     
  10. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
     
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  12. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
  13. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page