How to automate SQL database creation upon new user creation

crinte

Member
Feb 17, 2010
7
0
51
I would like to set up whm so that whenever a user is created a certain database with tables will be installed automatically on to the users domain, how can that be done? I guess I have to write some code, but I don't know where..
 

sven4o

Member
May 21, 2007
19
0
151
New cPanel accounts can be created through the following cPanel script:

/scripts/wwwacct

Its most basic syntax is:

/scripts/wwwacct primarydomainname.com cpaneluser cpanelpassword

You can modify it to establish a mysql connection, create a new database and import the database tables in it after the account creation.
 

crinte

Member
Feb 17, 2010
7
0
51
Code:
#!/usr/bin/perl
# cpanel - wwwacct                                Copyright(c) 2008 cPanel, Inc.
#                                                           All rights Reserved.
# [email][email protected][/email]                                         [url=http://cpanel.net]cPanel Inc.[/url]
# This code is subject to the cPanel license. Unauthorized copying is prohibited

my $wwwacct = -x '/usr/local/cpanel/bin/wwwacct.pl' ? '/usr/local/cpanel/bin/wwwacct.pl' : '/usr/local/cpanel/bin/wwwacct';
exec $wwwacct, @ARGV;
Is in that file, what do I do with that?
Where do I find the syntax that I can use?
 

MattDees

Well-Known Member
Apr 29, 2005
416
1
243
Houston, TX
cPanel Access Level
Root Administrator
So, in order to do this, I would use a script hook by creating a shell script at

/scripts/postwwwacct

all information used for account creation is passed to this script as a hash. ( see: cPanel/WHM Script Hooks for more information)

In conjunction with this, I would use the Mysql::adddb ( ApiMysql < ApiDocs < TWiki )call to create the database via the attached shell script.
 

Attachments

crinte

Member
Feb 17, 2010
7
0
51
Step by step

Is it possible to describe it step by step and in more detial, I'm new to WHM server administration.
 

cPanelDavidN

Well-Known Member
Staff member
Dec 17, 2009
571
3
68
Houston, TX
cPanel Access Level
Root Administrator
a little more verbose

Crinte,

I'd do like cPanelMatt has suggested (and provided example script for).

The idea is that immediately after a new user is created, the backend code looks for a shell script called postwwwacct in the /scripts directory. This pre/post shell script hook action is made into WHM and is documented here:
cPanel/WHM Script Hooks

By utilizing this hook, you could automate your database creation. You could write your own db creation script that uses the MySQL CLI (or other similar methods), however it's easier and more stable to use a cPanel/WHM API query.

ApiMysql < ApiDocs < TWiki documents the database creation API1 call. cPanelMatt's file is an example of a postwwwacct script that does just that: gets the arguments (as passed to it by the backend code) and formats a query string, then performs the query on the localhost (using it's own hash access).

Cheers,
-David
 

KoiHosting

Registered
Apr 21, 2010
3
0
51
Is it possible to Hire somebody to do this?

After having read this thread with great interest, I would gladly pay a developer who can initiate this on our server.

Specifics for what I am looking for:

USERNAME = the Account Username created during Account Setup/creation
PASSWORD = the Account Password created during Account Setup/creation

All new accounts created will have a database with the name:
_subsite

So when the account is created the database will be named:

USERNAME_subsite

Then it will need a Master User Set up

_aDm1N

Which will become:

USERNAME_aDm1N

and the password will be the same as the account password which was entered when the account was created in WHM.
 

crinte

Member
Feb 17, 2010
7
0
51
I got it sorted out!

Modified a little bit to create user and add it to the database.
NB! Make sure that postwwwacct has 755 permissions, otherwise it won't start at all
Code:
#!/usr/bin/perl

##
# This script is for making queries against WHM for testing purposes.
# it uses /root/.accesshash for grabbing authentication information
##

use strict;
use warnings;

use LWP::UserAgent;
use MIME::Base64;

#convert input data to hash
my %OPTS = @ARGV;
my $dbname = "db";
my $dbuser = int(rand(10000));
my $dbpassword = int(rand(100000));
my $perms = "select insert update delete create";
my $create_db = '/xml-api/cpanel?user=' . $OPTS{'user'} . '&cpanel_xmlapi_apiversion=1&cpanel_xmlapi_module=Mysql&cpanel_xmlapi_func=adddb&arg-0=' . $dbname;
my $create_db_user = '/xml-api/cpanel?user=' . $OPTS{'user'} . '&cpanel_xmlapi_apiversion=1&cpanel_xmlapi_module=Mysql&cpanel_xmlapi_func=adduser&arg-0=' . $dbuser . '&arg-1=' . $dbpassword;
my $add_db_user = '/xml-api/cpanel?user=' . $OPTS{'user'} . '&cpanel_xmlapi_apiversion=1&cpanel_xmlapi_module=Mysql&cpanel_xmlapi_func=adduserdb&arg-0=' . $dbname . '&arg-1=' . $dbuser . '&arg-2=' . $perms;

query($create_db);
query($create_db_user);
query($add_db_user);

sub query {
	my ($call) = @_;
	
	# grab hash data
	my $accesshash;
	open(my $hash_fh, "<", "/root/.accesshash");
	while (my $line = readline($hash_fh ) ) {
		$accesshash .= $line;
	}
	close ($hash_fh);
	
	# clean of new lines
	$accesshash =~ s/\n//g;
	# create auth string
	my $auth = "WHM root:$accesshash";
	
	#setup query object
	my $ua = LWP::UserAgent->new;

	# perform query
	my $res = $ua->get("http://127.0.0.1:2086" . $call, Authorization =>$auth);

	# print response from query
	print $res->content;
}
Thanks cPanelMatt!
 

asuservice

Well-Known Member
Oct 24, 2001
83
0
306
Hi,
Thanks to crinte for posting this. I have added it to my postwwwacct and everything works great except setting the privileges.

When I check the domain's cpanel nothing is set. I checked /usr/local/cpanel/logs/error_log and found "mysqladmin: You do not have access to that database " which is what WHM also spits out during the account creation.

I understand what the error is but I'm not sure how to properly auth the function when calling it. I assume the line of my $auth = "WHM root:$accesshash"; should do that.

Any ideas? I'm using WHM 11.28.60

Thanks in advance!
 
Last edited:

cPanelDavidN

Well-Known Member
Staff member
Dec 17, 2009
571
3
68
Houston, TX
cPanel Access Level
Root Administrator
Hi Mike,

With you using 11.28.60 (and reviewing crinte's code), you're likely hitting a bug. There's a whole thread on the issue and a work around here: http://forums.cpanel.net/f42/case-4...ing-created-user-created-database-182011.html

You should see RELEASE and STABLE release tiers with the bug patched fairly soon. If you're updating based on the EDGE or CURRENT release tiers, then the fix is already there (they are at 11.28.73 as of this post).

That being said, I'll advocate here (as I did in the other thread I linked above) to do the "work around" in your script. ie, alter your code to use literal values, that include a prefix (if prefixing is "on" [the default]), for the $dbname and $dbuser when performing the "adduserdb" call.

Regards,
-DavidN