adduser-proc.sql

-- adduser-proc.sql
-- Alexandre Gauthier 2015-2016

-- Stored Procedure to create new virtual users in the mail server database.
-- Serves as a better interface than inserting rows and hoping to hash the
-- password right.

use mailserver;

DROP PROCEDURE IF EXISTS AddMailUser;

DELIMITER $$
CREATE PROCEDURE AddMailUser(
    IN puser VARCHAR(250),
    IN ppassword VARCHAR(250),
    IN pdomain VARCHAR(250),
    IN penabled TINYINT(1)
)

BEGIN
    DECLARE vdomainid INT;         -- Foreign domain key
    DECLARE passhash VARCHAR(250); -- password hash

    -- Generic Exception Handler
    -- DECLARE EXIT HANDLER FOR SQLEXCEPTION
    -- BEGIN
        -- ROLLBACK;
        -- SELECT 'An error occured during User Creation. Transaction Rolled Back.';
    -- END;

    -- Get foreign domain key
    SELECT id INTO vdomainid FROM domains WHERE name=pdomain AND enabled='1';

    -- Sanity checks
    IF vdomainid IS NULL THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = "The specified domain does not exist.";
    END IF;

    -- Hash and salt provided password.
    -- ENCRYPT() is basically unix crypt(), uses SHA-512 hashes.
    SET passhash = 
        ENCRYPT(ppassword, CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)));

    -- Insert Record, primary key is autoincremented.
    INSERT INTO users(domain_id, password, email, enabled)
    VALUES (vdomainid, passhash, puser, penabled);

END $$
DELIMITER ;