How to automate SQL database creation upon new user creation

[crinte]

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]

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]

#!/usr/bin/perl
# cpanel - wwwacct                                Copyright(c) 2008 cPanel, Inc.
#                                                           All rights Reserved.
# copyright@cpanel.net                                         cPanel Inc.
# 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?

[cPanelMatt]

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.

[crinte]

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

[cPanelDavidN]

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]

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]

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

#!/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!

[cPanelDavidN]

A lot of what this thread discusses is covered in a post on the Integration Blog. It's geared towards PHP developers, so it differs in that aspect from the original first few posts of this thread.

Frequently Asked Scripts: "How do I automate XYZ after creating an account?" - cPanel Integration

Enjoy,
-DavidN

[asuservice]

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!

[cPanelDavidN]

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-45...se-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