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!

unable to create a database from mysql command line

Discussion in 'Database Discussion' started by coffeeboyuk, Nov 16, 2017.

  1. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Hi,

    I'm new to MySQL. I added a database user in MySQL CPANEL but when I log into SSH shell and use the command line of MySQL, I notice I can't create databases.

    At the MySQL command prompt, I type:

    create database test1;

    It output the following error message:
    ERROR 1044 (42000): Access denied for user 'xxxxx_john'@'localhost' to database 'test1'

    Do I have to create all databases via CPANEL?

    Leon
     
  2. cPWilliamL

    cPWilliamL cP Technical Analyst II
    Staff Member

    Joined:
    May 15, 2017
    Messages:
    258
    Likes Received:
    29
    Trophy Points:
    103
    Location:
    America
    cPanel Access Level:
    Root Administrator
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Hi William,

    I got some questions:

    1. It looks like the uapi is to be used on the shell command line, but what if I wanted to log into mysql command line and create a database from there, will CPANEL still see the databases?

    2. I notice in the cpanel documentation it said, "If database prefixing is enabled, you must prefix this value with the account prefix and an underscore(_)." Prefix is enabled by default so I went and created a database with an example database name such as: cpanelusername_testdatabase1 in mysql command line and cpanel doesn't see the database, what is wrong?

    3. Few days ago I "GRANT ALL PRIVILEGES" to my CPANEL username account using root, so I can practice my SQL skills by creating databases and tables, is that a security risk? If it is how do I revert the privileges for that cpanel username back to its default state or can I just leave it?

    Leon
     
  4. cPWilliamL

    cPWilliamL cP Technical Analyst II
    Staff Member

    Joined:
    May 15, 2017
    Messages:
    258
    Likes Received:
    29
    Trophy Points:
    103
    Location:
    America
    cPanel Access Level:
    Root Administrator
    1) cPanel would be unaware of the database and would not manage it for you.

    2) The databases must be created through cPanel. If you have already created databases, you can remap them to the cPanel account via WHM > SQL Services > Database Map Tool, or via the command line tool '/usr/local/cpanel/bin/dbmaptool'(pass '--help' for usage).

    3) It sounds that way. You can confirm as below(please redact your password string if you feel the need to post the output here):
    Code:
    # mysql -e "show grants for blue@localhost"
    +-------------------------------------------------------------------------------------------------------------+
    | Grants for blue@localhost                                                                                   |
    +-------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'blue'@'localhost' IDENTIFIED BY PASSWORD '**PASSWORD REDACTED***' |
    | GRANT ALL PRIVILEGES ON `cptmpdb\_blue\_eD1imbzUyNvqgdo3`.* TO 'blue'@'localhost'                           |
    | GRANT ALL PRIVILEGES ON `blue\_db`.* TO 'blue'@'localhost'                                                  |
    | GRANT ALL PRIVILEGES ON `blue\_db1`.* TO 'blue'@'localhost'                                                 |
    +-------------------------------------------------------------------------------------------------------------+
    
    Here, we can see my cPanel username is 'blue', and we can see each database the user has access to. Do you see something like 'GRANT ALL PRIVILEGES ON *.*' ? If so, I would revoke those:
    Remove Permissions for a MySQL User on Linux via Command Line – Liquid Web Knowledge Base
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Hi,

    Thanks for that. Regarding the questions:


    2. I saw I had this tool, "Database Map" but didn't realized what it was used for, until now. I just mapped one of my databases to my username but as I understand, this database doesn't have the cpanel username prefix added to it example: cpanelusername_database. Will this cause a problem for CPANEL in the future?

    3. I just ran the following command line: mysql -e "show grants for my_cpanel_username@localhost" via root and the following output is shown:


    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for my_cpanel_username@localhost |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'REACTED'@'localhost' IDENTIFIED BY PASSWORD '**REDACTED**' |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    How do I remove the above privileges for that username? Do I run "GRANT ALL PRIVILEGES OFF"?

    Regards,

    Leon
     
  6. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,400
    Likes Received:
    1,953
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    It shouldn't cause any problems, however you could modify the "Require a username prefix on names of new databases and database users" option under the "SQL" tab in "WHM >> Tweak Settings" if you wanted to allow database names/database usernames without prefixes.

    The easier approach is to simply delete the MySQL user, and then add it back again using cPanel or the corresponding UAPI function:

    UAPI Functions - Mysql::create_user - Software Development Kit - cPanel Documentation

    Once you do that, add it back to the database using "cPanel >> MySQL Databases" or the following UAPI function:

    UAPI Functions - Mysql::set_privileges_on_database - Software Development Kit - cPanel Documentation

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Hi Michael,

    I don't think I can simply delete the username, the username is part of my domain username and it's not listed as a MySQL member for deletion. Any other ideas?

    Leon
     
  8. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,400
    Likes Received:
    1,953
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    You could use the instructions referenced on the link quoted earlier:

    Look for the "REVOKE" command in the example.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  9. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Hi Michael,

    I have found the revoke: REVOKE CREATE ON *.* FROM 'testuser'@'localhost';

    It looks like this command will remove all the permissions for that cpanel domain username. I just want to make sure I am returning the status of that username to its default privileges. Are there any access privileges that I need to be made aware of before I remove it?

    I tried to look at my other domain usernames earlier but cannot seem to display what privileges they have by default.

    Leon
     
  10. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Oppss.. that command should be: REVOKE ALL ON *.* FROM 'testuser'@'localhost';
     
  11. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,400
    Likes Received:
    1,953
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    You could revoke all privileges for that MySQL user, and then use the script referenced on the following document to reset them:

    How to Restore Database Grants - Documentation - cPanel Documentation

    Let us know if that helps.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  12. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Hi Michael,

    I don't need to restore access because I have other username that can see the databases.

    My main one concern is will it affect my cpanel?

    Leon
     
  13. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,400
    Likes Received:
    1,953
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    Yes, it could affect existing or future functionality if the default MySQL username associated with the cPanel account does not utilize the correct grants. I recommend revoking all privileges and then restoring them using the script referenced in my last response.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  14. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Hi,

    I justed checked my other domain cpanel usernames and they have no privileges. See the results listed below.

    Is it really necessary?

    Username 1. mysql> show grants for reacted@localhost;
    +-----------------------------------------------------------------------------+
    | Grants for reacted@localhost |
    +-----------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'reacted'@'localhost' IDENTIFIED BY PASSWORD <secret> |
    +-----------------------------------------------------------------------------+
    1 row in set (0.00 sec)



    Username 2. mysql> show grants for reacted@localhost;
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for reacted@localhost |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'reacted'@'localhost' IDENTIFIED BY PASSWORD '***********************************' |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)


    So, once I removed all privileges there are two commands of which I have listed below. Which one do I use? I get the feeling once I have restored it, all the privileges will get added back. Am I right? I'm at lost with this.

    1. /usr/local/cpanel/bin/restoregrants --cpuser=$cpuser --db={mysql, pg} --dbuser=$dbuser

    2. /usr/local/cpanel/bin/restoregrants --cpuser=$cpuser --db={mysql, pg} --dbuser=$dbuser
     
  15. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,400
    Likes Received:
    1,953
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    You'd use a command like this:

    Code:
    /usr/local/cpanel/bin/restoregrants --cpuser=cptest0123 --db=mysql --dbuser=cptest0123
    Replace "cptest0123" with the name of the cPanel account username.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  16. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    I have revoked all the privileges for my cpanel username via the mysql command line.

    I then went into command shell and issued the command to restore grants using root:

    /usr/local/cpanel/bin/restoregrants --cpuser=cptest0123 --db=mysql --dbuser=cptest0123 (where cptest0123 replaced with my cpanel username)

    Interesting it's assigned permission to all the databases I created for testing seen below. Is my server now secured now? And finally, is there anything else I need to revert?

    mysql> show grants for ******@localhost;
    +----------------------------------------------------------------------------+
    | Grants for ******@localhost |
    +----------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO '******'@'localhost' IDENTIFIED BY PASSWORD <secret> |
    | GRANT ALL PRIVILEGES ON `******\_database1`.* TO '******'@'localhost' |
    | GRANT ALL PRIVILEGES ON `******\_test123a`.* TO '******'@'localhost' |
    | GRANT ALL PRIVILEGES ON `******\_database2`.* TO '******'@'localhost' |
    | GRANT ALL PRIVILEGES ON `testdb`.* TO '******'@'localhost' |
    +----------------------------------------------------------------------------+
    5 rows in set (0.00 sec)
     
  17. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,400
    Likes Received:
    1,953
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    Yes, that looks correct and matches how it looks after creating new databases on an account. No further actions are required.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  18. coffeeboyuk

    coffeeboyuk Member

    Joined:
    Nov 12, 2005
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    153
    Cheers Michael, all the best and have a great festive season as this is the time for rejoicing.
     
    cPanelMichael likes this.
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