Cannot create new mysql user and grant access using shell access

[itconstruct]

I have a cPanel reseller account and I do alot of web development using mysql database and my host gives me shell access to mysql so that I can sql queries however I have come across a particular issue of late that I haven't been able to get past and would like some assistance on.

When I create new databases I will create a new mysql user and grant them access to this new database for security reasons rather than using the main user account for my reseller account which is a major security risk as this password is stored in plain text for some systems to connect to the databases.

I have been able to create the new databases ok using sql queries however when I then try and create the user and grant privileges I am unable to do so. (I have omitted the code for creating a new database as it works).

mysql> CREATE USER 'username_dbname'@'localhost' IDENTIFIED BY '******'; 
GRANT ALL PRIVILEGES ON username_user TO 'username_dbname'@'localhost' WITH GRANT OPTION;

However I get an error that I don't have permission to create the user:

Error 
SQL query:

CREATE USER 'user_username'@'localhost' IDENTIFIED BY '******'; 
MySQL said: Documentation

#1227 - Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

ERROR 1142 (42000): GRANT command denied to user 'username_user' for table 'username_dbname' 
mysql>

I had raised a ticket with my WebHost however after investigating the issue with them they suggested I should come here to seek further assistance regarding this.

Even after they tried to run the commands as root using the sql queries I still couldn't use the mysql account correctly and it had to be recreated manually through the cPanel console which is cumbersome and time consuming when creating multiple databases due to issues with the privileges for the user.

Can someone please help me regarding this issue. I'm not sure if this is currently a bug/feature request or something that cPanel can do already but perhaps my webhost may not be aware of what to action in order for it work properly.

Any help will be greatly appreciated.

 

[cPanelMichael]

Hello

You will not be able to assign the user to the database through the command line because access to /usr/local/cpanel/bin/dbmaptool is restricted to the "root" user. You would have to utilize our API to grant a database user permissions to a database, as referenced in the following post:

Database User Permissions

Thank you.