Skip to main content

unable to create a database from mysql command line

coffeeboyuk
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

Comments

17 comments

Date Votes
  • cPWilliamL
    Hi Leon, For cPanel to properly manage and keep track of your database and users, they must be created through cPanel. You can still use the command line to do this: UAPI Functions - Mysql::create_database - Software Development Kit - cPanel Documentation
    0
  • coffeeboyuk
    Hi Leon, For cPanel to properly manage and keep track of your database and users, they must be created through cPanel. You can still use the command line to do this:
    0
  • cPWilliamL
    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):
    # 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
    0
  • coffeeboyuk
    ) 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):
    # 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

    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
    0
  • cPanelMichael
    . 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?

    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.
    How do I remove the above privileges for that username? Do I run "GRANT ALL PRIVILEGES OFF"?

    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.
    0
  • coffeeboyuk
    The easier approach is to simply delete the MySQL user, and then add it back again using cPanel or the corresponding UAPI function:

    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
    0
  • cPanelMichael
    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?

    Hello, You could use the instructions referenced on the link quoted earlier:
    If so, I would revoke those: Remove Permissions for a MySQL User on Linux via Command Line " Liquid Web Knowledge Base

    Look for the "REVOKE" command in the example. Thank you.
    0
  • coffeeboyuk
    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
    0
  • coffeeboyuk
    Oppss.. that command should be: REVOKE ALL ON *.* FROM 'testuser'@'localhost';
    0
  • cPanelMichael
    I tried to look at my other domain usernames earlier but cannot seem to what privileges they have by default.

    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.
    0
  • coffeeboyuk
    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
    0
  • cPanelMichael
    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.
    0
  • coffeeboyuk
    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 | +-----------------------------------------------------------------------------+ 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
    0
  • cPanelMichael
    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

    Hello, You'd use a command like this:
    /usr/local/cpanel/bin/restoregrants --cpuser=cptest0123 --db=mysql --dbuser=cptest0123
    Replace "cptest0123" with the name of the cPanel account username. Thank you.
    0
  • coffeeboyuk
    Hello, You'd use a command like this:
    /usr/local/cpanel/bin/restoregrants --cpuser=cptest0123 --db=mysql --dbuser=cptest0123
    Replace "cptest0123" with the name of the cPanel account username. Thank you.

    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 | | 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)
    0
  • cPanelMichael
    Hello, Yes, that looks correct and matches how it looks after creating new databases on an account. No further actions are required. Thank you.
    0
  • coffeeboyuk
    Hello, Yes, that looks correct and matches how it looks after creating new databases on an account. No further actions are required. Thank you.

    Cheers Michael, all the best and have a great festive season as this is the time for rejoicing.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post