The new update to cPanel 11.28.X has removed the admin’s ability to directly interact with certain functionality from the command line using standard bash commands. The idea is that cPanel wants admins to interact with the API or do everything through the WHM GUI. 
I am not about to load up WHM to perform simple task, so I have written scripts for the most common CLI tasks to interact with the cPanel XML API. The first one is an update to my dbuser script.
This script will create the following:
MySQL database
MySQL database user
The important part is this: The script will map the database and database user to a cPanel account.
Even more important?: The script will map the database user to the database. This is the only way to perform this final step outside of the cPanel GUI.
#Written By Brendan Clemmer
#only way to properly create and assign a MySQL db+user since the cPanel 11.28 debacle
use strict;
use LWP::UserAgent;
BEGIN {push (@INC,"/usr/local/cpanel");}
my($whm) = Cpanel::Accounting->new;
my $file = '/root/.accesshash';
open(FILE, $file);
my @lines = ;
close(FILE);
use Cpanel::Accounting;
use Term::ANSIColor;
use Cwd;
my $dir = getcwd;
my $UN = `echo $dir|cut -d/ -f3`;
$UN =~ s/\s//g;
my $hash = "@lines";
$hash =~ s/\s//g;
my $auth = "WHM root:" . $hash;
$whm->{host} = "localhost";
$whm->{user} = "root";
$whm->{accesshash} = $hash;
if ($#ARGV != 2) {
print color 'green';
print "usage: perl <(GET yourscriptdomain.com/dbuser) dbname mysqluser password\n";
print color 'reset';
exit;
}
my $user = $UN; # cPanel username
my $DBNAME = $ARGV[0]; # database name
my $DBUSER = $ARGV[1]; # database username
my $DBPASS = $ARGV[2]; # database password
my $module = 'Mysql';
my $DBUHmodule = 'adduser';
my $DBAUHmodule = 'adduserdb';
my $DBAUHperms = 'alter temporary routine create delete drop select insert update references index lock all';
my $DBHmodule = 'adddb';
my $DBUHmodule = 'adduser';
my $checkpath = '/var/cpanel/users/'.$UN;
unless (-e $checkpath && -f $checkpath) {
print color 'red';
print "You need to run this from the user's home. Try Again.\n";
print color 'reset';
exit;
}
if ($DBNAME =~ /\_/){
print color 'red';
print "Don't append the cPanel username to the database name. Try again.\n";
print color 'reset';
exit;
}
if ($DBUSER =~ /\_/){
print color 'red';
print "Don't append the cPanel username to the MySQL username. Try again.\n";
print color 'reset';
exit;
}
my $length = '7';
my $checklength = length($DBUSER);
unless ($checklength < $length) { print color 'red'; print "Your MySQL username is too long. There are limits. Try again.\n"; print color 'reset'; exit; } my $version = $whm->showversion();
if ($whm->{error} ne "") {
print "There was an error while processing your request: Cpanel::Accounting returned [$whm->{error}]\n";
exit;
}
print "cPanel version: $version";
print "\n";
my $dbhack = $whm->api1( $user, $module, $DBHmodule, $DBNAME );
if ($whm->{error} ne "") {
print "There was an error while processing your request: Cpanel::Accounting returned [$whm->{error}]\n";
exit;
}
my $dbuserhack = $whm->api1( $user, $module, $DBUHmodule, $DBUSER, $DBPASS );
if ($whm->{error} ne "") {
print "There was an error while processing your request: Cpanel::Accounting returned [$whm->{error}]\n";
exit;
}
my $dbadduserhack = $whm->api1( $user, $module, $DBAUHmodule, $DBNAME, $DBUSER, $DBAUHperms );
if ($whm->{error} ne "") {
print "There was an error while processing your request: Cpanel::Accounting returned [$whm->{error}]\n";
exit;
}
print color 'green';
print "SUCCESS::We created the user $user\_$DBUSER on $user\_$DBNAME using the password $DBPASS";
print color 'reset';
print "\n";
exit;
EDIT: cPanel has made another change. They have decided to remove the functionality of the cPanel Accounting perl module in 11.28.64 making this script not work properly. I do have an update, but will not be posting it here. If you are interested maybe we can work something out $$, contact me.
Ever try to add a database user from the command line and it doesn’t show up in a user’s cPanel account?
The following bit of code can be used to create a database, database user, and assign the same privileges that would have been assigned if the user were created in the cPanel GUI. Once the script has been run the output will let you know the name of the database, database user, and the password assigned.
If you run the script without any command line arguments then the usage instructions will be printed on screen.
The basic instructions are to run the script from inside the users account ( /home/username/* ) and supply the database name, database username, and password. Do not add the cPanel username or the script will error out.
EDIT:: This is now outdated with the 11.28 Patch. Please see this new post for an updated script.
#dbuser.sh
# Database and database user creation script
# by Brendan Clemmer
# This script will create a database if it does not exist, create a user, and assign that user to the database.
EXPECTED_ARGS=3
E_BADARGS=65
SQLBASE=$1
SQLUSER=$2
SQLPASS=$3
user_check(){
if [ ! -f /var/cpanel/users/$UN ]; then
echo -e "\E[0;31mScript dying, you need to run this from the user's home";tput sgr0
exit
fi
create_database(){
if [[ $SQLBASE == "$UN"_* ]];
then
echo -e "\E[0;31mOnly enter the database name, not the cPanel username prefix, try again. ";tput sgr0
exit 2
else
mysql -e "create database if not exists ${UN}_${SQLBASE}"
fi
}
create_user(){
if [[ $SQLUSER == "$UN"_* ]];
then
echo -e "\E[0;31mOnly enter the database username, not the cPanel username prefix, try again. ";tput sgr0
exit 2
else
mysql -Be "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE ON \`"$UN"\\_"$SQLBASE"\`.* TO '"$UN"_"$SQLUSER"'@'localhost' IDENTIFIED BY '$SQLPASS';FLUSH PRIVILEGES"
status=$?
fi
}
quit(){
if test $status -eq 0
then
printf "\E[0;32mSUCCESS::We created the user ${UN}_${SQLUSER} on ${UN}_${SQLBASE} with the password $SQLPASS.\n";tput sgr0
exit 2
else
echo -e "\E[0;31mHoly CARP! you messed up! MySQL error listed above.";tput sgr0
exit 2
fi
}
if [ $# -ne $EXPECTED_ARGS ]
then
echo -e '\E[0;31mUsage: ./dbuser.sh database user dbpass';tput sgr0
echo -e '\E[0;32mRun this script from the users home directory.'
echo -e '\E[0;32mThe script will place the username and underscore prefix'
echo -e '\E[0;32min the appropriate place for the db name and user name.';tput sgr0
exit $E_BADARGS
fi
create_database
create_user
quit
