The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to automate SQL database creation upon new user creation

Discussion in 'cPanel Developers' started by crinte, Feb 17, 2010.

  1. crinte

    crinte Member

    Joined:
    Feb 17, 2010
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    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..
     
  2. sven4o

    sven4o Member

    Joined:
    May 21, 2007
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    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.
     
  3. crinte

    crinte Member

    Joined:
    Feb 17, 2010
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    Code:
    #!/usr/bin/perl
    # cpanel - wwwacct                                Copyright(c) 2008 cPanel, Inc.
    #                                                           All rights Reserved.
    # [email]copyright@cpanel.net[/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?
     
  4. MattDees

    MattDees cPanel Product Owner
    Staff Member

    Joined:
    Apr 29, 2005
    Messages:
    417
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    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.
     

    Attached Files:

  5. crinte

    crinte Member

    Joined:
    Feb 17, 2010
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    Step by step

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

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    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
     
  7. KoiHosting

    KoiHosting Registered

    Joined:
    Apr 21, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    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.
     
  8. crinte

    crinte Member

    Joined:
    Feb 17, 2010
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    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!
     
  9. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
  10. asuservice

    asuservice Well-Known Member

    Joined:
    Oct 24, 2001
    Messages:
    85
    Likes Received:
    0
    Trophy Points:
    6
    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!
     
    #10 asuservice, Jan 20, 2011
    Last edited: Jan 20, 2011
  11. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    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
     
Loading...

Share This Page