Create Database, User and set permissions via SSH

[vestigium]

How can I create a database user and set permissions through a SSH connection?

I connected to MySQL via SSH and created the database, but it does not appear in cPanel.
And to create a new user for the database is giving the error: ERROR 1227 (42000): Access denied; You need the CREATE USER privilege for this operation

I read something to the user/database appear in cPanel is necessary to relate the database created with the user via cPanel WHM via the Database Map Tool menu.
I also saw somewhere over run the script /usr/local/cpanel/bin/setupdbmap for him to show in cPanel, it did not work.

I discovered that even the version 11.28 of cPanel had a script to create users, but it was removed. Script: /scripts/mysqladduserdb

After all, it's possible do what I wish exclusively via SSH?

 

[cPanelMichael]

Hello

You must manually create the databases and database users through MySQL itself. You can then determine if it's assigned to an account using "/usr/local/cpanel/bin/dbmaptool". Here are the options for this utility:

# /usr/local/cpanel/bin/dbmaptool
/usr/local/cpanel/bin/dbmaptool cpuser --type mysql|pg --dbusers 'user1, user2' --dbs 'db1, db2'

           These flags are treated individually. This tool will not map a virtual user to a database.
           /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbs 'db1' --dbusers 'virt1'
           /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbs 'db1' && /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbusers 'virt1'
           The above two commands are analogous; result in cpuser 'user1' having privileges for 'db1' and having a virtual user named 'virt1.'

Thank you.

 

[vestigium]

Searching a bit more I ended up finding the cPanel API.
I saw that the v1 API functions are available to create both user and database: Mysql Module Documentation

Could do everything I needed by making an API request via curl the following urls:

// Create database
https://mydomain.com:2083/json-api/cpanel?user=CPANELUSER&cpanel_jsonapi_module=Mysql&cpanel_jsonapi_func=adddb&cpanel_jsonapi_apiversion=1&arg-0=DATABASE

// Create user
https://mydomain.com:2083/json-api/cpanel?user=CPANELUSER&cpanel_jsonapi_module=Mysql&cpanel_jsonapi_func=adduser&cpanel_jsonapi_apiversion=1&arg-0=DATABASEUSER&arg-1=DATABASEUSERPASSWORD

// Grant a user permission (all)
https://mydomain.com:2083/json-api/cpanel?user=CPANELUSER&cpanel_jsonapi_module=Mysql&cpanel_jsonapi_func=adduserdb&cpanel_jsonapi_apiversion=1&arg-0=DATABASE&arg-1=DATABASEUSER&arg-2=all

 

[cPanelMichael]

I am happy to see you were able to find a suitable solution. Thank you for updating us with the outcome.