unable to create a database from mysql command line

coffeeboyuk

Active Member
Nov 12, 2005
43
3
158
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

cP Technical Analyst II
Staff member
May 15, 2017
258
30
103
America
cPanel Access Level
Root Administrator

coffeeboyuk

Active Member
Nov 12, 2005
43
3
158
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

cP Technical Analyst II
Staff member
May 15, 2017
258
30
103
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 [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

Active Member
Nov 12, 2005
43
3
158
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

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
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.

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

Active Member
Nov 12, 2005
43
3
158
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

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463

coffeeboyuk

Active Member
Nov 12, 2005
43
3
158
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
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463

coffeeboyuk

Active Member
Nov 12, 2005
43
3
158
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

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
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

Active Member
Nov 12, 2005
43
3
158
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

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
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

Active Member
Nov 12, 2005
43
3
158
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

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
Hello,

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

Thank you.