unable to create a database from mysql command line

coffeeboyuk · Nov 16, 2017

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

cPWilliamL · Nov 17, 2017

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

coffeeboyuk · Nov 17, 2017

cPWilliamL said:
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

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

cPWilliamL · Nov 17, 2017

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 [email protected]"
+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| 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

coffeeboyuk · Nov 18, 2017

cPWilliamL said:
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 [email protected]" +-------------------------------------------------------------------------------------------------------------+ | Grants for [email protected] | +-------------------------------------------------------------------------------------------------------------+ | 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 [email protected]" via root and the following output is shown:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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

cPanelMichael · Nov 21, 2017

coffeeboyuk said:
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?

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.

coffeeboyuk said:
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.

coffeeboyuk · Nov 23, 2017

cPanelMichael said:
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

cPanelMichael · Nov 28, 2017

coffeeboyuk said:
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:

cPWilliamL said:
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.

coffeeboyuk · Nov 29, 2017

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

coffeeboyuk · Nov 29, 2017

Oppss.. that command should be: REVOKE ALL ON *.* FROM 'testuser'@'localhost';

cPanelMichael · Nov 29, 2017

coffeeboyuk said:
I tried to look at my other domain usernames earlier but cannot seem to display 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.

coffeeboyuk · Nov 29, 2017

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

cPanelMichael · Nov 30, 2017

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.

coffeeboyuk · Nov 30, 2017

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 [email protected];
+-----------------------------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'reacted'@'localhost' IDENTIFIED BY PASSWORD <secret> |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

Username 2. mysql> show grants for [email protected];
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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

cPanelMichael · Dec 4, 2017

coffeeboyuk said:
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:

Code:

/usr/local/cpanel/bin/restoregrants --cpuser=cptest0123 --db=mysql --dbuser=cptest0123

Replace "cptest0123" with the name of the cPanel account username.

Thank you.

coffeeboyuk · Dec 4, 2017

cPanelMichael said:
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.

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)

cPanelMichael · Dec 5, 2017

Hello,

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

Thank you.

coffeeboyuk · Dec 5, 2017

cPanelMichael said:
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.

Thread starter	Similar threads	Forum	Replies	Date
T	Unable to create databases?	Databases	1	Jul 17, 2018
M	Unable to create DB in Cpanel or phpmyadmin	Databases	12	Sep 1, 2016
W	Unable to create a database issue	Databases	5	May 15, 2016
T	Unable to create new MySQL Database in Cpanel or PHPmyAdmin	Databases	3	Jan 5, 2011
G	MySQL - unable to create new databases	Databases	3	Jan 19, 2009

Search

Search

unable to create a database from mysql command line

coffeeboyuk

Active Member

cPWilliamL

cP Technical Analyst II

coffeeboyuk

Active Member

cPWilliamL

cP Technical Analyst II

coffeeboyuk

Active Member

cPanelMichael

Administrator

coffeeboyuk

Active Member

cPanelMichael

Administrator

coffeeboyuk

Active Member

coffeeboyuk

Active Member

cPanelMichael

Administrator

coffeeboyuk

Active Member

cPanelMichael

Administrator

coffeeboyuk

Active Member

cPanelMichael

Administrator

coffeeboyuk

Active Member

cPanelMichael

Administrator

coffeeboyuk

Active Member