IlohaMail - email address login

HOWTO: IlohaMail - email address login

Well, I did some tweaking to IlohaMail that allows me to login using email address instead of username. Here is the procedure:

1)
Setup a new MySQL username for accessing db_ispconfig database. For example, let's say it will be ilohamail. I did this step using phpmyadmin as root user. DO NOT MAKE ANY PRIVILEGES FOR ACCESSING ISPConfig's DATABASE YET!

2)
Add privileges to ilohamail user for certain ISPConfig tables and their fields:

Code:

GRANT SELECT ( `doc_id` , `user_username` , `user_emailalias` , `user_email`) ON `db_ispconfig`.`isp_isp_user` TO 'ilohamail'@'localhost';
GRANT SELECT ON `db_ispconfig`.`isp_dep` TO 'ilohamail'@'localhost';
GRANT SELECT ( `doc_id` , `doctype_id` , `web_domain`) ON `db_ispconfig`.`isp_isp_web` TO 'ilohamail'@'localhost'; 

This will enable IlohaMail access to only username - hostname parts of the tables and not any further info. If you named your database otherwise than db_ispconfig be sure to change that.

3)
Change ./conf/db_config.php and add following lines:

Code:

$DB_ISP_HOST="localhost";
$DB_ISP_USER="ilohamail";
$DB_ISP_NAME="db_ispconfig";
$DB_ISP_PASSWORD="<password you created in step 1>";

4)
Change ./conf/conf.php and add this:

Code:

$ISP=1

Put it somewhere into php code area (and not at the end of the file!)

5)
Copy include/idba.MySQL.inc into include/idba_isp.MySQL.inc

Code:

cp include/idba.MySQL.inc include/idba_isp.MySQL.inc

6)
Open include/idba_isp.MySQL.inc and replace all occurances:

$DB_HOST, $DB_USER, $DB_PASSWORD and $DB_NAME with
$DB_ISP_HOST, $DB_ISP_USER, $DB_ISP_PASSWORD, $DB_ISP_NAME

(they're the same only _ISP_ is added)
Leave $DB_PERSISTENT as it is.

7)
Open source/index.php.

Look for includes (lines 51+).
Add the following after include_once("../conf/login.php") :

Code:

// check if we're in ISP config mode
if ($ISP) {
    include_once("../conf/db_conf.php");
    include_once("../include/idba_isp.MySQL.inc");
}

Find this lines at around line 110:

Code:

        //attempt to initiate session
        if ($user && $password && $host){
                include("../include/icl.inc");
                $user_name = $user;

Change it into:

Code:

    //attempt to initiate session
    if ($user && $password && $host){
        include("../include/icl.inc");

        if (($ISP) && (strpos($user,'@')!==false)) {
            $db = new idba_isp_obj;
            // make connection to ISP config database. BE SURE YOU HAVE SECURED ACCESS TO THE DATABASE FIRST!
            if ($db->connect()) {
                $sql = "SELECT user.user_username "
                            ."FROM isp_isp_user user, isp_dep dep, isp_isp_web web "
                            ."WHERE user.doc_id=child_doc_id "
                            ." AND dep.child_doctype_id=1014 "
                            ." AND web.doc_id=dep.parent_doc_id "
                            ." AND (CONCAT(user.user_email,'@',web.web_domain)='$user')";
                $result = $db->query($sql);
                if (($result) && ($db->num_rows($result)>0)){
                    $user = $db->result($result, 0, "user_username");
                }
            }
        }

        $user_name = $user;

Looking thru my code I think this should be enough. You can try logging into your IlohaMail using your mail for login and your password. It should work with your primary email only - aliases wont work.

If it's not working for you or I forgot to write it down please let me know.

Regards,
necromncr