Cpanel MySQL Create/Drop database remotely

Question

Using cpanel, I was able to create a FTP user with limited access to allow our technical support team to upload files to the server.

Now, I'm kind of stuck since they also need to be able run script to create and populate databases each time we want to create a new subdomain, but it seems that we can only create databases via the cpanel.

Is there a way to remotely create databases (and grant all the access rights owned by that user to the database)?

Note: I'm hosting my project on HostGator (https://www.hostgator.com/)

Answer 1

Note: You'll need to ask your host for an SSH access and a root access to the database to apply the following solution.

Note 2: Replace "gemhr" in the following tutorial by your own domain.

1. Create two accounts (one used for the installation and one that will be assigned to the database and used in your server code, by example PHP).

2. You will need to add the ip address in the whitelist (you need a static ip address to do so).

3. Access SSH with putting and open MySQL. Then run the following queries.

Add full right the the database starting by "gemhr_" (use your domain name here) to the installer account:

GRANT ALL PRIVILEGES ON `gemhr\_%`.* TO 'gemhr_installer'@'modemcable134.79-70-69.static.videotron.ca'; 

Give the right to execute stored procedure on the table "mysql" to the installer account:

GRANT EXECUTE ON mysql.* TO 'gemhr_installer'@'modemcable134.79-70-69.static.videotron.ca';

The following stored procedure must be ran after creating or deleting a database. It will grant or revoke the access to the account used in PHPMyAdmin and to the support account use by the server code (like PHP).

Change the "gemhr" for your domain name and change the access rights of "gemhr_support" based on what you need it to do in your server code.

USE mysql;

DELIMITER //

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `manageSubDomain`
(
    sdName VARCHAR(50),
    created BOOLEAN
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK; 
        RESIGNAL;
    END;
    
    IF ((SELECT 
            CASE WHEN sdName REGEXP '^gemhr_[A-Za-z0-9_]+$'
            THEN TRUE
            ELSE FALSE END
        ) = TRUE AND 
        IFNULL(
            (
                SELECT `SCHEMA_NAME`
                FROM `information_schema`.`SCHEMATA` 
                WHERE `SCHEMA_NAME` = (sdName COLLATE utf8_unicode_ci)
            ),
            ''
        ) <> ''
    ) THEN
        
        SET sdName = INSERT(
            sdName, 
            INSTR(sdName, '_'),
            1, 
            '\_'
        );
        
        START TRANSACTION;
        
        IF (created = true) THEN
            
            SET @query = CONCAT(
                'GRANT ALL PRIVILEGES ON `', 
                sdName, '`.* TO ''gemhr''@''localhost'''
            );
        ELSE
            SET @query = CONCAT(
                'REVOKE ALL PRIVILEGES ON `', 
                sdName, '`.* FROM ''gemhr''@''localhost'''
            );
        END IF;
        
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        IF (created = true) THEN
            
            SET @query = CONCAT(
                'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER  ON `', 
                sdName, 
                '`.* TO ''gemhr_support''@''localhost'''
            );
        ELSE
            SET @query = CONCAT(
                'REVOKE ALL PRIVILEGES ON `', 
                sdName, 
                '`.* FROM ''gemhr_support''@''localhost'''
            );
        END IF;
        
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET @query = REPLACE(
            @query,
            'localhost',
            'modemcable134.79-70-69.static.videotron.ca'
        );
        
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        COMMIT;
        
        SET @query = '';
    ELSE    
        
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'INVALID_SUBDOMAIN_NAME';
    END IF;
END //

DELIMITER ;

To be on the safe side, you should also grant access to all your sub-domain database remotely and for localhost.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `gemhr\_%`.* TO 'gemhr_support'@'localhost';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `gemhr\_%`.* TO 'gemhr_support'@'modemcable134.79-70-69.static.videotron.ca';

4. Create a cron job to refresh the database mapping (otherwise your database will be visible in the PHPMyAdmin, but not in Cpanel). The cron job didn't seem to work when executed by my cpanel user, so I created it directly in SSH.

Use this command to open the crontab editor:

crontab -e

Use this command to refresh the database mapping once per hour:

0 * * * * /usr/local/cpanel/scripts/rebuild_dbmap gemhr

CTRL + X to save.

5. Log in MySQL workbench with your installer to test that everything is alright:

    CREATE DATABASE IF NOT EXISTS `gemhr_test123`;
   
    USE mysql;
   
    CALL `manageSubDomain`('gemhr_test123', true);
   
    -- CALL `manageSubDomain`('gemhr_test123', false);
   
    -- DROP DATABASE IF EXISTS `gemhr_test123`;
   

Here's the expected result (you might want to set your cron job every minute for testing to quickly see the result in cpanel):

From there, I suggest to create some coding to completely hide the SQL creation to your tech. team behind a nice web interface.

Use that coding if you ever want to revert the changes presented in this post:

REVOKE ALL PRIVILEGES ON `gemhr\_%`.* from 'gemhr_installer'@'modemcable134.79-70-69.static.videotron.ca';

REVOKE ALL PRIVILEGES ON `mysql`.* from 'gemhr_installer'@'modemcable134.79-70-69.static.videotron.ca';

REVOKE ALL PRIVILEGES ON `gemhr\_%`.* from 'gemhr_support'@'modemcable134.79-70-69.static.videotron.ca';

REVOKE ALL PRIVILEGES ON `gemhr\_%`.* from 'gemhr_support'@'localhost';

USE mysql;

DROP PROCEDURE IF EXISTS `manageSubDomain`;