Implemented FS#1448 - one database user name and multiple databases Bugfi...

mcramer

2012-08-21 381520c8866a5f3be7e743e3ae16b6fb2988c495

Implemented  FS#1448 - one database user name and multiple databases 
Bugfix on db-Class (datalog Update)

 install/sql/incremental/upd_0039.sql

New file
@@ -0,0 +1,36 @@
-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `web_database_user`
--

CREATE TABLE IF NOT EXISTS `web_database_user` (
  `database_user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sys_userid` int(11) unsigned NOT NULL DEFAULT '0',
  `sys_groupid` int(11) unsigned NOT NULL DEFAULT '0',
  `sys_perm_user` varchar(5) DEFAULT NULL,
  `sys_perm_group` varchar(5) DEFAULT NULL,
  `sys_perm_other` varchar(5) DEFAULT NULL,
  `server_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `database_user` varchar(64) DEFAULT NULL,
  `database_password` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`database_user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

ALTER TABLE `web_database` ADD `database_user_id` INT( 11 ) UNSIGNED NULL DEFAULT NULL AFTER `database_password` ,
ADD `database_ro_user_id` INT( 11 ) UNSIGNED NULL DEFAULT NULL AFTER `database_user_id`,
ADD INDEX ( `database_user_id` ),
ADD INDEX ( `database_ro_user_id` ) ;

-- --------------------------------------------------------

UPDATE `web_database`, `web_database_user` SET `web_database`.`database_user_id` = `web_database_user`.`database_user_id` WHERE `web_database_user`.`database_user` = `web_database`.`database_user`;

-- --------------------------------------------------------

ALTER TABLE `web_database`
DROP `database_user`,
DROP `database_password`;


 install/sql/ispconfig3.sql

@@ -1616,19 +1616,39 @@
  `parent_domain_id` int(11) unsigned NOT NULL DEFAULT  '0',
  `type` varchar(16) NOT NULL DEFAULT 'y',
  `database_name` varchar(64) DEFAULT NULL,
  `database_user` varchar(64) DEFAULT NULL,
  `database_password` varchar(64) DEFAULT NULL,
  `database_user_id` int(11) unsigned DEFAULT NULL,
  `database_ro_user_id` int(11) unsigned DEFAULT NULL,
  `database_charset` varchar(64) DEFAULT NULL,
  `remote_access` enum('n','y') NOT NULL DEFAULT 'y',
  `remote_ips` text NOT NULL,
  `backup_interval` VARCHAR( 255 ) NOT NULL DEFAULT 'none',
  `backup_copies` INT NOT NULL DEFAULT '1',
  `active` enum('n','y') NOT NULL DEFAULT 'y',
  PRIMARY KEY (`database_id`)
  PRIMARY KEY (`database_id`),
  KEY `database_user_id` (`database_user_id`),
  KEY `database_ro_user_id` (`database_ro_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `web_database_user`
--

CREATE TABLE IF NOT EXISTS `web_database_user` (
  `database_user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sys_userid` int(11) unsigned NOT NULL DEFAULT '0',
  `sys_groupid` int(11) unsigned NOT NULL DEFAULT '0',
  `sys_perm_user` varchar(5) DEFAULT NULL,
  `sys_perm_group` varchar(5) DEFAULT NULL,
  `sys_perm_other` varchar(5) DEFAULT NULL,
  `database_user` varchar(64) DEFAULT NULL,
  `database_password` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`database_user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table  `web_domain`
-- 

 interface/lib/classes/db_mysql.inc.php

@@ -299,7 +299,7 @@
            $update_data_str = $update_data;
        }
        
      $this->query("UPDATE $tablename SET $update_data WHERE $index_field = '$index_value'");
      $this->query("UPDATE $tablename SET $update_data_str WHERE $index_field = '$index_value'");
      $new_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'");
      $this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec, $force_update);


 interface/web/sites/database_del.php

@@ -46,6 +46,41 @@
$app->auth->check_module_permissions('sites');

$app->uses("tform_actions");
$app->tform_actions->onDelete();
class page_action extends tform_actions {
    function onBeforeDelete() {
        global $app; $conf;
        if($app->tform->checkPerm($this->id,'d') == false) $app->error($app->lng('error_no_delete_permission'));
        
        $old_record = $app->tform->getDataRecord($this->id);
        if($old_record['database_user_id']) {
            // check if any database on the server still uses this one
            $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($old_record['server_id']) . "' AND (`database_user_id` = '" . intval($old_record['database_user_id']) . "' OR `database_ro_user_id` = '" . intval($old_record['database_user_id']) . "') AND `sys_groupid` = '" . intval($old_record['sys_groupid']) . "' AND `database_id` != '" . intval($this->id) . "'");
            if($check['cnt'] < 1) {
                // send a datalog delete
                $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($old_record['database_user_id']) . "' AND `sys_groupid` = '" . intval($old_record['sys_groupid']) . "'");
                if($db_user) {
                    $db_user['server_id'] = $old_record['server_id'];
                    $app->db->datalogSave('web_database_user', 'DELETE', 'database_user_id', $db_user['database_user_id'], $db_user, array());
                }
            }
        }
        if($old_record['database_ro_user_id']) {
            // check if any database on the server still uses this one
            $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($old_record['server_id']) . "' AND (`database_user_id` = '" . intval($old_record['database_ro_user_id']) . "' OR `database_ro_user_id` = '" . intval($old_record['database_ro_user_id']) . "') AND `sys_groupid` = '" . intval($old_record['sys_groupid']) . "' AND `database_id` != '" . intval($this->id) . "'");
            if($check['cnt'] < 1) {
                // send a datalog delete
                $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($old_record['database_ro_user_id']) . "' AND `sys_groupid` = '" . intval($old_record['sys_groupid']) . "'");
                if($db_user) {
                    $db_user['server_id'] = $old_record['server_id'];
                    $app->db->datalogSave('web_database_user', 'DELETE', 'database_user_id', $db_user['database_user_id'], $db_user, array());
                }
            }
        }
        
    }
}

$page = new page_action;
$page->onDelete();

?>

 interface/web/sites/database_edit.php

@@ -114,20 +114,16 @@
        $app->uses('getconf');
        $global_config = $app->getconf->get_global_config('sites');
        $dbname_prefix = replacePrefix($global_config['dbname_prefix'], $this->dataRecord);
        $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord);
        
        if ($this->dataRecord['database_name'] != ""){
            /* REMOVE the restriction */
            $app->tpl->setVar("database_name", str_replace($dbname_prefix , '', $this->dataRecord['database_name']));
            $app->tpl->setVar("database_user", str_replace($dbuser_prefix , '', $this->dataRecord['database_user']));
        }
        
        if($_SESSION["s"]["user"]["typ"] == 'admin' || $app->auth->has_clients($_SESSION['s']['user']['userid'])) {
            $app->tpl->setVar("database_name_prefix", $global_config['dbname_prefix']);
            $app->tpl->setVar("database_user_prefix", $global_config['dbuser_prefix']);
        } else {
            $app->tpl->setVar("database_name_prefix", $dbname_prefix);
            $app->tpl->setVar("database_user_prefix", $dbuser_prefix);
        }
        
        if($this->id > 0) {
@@ -187,8 +183,7 @@
        $app->uses('getconf');
        $global_config = $app->getconf->get_global_config('sites');
        $dbname_prefix = replacePrefix($global_config['dbname_prefix'], $this->dataRecord);
        $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord);

		
        //* Prevent that the database name and charset is changed
        $old_record = $app->tform->getDataRecord($this->id);
        if($old_record["database_name"] != $dbname_prefix . $this->dataRecord["database_name"]) {
@@ -200,8 +195,7 @@
        
        //* Database username and database name shall not be empty
        if($this->dataRecord['database_name'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_name_error_empty"].'<br />';
        if($this->dataRecord['database_user'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_user_error_empty"].'<br />';

		
        //* Check if the server has been changed
        // We do this only for the admin or reseller users, as normal clients can not change the server ID anyway
        if($_SESSION["s"]["user"]["typ"] == 'admin' || $app->auth->has_clients($_SESSION['s']['user']['userid'])) {
@@ -214,7 +208,6 @@
        unset($old_record);
        
        if(strlen($dbname_prefix . $this->dataRecord['database_name']) > 64) $app->tform->errorMessage .= str_replace('{db}',$dbname_prefix . $this->dataRecord['database_name'],$app->tform->wordbook["database_name_error_len"]).'<br />';
        if(strlen($dbuser_prefix . $this->dataRecord['database_user']) > 16) $app->tform->errorMessage .= str_replace('{user}',$dbuser_prefix . $this->dataRecord['database_user'],$app->tform->wordbook["database_user_error_len"]).'<br />';
        
        //* Check database name and user against blacklist
        $dbname_blacklist = array($conf['db_database'],'mysql');
@@ -222,16 +215,10 @@
            $app->tform->errorMessage .= $app->lng('Database name not allowed.').'<br />';
        }
        
        $dbuser_blacklist = array($conf['db_user'],'mysql','root');
        if(in_array($dbname_prefix . $this->dataRecord['database_user'],$dbname_blacklist)) {
            $app->tform->errorMessage .= $app->lng('Database user not allowed.').'<br />';
        }
		
        if ($app->tform->errorMessage == ''){
            /* restrict the names if there is no error */
            /* crop user and db names if they are too long -> mysql: user: 16 chars / db: 64 chars */
            $this->dataRecord['database_name'] = substr($dbname_prefix . $this->dataRecord['database_name'], 0, 64);
            $this->dataRecord['database_user'] = substr($dbuser_prefix . $this->dataRecord['database_user'], 0, 16);
        }
        
        //* Check for duplicates
@@ -244,8 +231,11 @@
            // we need remote access rights for this server, so get it's ip address
            $server_config = $app->getconf->get_server_config($tmp['server_id'], 'server');
            if($server_config['ip_address']!='') {
                if($this->dataRecord['remote_access'] != 'y') $this->dataRecord['remote_ips'] = '';
                $this->dataRecord['remote_access'] = 'y';
                $this->dataRecord['remote_ips'] .= ($this->dataRecord['remote_ips'] != '' ? ',' : '') . $server_config['ip_address'];
                if(preg_match('/(^|,)' . preg_quote($server_config['ip_address'], '/') . '(,|$)/', $this->dataRecord['remote_ips']) == false) {
                    $this->dataRecord['remote_ips'] .= ($this->dataRecord['remote_ips'] != '' ? ',' : '') . $server_config['ip_address'];
                }
            }
        }
        
@@ -261,16 +251,13 @@
        
        //* Database username and database name shall not be empty
        if($this->dataRecord['database_name'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_name_error_empty"].'<br />';
        if($this->dataRecord['database_user'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_user_error_empty"].'<br />';

        //* Get the database name and database user prefix
        $app->uses('getconf');
        $global_config = $app->getconf->get_global_config('sites');
        $dbname_prefix = replacePrefix($global_config['dbname_prefix'], $this->dataRecord);
        $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord);
        
        if(strlen($dbname_prefix . $this->dataRecord['database_name']) > 64) $app->tform->errorMessage .= str_replace('{db}',$dbname_prefix . $this->dataRecord['database_name'],$app->tform->wordbook["database_name_error_len"]).'<br />';
        if(strlen($dbuser_prefix . $this->dataRecord['database_user']) > 16) $app->tform->errorMessage .= str_replace('{user}',$dbuser_prefix . $this->dataRecord['database_user'],$app->tform->wordbook["database_user_error_len"]).'<br />';
        
        //* Check database name and user against blacklist
        $dbname_blacklist = array($conf['db_database'],'mysql');
@@ -278,16 +265,10 @@
            $app->tform->errorMessage .= $app->lng('Database name not allowed.').'<br />';
        }
        
        $dbuser_blacklist = array($conf['db_user'],'mysql','root');
        if(in_array($dbname_prefix . $this->dataRecord['database_user'],$dbname_blacklist)) {
            $app->tform->errorMessage .= $app->lng('Database user not allowed.').'<br />';
        }

        /* restrict the names */
        /* crop user and db names if they are too long -> mysql: user: 16 chars / db: 64 chars */
        if ($app->tform->errorMessage == ''){
            $this->dataRecord['database_name'] = substr($dbname_prefix . $this->dataRecord['database_name'], 0, 64);
            $this->dataRecord['database_user'] = substr($dbuser_prefix . $this->dataRecord['database_user'], 0, 16);
        }
        
        //* Check for duplicates
@@ -300,14 +281,139 @@
            // we need remote access rights for this server, so get it's ip address
            $server_config = $app->getconf->get_server_config($tmp['server_id'], 'server');
            if($server_config['ip_address']!='') {
                if($this->dataRecord['remote_access'] != 'y') $this->dataRecord['remote_ips'] = '';
                $this->dataRecord['remote_access'] = 'y';
                $this->dataRecord['remote_ips'] .= (trim($this->dataRecord['remote_ips']) != '' ? ',' : '') . $server_config['ip_address'];
                if(preg_match('/(^|,)' . preg_quote($server_config['ip_address'], '/') . '(,|$)/', $this->dataRecord['remote_ips']) == false) {
                    $this->dataRecord['remote_ips'] .= ($this->dataRecord['remote_ips'] != '' ? ',' : '') . $server_config['ip_address'];
                }
            }
        }
        
        parent::onBeforeInsert();
    }

    function onInsertSave($sql) {
        global $app, $conf;

        if($this->dataRecord["parent_domain_id"] > 0) {
            $web = $app->db->queryOneRecord("SELECT * FROM web_domain WHERE domain_id = ".intval($this->dataRecord["parent_domain_id"]));
		
            //* The Database user shall be owned by the same group then the website
            $sys_groupid = $web['sys_groupid'];
        } else {
            $sys_groupid = $this->dataRecord['sys_groupid'];
        }
        

        if($this->dataRecord['database_user_id']) {
            // check if there has already been a database on this server with that user
            $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'");
            
            if($check && $check['cnt'] < 1) {
                // we need to make a datalog insert for the database users that are connected to this database
                $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                if($db_user) {
                    $db_user['server_id'] = $this->dataRecord['server_id'];
                    $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user);
                }
            }
        }

        if($this->dataRecord['database_ro_user_id']) {
            // check if there has already been a database on this server with that user
            $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'");
            
            if($check && $check['cnt'] < 1) {
                // we need to make a datalog insert for the database users that are connected to this database
                $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                if($db_user) {
                    $db_user['server_id'] = $this->dataRecord['server_id'];
                    $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user);
                }
            }
        }
        
        $app->db->query($sql);
        if($app->db->errorMessage != '') die($app->db->errorMessage);
        $new_id = $app->db->insertID();
        
        return $new_id;
    }

    function onUpdateSave($sql) {
        global $app;
        if(!empty($sql) && !$app->tform->isReadonlyTab($app->tform->getCurrentTab(),$this->id)) {
            $old_record = $app->tform->getDataRecord($this->id);
            
            if($this->dataRecord["parent_domain_id"] > 0) {
                $web = $app->db->queryOneRecord("SELECT * FROM web_domain WHERE domain_id = ".intval($this->dataRecord["parent_domain_id"]));
            
                //* The Database user shall be owned by the same group then the website
                $sys_groupid = $web['sys_groupid'];
            } else {
                $sys_groupid = $this->dataRecord['sys_groupid'];
            }
            
            // check if database user has changed
            if($old_record['database_user_id'] && $old_record['database_user_id'] != $this->dataRecord['database_user_id'] && $old_record['database_user_id'] != $this->dataRecord['database_ro_user_id']) {
                // check if any database on the server still uses this one
                $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($old_record['database_user_id']) . "' OR `database_ro_user_id` = '" . intval($old_record['database_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "' AND `database_id` != '" . intval($this->id) . "'");
                if($check['cnt'] < 1) {
                    // send a datalog delete
                    $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($old_record['database_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                    if($db_user) {
                        $db_user['server_id'] = $this->dataRecord['server_id'];
                        $app->db->datalogSave('web_database_user', 'DELETE', 'database_user_id', $db_user['database_user_id'], $db_user, array());
                    }
                }
            }
            // check if readonly database user has changed
            if($old_record['database_ro_user_id'] && $old_record['database_ro_user_id'] != $this->dataRecord['database_ro_user_id'] && $old_record['database_ro_user_id'] != $this->dataRecord['database_user_id']) {
                // check if any database on the server still uses this one
                $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($old_record['database_ro_user_id']) . "' OR `database_ro_user_id` = '" . intval($old_record['database_ro_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "' AND `database_id` != '" . intval($this->id) . "'");
                if($check['cnt'] < 1) {
                    // send a datalog delete
                    $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($old_record['database_ro_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                    if($db_user) {
                        $db_user['server_id'] = $this->dataRecord['server_id'];
                        $app->db->datalogSave('web_database_user', 'DELETE', 'database_user_id', $db_user['database_user_id'], $db_user, array());
                    }
                }
            }
            
            if($this->dataRecord['database_user_id']) {
                // check if there has already been a database on this server with that user
                $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                
                if($check && $check['cnt'] < 1) {
                    // we need to make a datalog insert for the database users that are connected to this database
                    $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                    if($db_user) {
                        $db_user['server_id'] = $this->dataRecord['server_id'];
                        $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user);
                    }
                }
            }

            if($this->dataRecord['database_ro_user_id']) {
                // check if there has already been a database on this server with that user
                $check = $app->db->queryOneRecord("SELECT COUNT(*) as `cnt` FROM `web_database` WHERE `server_id` = '" . intval($this->dataRecord['server_id']) . "' AND (`database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' OR `database_ro_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "') AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                
                if($check && $check['cnt'] < 1) {
                    // we need to make a datalog insert for the database users that are connected to this database
                    $db_user = $app->db->queryOneRecord("SELECT * FROM `web_database_user` WHERE `database_user_id` = '" . intval($this->dataRecord['database_ro_user_id']) . "' AND `sys_groupid` = '" . intval($sys_groupid) . "'");
                    if($db_user) {
                        $db_user['server_id'] = $this->dataRecord['server_id'];
                        $app->db->datalogSave('web_database_user', 'INSERT', 'database_user_id', $db_user['database_user_id'], array(), $db_user);
                    }
                }
            }

            $app->db->query($sql);
            if($app->db->errorMessage != '') die($app->db->errorMessage);
        }
    }
    
    function onAfterInsert() {
        global $app, $conf;
        

 interface/web/sites/database_user_del.php

New file
@@ -0,0 +1,78 @@
<?php

/*
Copyright (c) 2008, Till Brehm, projektfarm Gmbh
All rights reserved.

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice,
      this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.
    * Neither the name of ISPConfig nor the names of its contributors
      may be used to endorse or promote products derived from this software without
      specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

/******************************************
* Begin Form configuration
******************************************/

$list_def_file = "list/database_user.list.php";
$tform_def_file = "form/database_user.tform.php";

/******************************************
* End Form configuration
******************************************/

require_once('../../lib/config.inc.php');
require_once('../../lib/app.inc.php');

//* Check permissions for module
$app->auth->check_module_permissions('sites');

$app->uses("tform_actions");

class page_action extends tform_actions {
    function onBeforeDelete() {
        global $app; $conf;
        if($app->tform->checkPerm($this->id,'d') == false) $app->error($app->lng('error_no_delete_permission'));
        
        $old_record = $app->tform->getDataRecord($this->id);
        $app->db->datalogDelete('web_database_user', 'database_user_id', $this->id);
    }
    
    function onAfterDelete() { // this has to be done on AFTER delete, because we need the db user still in the database when the server plugin processes the datalog
        global $app; $conf;
		
        //* Update all records that belog to this user
        $records = $app->db->queryAllRecords("SELECT database_id FROM web_database WHERE database_user_id = '".intval($this->id)."'");
        foreach($records as $rec) {
            $app->db->datalogUpdate('web_database','database_user_id=NULL','database_id', $rec['database_id']);
            
        }
        $records = $app->db->queryAllRecords("SELECT database_id FROM web_database WHERE database_ro_user_id = '".intval($this->id)."'");
        foreach($records as $rec) {
            $app->db->datalogUpdate('web_database','database_ro_user_id=NULL','database_id', $rec['database_id']);
        }
    }
}

$page = new page_action;
$page->onDelete();

?>

 interface/web/sites/database_user_edit.php

New file
@@ -0,0 +1,212 @@
<?php
/*
Copyright (c) 2008, Till Brehm, projektfarm Gmbh
All rights reserved.

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice,
      this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.
    * Neither the name of ISPConfig nor the names of its contributors
      may be used to endorse or promote products derived from this software without
      specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/


/******************************************
* Begin Form configuration
******************************************/

$tform_def_file = "form/database_user.tform.php";

/******************************************
* End Form configuration
******************************************/

require_once('../../lib/config.inc.php');
require_once('../../lib/app.inc.php');
require_once('tools.inc.php');

//* Check permissions for module
$app->auth->check_module_permissions('sites');

// Loading classes
$app->uses('tpl,tform,tform_actions');
$app->load('tform_actions');

class page_action extends tform_actions {

    function onShowEnd() {
        global $app, $conf, $interfaceConf;

        /*
         * If the names are restricted -> remove the restriction, so that the
         * data can be edited
         */
		
        //* Get the database user prefix
        $app->uses('getconf');
        $global_config = $app->getconf->get_global_config('sites');
        $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord);
		
        if ($_SESSION["s"]["user"]["typ"] != 'admin' && $app->auth->has_clients($_SESSION['s']['user']['userid'])) {
            // Get the limits of the client
            $client_group_id = $_SESSION["s"]["user"]["default_group"];
            $client = $app->db->queryOneRecord("SELECT client.contactname, client.name, client.client_id FROM sys_group, client WHERE sys_group.client_id = client.client_id and sys_group.groupid = $client_group_id");
            
            // Fill the client select field
            $sql = "SELECT sys_group.groupid, sys_group.name, CONCAT(client.company_name,' :: ',client.contact_name) as contactname FROM sys_group, client WHERE sys_group.client_id = client.client_id AND client.parent_client_id = ".$client['client_id']." ORDER BY sys_group.name";
            $records = $app->db->queryAllRecords($sql);
            $tmp = $app->db->queryOneRecord("SELECT groupid FROM sys_group WHERE client_id = ".$client['client_id']);
            $client_select = '<option value="'.$tmp['groupid'].'">'.$client['name'].' :: '.$client['contactname'].'</option>';
            //$tmp_data_record = $app->tform->getDataRecord($this->id);
            if(is_array($records)) {
                foreach( $records as $rec) {
                    $selected = @(is_array($this->dataRecord) && ($rec["groupid"] == $this->dataRecord['client_group_id'] || $rec["groupid"] == $this->dataRecord['sys_groupid']))?'SELECTED':'';
                    $client_select .= "<option value='$rec[groupid]' $selected>$rec[name] :: $rec[contactname]</option>\r\n";
                }
            }
            $app->tpl->setVar("client_group_id",$client_select);
        } elseif($_SESSION["s"]["user"]["typ"] == 'admin') {
            // Fill the client select field
            $sql = "SELECT sys_group.groupid, sys_group.name, CONCAT(client.company_name,' :: ',client.contact_name) as contactname FROM sys_group, client WHERE sys_group.client_id = client.client_id AND sys_group.client_id > 0 ORDER BY sys_group.name";
            $clients = $app->db->queryAllRecords($sql);
            $client_select = "<option value='0'></option>";
            //$tmp_data_record = $app->tform->getDataRecord($this->id);
            if(is_array($clients)) {
                foreach( $clients as $client) {
                    //$selected = @($client["groupid"] == $tmp_data_record["sys_groupid"])?'SELECTED':'';
                    $selected = @(is_array($this->dataRecord) && ($client["groupid"] == $this->dataRecord['client_group_id'] || $client["groupid"] == $this->dataRecord['sys_groupid']))?'SELECTED':'';
                    $client_select .= "<option value='$client[groupid]' $selected>$client[name] :: $client[contactname]</option>\r\n";
                }
            }
            $app->tpl->setVar("client_group_id",$client_select);
        }
        
        
        if ($this->dataRecord['database_user'] != ""){
            /* REMOVE the restriction */
            $app->tpl->setVar("database_user", str_replace($dbuser_prefix , '', $this->dataRecord['database_user']));
        }
		
        if($_SESSION["s"]["user"]["typ"] == 'admin' || $app->auth->has_clients($_SESSION['s']['user']['userid'])) {
            $app->tpl->setVar("database_user_prefix", $global_config['dbuser_prefix']);
        } else {
            $app->tpl->setVar("database_user_prefix", $dbuser_prefix);
        }
		
        parent::onShowEnd();
    }
    
    function onSubmit() {
        global $app;
        
        if($_SESSION['s']['user']['typ'] != 'admin' && !$app->auth->has_clients($_SESSION['s']['user']['userid'])) unset($this->dataRecord["client_group_id"]);
        
        parent::onSubmit();
    }

    function onBeforeUpdate() {
        global $app, $conf, $interfaceConf;

        //* Get the database user prefix
        $app->uses('getconf');
        $global_config = $app->getconf->get_global_config('sites');
        $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord);

        //* Database username shall not be empty
        if($this->dataRecord['database_user'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_user_error_empty"].'<br />';

        if(strlen($dbuser_prefix . $this->dataRecord['database_user']) > 16) $app->tform->errorMessage .= str_replace('{user}',$dbuser_prefix . $this->dataRecord['database_user'],$app->tform->wordbook["database_user_error_len"]).'<br />';
		
        //* Check database user against blacklist
        $dbuser_blacklist = array($conf['db_user'],'mysql','root');
        if(in_array($dbname_prefix . $this->dataRecord['database_user'],$dbname_blacklist)) {
            $app->tform->errorMessage .= $app->lng('Database user not allowed.').'<br />';
        }
		
        if ($app->tform->errorMessage == ''){
            /* restrict the names if there is no error */
            /* crop user and db names if they are too long -> mysql: user: 16 chars / db: 64 chars */
            $this->dataRecord['database_user'] = substr($dbuser_prefix . $this->dataRecord['database_user'], 0, 16);
        }
		
        parent::onBeforeUpdate();
    }

    function onBeforeInsert() {
        global $app, $conf, $interfaceConf;
		
        //* Database username shall not be empty
        if($this->dataRecord['database_user'] == '') $app->tform->errorMessage .= $app->tform->wordbook["database_user_error_empty"].'<br />';

        //* Get the database name and database user prefix
        $app->uses('getconf');
        $global_config = $app->getconf->get_global_config('sites');
        $dbuser_prefix = replacePrefix($global_config['dbuser_prefix'], $this->dataRecord);
		
        if(strlen($dbuser_prefix . $this->dataRecord['database_user']) > 16) $app->tform->errorMessage .= str_replace('{user}',$dbuser_prefix . $this->dataRecord['database_user'],$app->tform->wordbook["database_user_error_len"]).'<br />';
		
        //* Check database user against blacklist
        $dbuser_blacklist = array($conf['db_user'],'mysql','root');
        if(in_array($dbname_prefix . $this->dataRecord['database_user'],$dbname_blacklist)) {
            $app->tform->errorMessage .= $app->lng('Database user not allowed.').'<br />';
        }

        /* restrict the names */
        /* crop user names if they are too long -> mysql: user: 16 chars / db: 64 chars */
        if ($app->tform->errorMessage == ''){
            $this->dataRecord['database_user'] = substr($dbuser_prefix . $this->dataRecord['database_user'], 0, 16);
        }
		
        parent::onBeforeInsert();
    }

    function onAfterInsert() {
        global $app, $conf;
		
        if($_SESSION["s"]["user"]["typ"] == 'admin' && isset($this->dataRecord["client_group_id"])) {
            $client_group_id = intval($this->dataRecord["client_group_id"]);
            $app->db->query("UPDATE web_database_user SET sys_groupid = $client_group_id, sys_perm_group = 'riud' WHERE database_user_id = ".$this->id);
        }
        if($app->auth->has_clients($_SESSION['s']['user']['userid']) && isset($this->dataRecord["client_group_id"])) {
            $client_group_id = intval($this->dataRecord["client_group_id"]);
            $app->db->query("UPDATE web_database_user SET sys_groupid = $client_group_id, sys_perm_group = 'riud' WHERE database_user_id = ".$this->id);
        }
    }

    function onAfterUpdate() {
        global $app, $conf;

        if($_SESSION["s"]["user"]["typ"] == 'admin' && isset($this->dataRecord["client_group_id"])) {
            $client_group_id = intval($this->dataRecord["client_group_id"]);
            $app->db->query("UPDATE web_database_user SET sys_groupid = $client_group_id, sys_perm_group = 'riud' WHERE database_user_id = ".$this->id);
        }
        if($app->auth->has_clients($_SESSION['s']['user']['userid']) && isset($this->dataRecord["client_group_id"])) {
            $client_group_id = intval($this->dataRecord["client_group_id"]);
            $app->db->query("UPDATE web_database_user SET sys_groupid = $client_group_id, sys_perm_group = 'riud' WHERE database_user_id = ".$this->id);
        }

    }

}

$page = new page_action;
$page->onLoad();

?>

 interface/web/sites/database_user_list.php

New file
@@ -0,0 +1,65 @@
<?php

/*
Copyright (c) 2008, Till Brehm, projektfarm Gmbh
All rights reserved.

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice,
      this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.
    * Neither the name of ISPConfig nor the names of its contributors
      may be used to endorse or promote products derived from this software without
      specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

require_once('../../lib/config.inc.php');
require_once('../../lib/app.inc.php');

/******************************************
* Begin Form configuration
******************************************/

$list_def_file = "list/database_user.list.php";

/******************************************
* End Form configuration
******************************************/

//* Check permissions for module
$app->auth->check_module_permissions('sites');

$app->load('listform_actions');


class list_action extends listform_actions {
	
    function onShow() {
        global $app,$conf;
		
        parent::onShow();
    }
	
}

$list = new list_action;
$list->SQLOrderBy = 'ORDER BY database_user';
$list->onLoad();


?>

 interface/web/sites/form/database.tform.php

@@ -106,31 +106,27 @@
            'maxlength'    => '255',
            'searchable' => 1
        ),
        'database_user' => array (
            'datatype'    => 'VARCHAR',
            'formtype'    => 'TEXT',
            'validators'    => array (     0 => array (    'type'    => 'NOTEMPTY',
                                                        'errmsg'=> 'database_user_error_empty'),
                                        1 => array (    'type'    => 'UNIQUE',
                                                        'errmsg'=> 'database_user_error_unique'),
                                        2 => array (    'type'    => 'REGEX',
                                                        'regex' => '/^[a-zA-Z0-9_]{2,64}$/',
                                                        'errmsg'=> 'database_user_error_regex'),
                                    ),
        'database_user_id' => array (
            'datatype'    => 'INTEGER',
            'formtype'    => 'SELECT',
            'default'    => '',
            'value'        => '',
            'width'        => '30',
            'maxlength'    => '255',
            'searchable' => 2
            'datasource'    => array (     'type'    => 'SQL',
                                        'querystring' => "SELECT database_user_id,database_user FROM web_database_user WHERE {AUTHSQL} ORDER BY database_user",
                                        'keyfield'=> 'database_user_id',
                                        'valuefield'=> 'database_user'
                                     ),
            'value'        => array('0' => $app->tform->lng('select_dbuser_txt'))
        ),
        'database_password' => array (
            'datatype'    => 'VARCHAR',
            'formtype'    => 'PASSWORD',
            'encryption' => 'MYSQL',
        'database_ro_user_id' => array (
            'datatype'    => 'INTEGER',
            'formtype'    => 'SELECT',
            'default'    => '',
            'value'        => '',
            'width'        => '30',
            'maxlength'    => '255'
            'datasource'    => array (     'type'    => 'SQL',
                                        'querystring' => "SELECT database_user_id,database_user FROM web_database_user WHERE {AUTHSQL} ORDER BY database_user",
                                        'keyfield'=> 'database_user_id',
                                        'valuefield'=> 'database_user'
                                     ),
            'value'        => array('0' => $app->tform->lng('no_dbuser_txt'))
        ),
        'database_charset' => array (
            'datatype'    => 'VARCHAR',

 interface/web/sites/form/database_user.tform.php

New file
@@ -0,0 +1,99 @@
<?php

/*
    Form Definition

    Tabledefinition

    Datatypes:
    - INTEGER (Forces the input to Int)
    - DOUBLE
    - CURRENCY (Formats the values to currency notation)
    - VARCHAR (no format check, maxlength: 255)
    - TEXT (no format check)
    - DATE (Dateformat, automatic conversion to timestamps)

    Formtype:
    - TEXT (Textfield)
    - TEXTAREA (Textarea)
    - PASSWORD (Password textfield, input is not shown when edited)
    - SELECT (Select option field)
    - RADIO
    - CHECKBOX
    - CHECKBOXARRAY
    - FILE

    VALUE:
    - Wert oder Array

    Hint:
    The ID field of the database table is not part of the datafield definition.
    The ID field must be always auto incement (int or bigint).
	
    Search:
    - searchable = 1 or searchable = 2 include the field in the search
    - searchable = 1: this field will be the title of the search result
    - searchable = 2: this field will be included in the description of the search result


*/

$form["title"]             = "Database User";
$form["description"]     = "";
$form["name"]             = "database_user";
$form["action"]            = "database_user_edit.php";
$form["db_table"]        = "web_database_user";
$form["db_table_idx"]    = "database_user_id";
$form["db_history"]        = "no";
$form["tab_default"]    = "database_user";
$form["list_default"]    = "database_user_list.php";
$form["auth"]            = 'yes'; // yes / no

$form["auth_preset"]["userid"]  = 0; // 0 = id of the user, > 0 id must match with id of current user
$form["auth_preset"]["groupid"] = 0; // 0 = default groupid of the user, > 0 id must match with groupid of current user
$form["auth_preset"]["perm_user"] = 'riud'; //r = read, i = insert, u = update, d = delete
$form["auth_preset"]["perm_group"] = 'riud'; //r = read, i = insert, u = update, d = delete
$form["auth_preset"]["perm_other"] = ''; //r = read, i = insert, u = update, d = delete

$form["tabs"]['database_user'] = array (
    'title'     => "Database User",
    'width'     => 100,
    'template'     => "templates/database_user_edit.htm",
    'fields'     => array (
    ##################################
    # Begin Datatable fields
    ##################################
        'database_user' => array (
            'datatype'    => 'VARCHAR',
            'formtype'    => 'TEXT',
            'validators'    => array (     0 => array (    'type'    => 'NOTEMPTY',
                                                        'errmsg'=> 'database_user_error_empty'),
                                        1 => array (    'type'    => 'UNIQUE',
                                                        'errmsg'=> 'database_user_error_unique'),
                                        2 => array (    'type'    => 'REGEX',
                                                        'regex' => '/^[a-zA-Z0-9_]{2,64}$/',
                                                        'errmsg'=> 'database_user_error_regex'),
                                    ),
            'default'    => '',
            'value'        => '',
            'width'        => '30',
            'maxlength'    => '255',
            'searchable' => 2
        ),
        'database_password' => array (
            'datatype'    => 'VARCHAR',
            'formtype'    => 'PASSWORD',
            'encryption' => 'MYSQL',
            'default'    => '',
            'value'        => '',
            'width'        => '30',
            'maxlength'    => '255'
        ),
    ##################################
    # ENDE Datatable fields
    ##################################
    )
);


?>

 interface/web/sites/lib/lang/de_database.lng

@@ -3,10 +3,14 @@
$wb['type_txt'] = 'Typ';
$wb['database_name_txt'] = 'Datenbankname';
$wb['database_user_txt'] = 'Datenbank-Benutzer';
$wb['database_ro_user_txt'] = 'Nur-Lesen Datenbank-Benutzer';
$wb['optional_txt'] = 'optional';
$wb['database_password_txt'] = 'Datenbank-Passwort';
$wb['database_charset_txt'] = 'Datenbank Zeichensatz';
$wb['remote_access_txt'] = 'Remotezugang';
$wb['remote_ips_txt'] = 'Remotezugang-IPs (mit Komma trennen, keine Eingabe für <i>alle</i>)';
$wb['select_dbuser_txt'] = 'Datenbank User auswählen';
$wb['no_dbuser_txt'] = 'Keiner';
$wb['remote_access_txt'] = 'Remotezugriff';
$wb['remote_ips_txt'] = 'Remotezugriff-IPs (mit Komma trennen, keine Eingabe für <i>alle</i>)';
$wb['database_remote_error_ips'] = 'Mindestens eine der eingegebenen IP-Adressen ist ungültig.';
$wb['client_txt'] = 'Kunde';
$wb['active_txt'] = 'Aktiv';

 interface/web/sites/lib/lang/de_database_list.lng

@@ -1,7 +1,7 @@
<?php
$wb['list_head_txt'] = 'Datenbank';
$wb['active_txt'] = 'Aktiv';
$wb['remote_access_txt'] = 'Remotezugang';
$wb['remote_access_txt'] = 'Remotezugriff';
$wb['server_id_txt'] = 'Server';
$wb['database_name_txt'] = 'Datenbankname';
$wb['add_new_record_txt'] = 'Neue Datenbank hinzufügen';

 interface/web/sites/lib/lang/de_database_user.lng

New file
@@ -0,0 +1,23 @@
<?php
$wb['database_user_txt'] = 'Datenbank-Benutzer';
$wb['database_password_txt'] = 'Datenbank-Passwort';
$wb['client_txt'] = 'Kunde';
$wb['active_txt'] = 'Aktiv';
$wb['database_user_error_empty'] = 'Datenbank Benutzer ist leer.';
$wb['database_user_error_unique'] = 'Es existiert bereits ein Benutzer mit diesem Namen am Server. Um einen eindeutigen Namen zu erhalten können sie z.B. den Domainnamen vor dem Benutzernamen verwenden.';
$wb['database_user_error_regex'] = 'Ungültiger Benutzername. Der Benutzername darf die Zeichen: a-z, A-Z, 0-9 und den Unterstrich beinhalten. Länge: 2 - 64 Zeichen.';
$wb['password_strength_txt'] = 'Passwortkomplexität';
$wb['database_user_error_len'] = 'Datenbank Benutzername - {user} - zu lang. Die max. Datenbank Benutzernamen Länge inkl. Präfix ist 16 Zeichen.';
$wb['generate_password_txt'] = 'Passwort erzeugen';
$wb['repeat_password_txt'] = 'Passwort wiederholen';
$wb['password_mismatch_txt'] = 'Die Passwörter stimmen nicht überein.';
$wb['password_match_txt'] = 'Die Passwörter stimmen überein.';
$wb['btn_save_txt'] = 'Speichern';
$wb['btn_cancel_txt'] = 'Abbrechen';
$wb['globalsearch_resultslimit_of_txt'] = 'von';
$wb['globalsearch_resultslimit_results_txt'] = 'Treffern';
$wb['globalsearch_noresults_text_txt'] = 'Keine Treffer.';
$wb['globalsearch_noresults_limit_txt'] = '0 Treffer';
$wb['globalsearch_searchfield_watermark_txt'] = 'Suche';
$wb['globalsearch_suggestions_text_txt'] = 'Vorschläge';
?>

 interface/web/sites/lib/lang/de_database_user_admin_list.lng

New file
@@ -0,0 +1,6 @@
<?php
$wb['list_head_txt'] = 'Datenbank User';
$wb['add_new_record_txt'] = 'Neuen User hinzufügen';
$wb['database_user_txt'] = 'Datenbank User';
$wb['sys_groupid_txt'] = 'Kunde';
?>

 interface/web/sites/lib/lang/de_database_user_list.lng

New file
@@ -0,0 +1,5 @@
<?php
$wb['list_head_txt'] = 'Datenbank User';
$wb['add_new_record_txt'] = 'Neuen User hinzufügen';
$wb['database_user_txt'] = 'Datenbank User';
?>

 interface/web/sites/lib/lang/en_database.lng

@@ -3,9 +3,13 @@
$wb["type_txt"] = 'Type';
$wb["database_name_txt"] = 'Database name';
$wb["database_user_txt"] = 'Database user';
$wb['database_ro_user_txt'] = 'Read-only database user';
$wb['optional_txt'] = 'optional';
$wb["database_password_txt"] = 'Database password';
$wb["password_strength_txt"] = 'Password strength';
$wb["database_charset_txt"] = 'Database charset';
$wb['select_dbuser_txt'] = 'Select database user';
$wb['no_dbuser_txt'] = 'None';
$wb["remote_access_txt"] = 'Remote Access';
$wb["remote_ips_txt"] = 'Remote Access IPs (separate by , and leave blank for <i>any</i>)';
$wb["database_remote_error_ips"] = 'At least one of the entered ip addresses is invalid.';

 interface/web/sites/lib/lang/en_database_user.lng

New file
@@ -0,0 +1,23 @@
<?php
$wb["database_user_txt"] = 'Database user';
$wb["database_password_txt"] = 'Database password';
$wb["password_strength_txt"] = 'Password strength';
$wb["client_txt"] = 'Client';
$wb["active_txt"] = 'Active';
$wb["database_user_error_empty"] = 'Database user is empty.';
$wb["database_user_error_unique"] = 'There is already a database user with this name on the server. To get a unique name, e.g. prepend your domain name to the username.';
$wb["database_user_error_regex"] = 'Invalid database user name. The username may contain these characters: a-z, A-Z, 0-9 and the underscore. Length: 2 - 64 characters.';
$wb["database_user_error_len"] = 'Database username - {user} - too long. The max. database username length incl. prefix is 16 chars.';
$wb["btn_save_txt"] = 'Save';
$wb["btn_cancel_txt"] = 'Cancel';
$wb['generate_password_txt'] = 'Generate Password';
$wb['repeat_password_txt'] = 'Repeat Password';
$wb['password_mismatch_txt'] = 'The passwords do not match.';
$wb['password_match_txt'] = 'The passwords do match.';
$wb['globalsearch_resultslimit_of_txt'] = "of";
$wb['globalsearch_resultslimit_results_txt'] = "results";
$wb['globalsearch_noresults_text_txt'] = "No results.";
$wb['globalsearch_noresults_limit_txt'] = "0 results";
$wb['globalsearch_searchfield_watermark_txt'] = "Search";
$wb['globalsearch_suggestions_text_txt'] = "Suggestions";
?>

 interface/web/sites/lib/lang/en_database_user_admin_list.lng

New file
@@ -0,0 +1,6 @@
<?php
$wb["list_head_txt"] = 'Database User';
$wb["database_user_txt"] = 'Database user';
$wb["add_new_record_txt"] = 'Add new User';
$wb["sys_groupid_txt"] = 'Client';
?>

 interface/web/sites/lib/lang/en_database_user_list.lng

New file
@@ -0,0 +1,5 @@
<?php
$wb["list_head_txt"] = 'Database user';
$wb["database_user_txt"] = 'Database user';
$wb["add_new_record_txt"] = 'Add new user';
?>

 interface/web/sites/lib/module.conf.php

@@ -62,15 +62,12 @@
                    'link'    => 'sites/database_list.php',
                    'html_id'   => 'database_list');

/*
Database User (for future development)

$items[] = array(   'title'     => "Database User",
                    'target'     => 'content',
                    'link'    => 'sites/database_user_list.php',
                    'html_id'   => 'database_user_list'
);
*/

$module["nav"][] = array(   'title' => 'Database',
                            'open'  => 1,

 interface/web/sites/lib/remote.conf.php

@@ -1,6 +1,6 @@
<?php
$function_list['sites_cron_get,sites_cron_add,sites_cron_update,sites_cron_delete'] = 'Sites cron functions';
$function_list['sites_database_get,sites_database_add,sites_database_update,sites_database_delete, sites_database_get_all_by_user'] = 'Sites database functions';
$function_list['sites_database_get,sites_database_add,sites_database_update,sites_database_delete, sites_database_get_all_by_user,sites_database_user_get,sites_database_user_add,sites_database_user_update,sites_database_user_delete, sites_database_user_get_all_by_user'] = 'Sites database functions';
$function_list['sites_web_folder_get,sites_web_folder_add,sites_web_folder_update,sites_web_folder_delete,sites_web_folder_user_get,sites_web_folder_user_add,sites_web_folder_user_update,sites_web_folder_user_delete'] = 'Sites Protected folder functions';
$function_list['sites_ftp_user_get,sites_ftp_user_server_get,sites_ftp_user_add,sites_ftp_user_update,sites_ftp_user_delete'] = 'Sites FTP-User functions';
$function_list['sites_shell_user_get,sites_shell_user_add,sites_shell_user_update,sites_shell_user_delete'] = 'Sites Shell-User functions';

 interface/web/sites/list/database.list.php

@@ -100,12 +100,17 @@
                            'width'        => "",
                            'value'        => "");

$liste["item"][] = array(    'field'        => "database_user",
                            'datatype'    => "VARCHAR",
                            'formtype'    => "TEXT",
                            'op'        => "like",
                            'prefix'    => "%",
                            'suffix'    => "%",
$liste["item"][] = array(    'field'        => "database_user_id",
                            'datatype'    => "INTEGER",
                            'formtype'    => "SELECT",
                            'op'        => "=",
                            'prefix'    => "",
                            'suffix'    => "",
                            'datasource'    => array (     'type'    => 'SQL',
                                                        'querystring' => 'SELECT database_user_id, database_user FROM web_database_user WHERE {AUTHSQL} ORDER BY database_user',
                                                        'keyfield'=> 'database_user_id',
                                                        'valuefield'=> 'database_user'
                                                       ),
                            'width'        => "",
                            'value'        => "");


 interface/web/sites/list/database_user.list.php

New file
@@ -0,0 +1,81 @@
<?php

/*
    Datatypes:
    - INTEGER
    - DOUBLE
    - CURRENCY
    - VARCHAR
    - TEXT
    - DATE
*/



// Name of the list
if($_SESSION['s']['user']['typ'] == 'admin') {
    $liste["name"]                 = "database_user_admin";
} else {
    $liste["name"]                 = "database_user";
}

// Database table
$liste["table"]             = "web_database_user";

// Index index field of the database table
$liste["table_idx"]            = "database_user_id";

// Search Field Prefix
$liste["search_prefix"]     = "search_";

// Records per page
$liste["records_per_page"]     = "15";

// Script File of the list
$liste["file"]                = "database_user_list.php";

// Script file of the edit form
$liste["edit_file"]            = "database_user_edit.php";

// Script File of the delete script
$liste["delete_file"]        = "database_user_del.php";

// Paging Template
$liste["paging_tpl"]        = "templates/paging.tpl.htm";

// Enable auth
$liste["auth"]                = "yes";


/*****************************************************
* Suchfelder
*****************************************************/


if($_SESSION['s']['user']['typ'] == 'admin') {
$liste["item"][] = array(    'field'        => "sys_groupid",
                            'datatype'    => "INTEGER",
                            'formtype'    => "SELECT",
                            'op'        => "=",
                            'prefix'    => "",
                            'suffix'    => "",
                            'datasource'    => array (     'type'    => 'SQL',
                                                        'querystring' => 'SELECT groupid, name FROM sys_group WHERE groupid != 1 ORDER BY name',
                                                        'keyfield'=> 'groupid',
                                                        'valuefield'=> 'name'
                                                       ),
                            'width'        => "",
                            'value'        => "");
}

$liste["item"][] = array(    'field'        => "database_user",
                            'datatype'    => "VARCHAR",
                            'formtype'    => "TEXT",
                            'op'        => "like",
                            'prefix'    => "%",
                            'suffix'    => "%",
                            'width'        => "",
                            'value'        => "");


?>

 interface/web/sites/templates/database_admin_list.htm

@@ -30,7 +30,7 @@
                        <td class="tbl_col_remote_access"><select name="search_remote_access">{tmpl_var name='search_remote_access'}</select></td>
                        <td class="tbl_col_sys_groupid"><select name="search_sys_groupid">{tmpl_var name='search_sys_groupid'}</select></td>
                        <td class="tbl_col_server_id"><select name="search_server_id">{tmpl_var name='search_server_id'}</select></td>
                        <td class="tbl_col_database_user"><input type="text" name="search_database_user" value="{tmpl_var name='search_database_user'}" /></td>
                        <td class="tbl_col_database_user"><select name="search_database_user_id">{tmpl_var name='search_database_user_id'}</select></td>
                        <td class="tbl_col_database_name"><input type="text" name="search_database_name" value="{tmpl_var name='search_database_name'}" /></td>
                        <td class="tbl_col_buttons"><div class="buttons"><button type="button" class="icons16 icoFilter" name="Filter" id="Filter" value="{tmpl_var name="filter_txt"}" onClick="submitForm('pageForm','sites/database_list.php');"><span>{tmpl_var name="filter_txt"}</span></button></div></td>
                    </tr>
@@ -42,7 +42,7 @@
                            <td class="tbl_col_remote_access"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="remote_access"}</a></td>
                            <td class="tbl_col_sys_groupid"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="sys_groupid"}</a></td>
                            <td class="tbl_col_server_id"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="server_id"}</a></td>
                            <td class="tbl_col_database_user"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_user"}</a></td>
                            <td class="tbl_col_database_user"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_user_id"}</a></td>
                            <td class="tbl_col_database_name"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_name"}</a></td>
                            <td class="tbl_col_buttons">
                                <div class="buttons icons16">

 interface/web/sites/templates/database_edit.htm

@@ -44,25 +44,17 @@
                </tmpl_if>
            </div>
            <div class="ctrlHolder">
                <label for="database_user">{tmpl_var name='database_user_txt'}</label>
                <p class="prefix">{tmpl_var name='database_user_prefix'}</p>
                <input name="database_user" id="database_user" value="{tmpl_var name='database_user'}" size="30" maxlength="255" type="text" class="textInput formLengthHalf" />
                <label for="database_user_id">{tmpl_var name='database_user_txt'}</label>
                <select name="database_user_id" id="database_user_id" class="selectInput">
                    {tmpl_var name='database_user_id'}
                </select>
            </div>
            <div class="ctrlHolder">
                <label for="database_password">{tmpl_var name='database_password_txt'}</label>
                <input name="database_password" id="database_password" value="{tmpl_var name='database_password'}" size="30" maxlength="255" type="password" class="textInput formLengthHalf" onkeyup="pass_check(this.value);checkPassMatch('database_password','repeat_password');" />&nbsp;<a href="javascript:void(0);" onClick="generatePassword('database_password','repeat_password');">{tmpl_var name='generate_password_txt'}</a>
                <label for="database_ro_user_id">{tmpl_var name='database_ro_user_txt'}</label>
                <select name="database_ro_user_id" id="database_ro_user_id" class="selectInput">
                    {tmpl_var name='database_ro_user_id'}
                </select>&nbsp;{tmpl_var name='optional_txt'}
            </div>
            <div class="ctrlHolder">
                <p class="label">{tmpl_var name='password_strength_txt'}</p>
                <div id="passBar"></div>
                <p class="formHint"><span id="passText">&nbsp;</span></p>
            </div>
            <div class="ctrlHolder">
                <label for="repeat_password">{tmpl_var name='repeat_password_txt'}</label>
                <input name="repeat_password" id="repeat_password" value="" size="15" maxlength="100" type="password" class="textInput" style="width:100px;"  onkeyup="checkPassMatch('database_password','repeat_password');" />
            </div>
            <div id="confirmpasswordError" style="display:none;" class="confirmpassworderror">{tmpl_var name='password_mismatch_txt'}</div>
            <div id="confirmpasswordOK" style="display:none;" class="confirmpasswordok">{tmpl_var name='password_match_txt'}</div>
            <div class="ctrlHolder">
                <tmpl_if name="edit_disabled">
                    <label for="database_charset_disabled">{tmpl_var name='database_charset_txt'}</label>

 interface/web/sites/templates/database_list.htm

@@ -21,7 +21,7 @@
                        <th class="tbl_col_active" scope="col"><tmpl_var name="active_txt"></th>
                        <th class="tbl_col_remote_access" scope="col"><tmpl_var name="remote_access_txt"></th>
                        <th class="tbl_col_server_id" scope="col"><tmpl_var name="server_id_txt"></th>
                        <th class="tbl_col_database_name" scope="col"><tmpl_var name="database_user_txt"></th>
                        <th class="tbl_col_database_user" scope="col"><tmpl_var name="database_user_txt"></th>
                        <th class="tbl_col_database_name" scope="col"><tmpl_var name="database_name_txt"></th>
                        <th class="tbl_col_limit" scope="col">{tmpl_var name='search_limit'}</th>
                    </tr>
@@ -29,7 +29,7 @@
                        <td class="tbl_col_active"><select name="search_active">{tmpl_var name='search_active'}</select></td>
                        <td class="tbl_col_remote_access"><select name="search_remote_access">{tmpl_var name='search_remote_access'}</select></td>
                        <td class="tbl_col_server_id"><select name="search_server_id">{tmpl_var name='search_server_id'}</select></td>
                        <td class="tbl_col_database_user"><input type="text" name="search_database_user" value="{tmpl_var name='search_database_user'}"/></td>
                        <td class="tbl_col_database_user"><select name="search_database_user_id">{tmpl_var name='search_database_user_id'}</select></td>
                        <td class="tbl_col_database_name"><input type="text" name="search_database_name" value="{tmpl_var name='search_database_name'}"/></td>
                        <td class="tbl_col_buttons"><div class="buttons"><button type="button" class="icons16 icoFilter" name="Filter" id="Filter" value="{tmpl_var name="filter_txt"}" onClick="submitForm('pageForm','sites/database_list.php');"><span>{tmpl_var name="filter_txt"}</span></button></div></td>
                    </tr>
@@ -40,7 +40,7 @@
                            <td class="tbl_col_active"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="active"}</a></td>
                            <td class="tbl_col_remote_access"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="remote_access"}</a></td>
                            <td class="tbl_col_server_id"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="server_id"}</a></td>
                            <td class="tbl_col_database_user"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_user"}</a></td>
                            <td class="tbl_col_database_user"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_user_id"}</a></td>
                            <td class="tbl_col_database_name"><a href="#" onClick="loadContent('sites/database_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_name"}</a></td>
                            <td class="tbl_col_buttons">
                                <div class="buttons icons16">    

 interface/web/sites/templates/database_user_admin_list.htm

New file
@@ -0,0 +1,50 @@
<h2><tmpl_var name="list_head_txt"></h2>

<div class="panel panel_list_database_user_admin">

    <div class="pnl_toolsarea">
        <fieldset><legend>{tmpl_var name="toolsarea_head_txt"}</legend>
            <div class="buttons">
                <button class="iconstxt icoAdd" type="button" onClick="loadContent('sites/database_user_edit.php');">
                    <span>{tmpl_var name="add_new_record_txt"}</span>
                </button>
            </div>
        </fieldset>
    </div>

    <div class="pnl_listarea">
        <fieldset><legend><tmpl_var name="list_head_txt"></legend>
            <table class="list">
                <thead>
                    <tr>
                        <th class="tbl_col_database_user" scope="col"><tmpl_var name="database_user_txt"></th>
                        <th class="tbl_col_limit" scope="col">{tmpl_var name='search_limit'}</th>
                    </tr>
                    <tr>
                        <td class="tbl_col_database_user"><input type="text" name="search_database_user" value="{tmpl_var name='search_database_user'}" /></td>
                        <td class="tbl_col_buttons"><div class="buttons"><button type="button" class="icons16 icoFilter" name="Filter" id="Filter" value="{tmpl_var name="filter_txt"}" onClick="submitForm('pageForm','sites/database_user_list.php');"><span>{tmpl_var name="filter_txt"}</span></button></div></td>
                    </tr>
                </thead>
                <tbody>
                    <tmpl_loop name="records">
                        <tr class="tbl_row_<tmpl_if name='__EVEN__'}even<tmpl_else>uneven</tmpl_if>">
                            <td class="tbl_col_database_user"><a href="#" onClick="loadContent('sites/database_user_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_user"}</a></td>
                            <td class="tbl_col_buttons">
                                <div class="buttons icons16">
                                    <a class="icons16 icoEdit" href="javascript: loadContent('sites/database_user_edit.php?id={tmpl_var name='id'}');"><span>{tmpl_var name='edit_txt'}</span></a>
                                    <a class="icons16 icoDelete" href="javascript: del_record('sites/database_user_del.php?id={tmpl_var name='id'}&phpsessid={tmpl_var name='phpsessid'}','{tmpl_var name='delete_confirmation'}');"><span>{tmpl_var name='delete_txt'}</span></a>
                                </div>
                            </td>
                        </tr>
                    </tmpl_loop>
                </tbody>
                <tfoot>
                    <tr>
                        <td class="tbl_footer tbl_paging" colspan="2"><tmpl_var name="paging"></td>
                    </tr>
                </tfoot>
            </table>
        </fieldset>
    </div>

</div>

 interface/web/sites/templates/database_user_edit.htm

New file
@@ -0,0 +1,54 @@
<h2><tmpl_var name="list_head_txt"></h2>
<p><tmpl_var name="list_desc_txt"></p>

<div class="panel panel_database_user">

    <div class="pnl_formsarea">
        <fieldset class="inlineLabels">
            <tmpl_if name="is_admin">
                <div class="ctrlHolder">
                    <label for="client_group_id">{tmpl_var name='client_txt'}</label>
                    <select name="client_group_id" id="client_group_id" class="selectInput">
                        {tmpl_var name='client_group_id'}
                    </select>
                </div>
            </tmpl_if>
            <tmpl_if name="is_reseller">
                <div class="ctrlHolder">
                    <label for="client_group_id">{tmpl_var name='client_txt'}</label>
                    <select name="client_group_id" id="client_group_id" class="selectInput">
                        {tmpl_var name='client_group_id'}
                    </select>
                </div>
            </tmpl_if>
            <div class="ctrlHolder">
                <label for="database_user">{tmpl_var name='database_user_txt'}</label>
                <p class="prefix">{tmpl_var name='database_user_prefix'}</p>
                <input name="database_user" id="database_user" value="{tmpl_var name='database_user'}" size="30" maxlength="255" type="text" class="textInput formLengthHalf" />
            </div>
            <div class="ctrlHolder">
                <label for="database_password">{tmpl_var name='database_password_txt'}</label>
                <input name="database_password" id="database_password" value="{tmpl_var name='database_password'}" size="30" maxlength="255" type="password" class="textInput formLengthHalf" onkeyup="pass_check(this.value);checkPassMatch('database_password','repeat_password');" />&nbsp;<a href="javascript:void(0);" onClick="generatePassword('database_password','repeat_password');">{tmpl_var name='generate_password_txt'}</a>
            </div>
            <div class="ctrlHolder">
                <p class="label">{tmpl_var name='password_strength_txt'}</p>
                <div id="passBar"></div>
                <p class="formHint"><span id="passText">&nbsp;</span></p>
            </div>
            <div class="ctrlHolder">
                <label for="repeat_password">{tmpl_var name='repeat_password_txt'}</label>
                <input name="repeat_password" id="repeat_password" value="" size="15" maxlength="100" type="password" class="textInput" style="width:100px;"  onkeyup="checkPassMatch('database_password','repeat_password');" />
            </div>
            <div id="confirmpasswordError" style="display:none;" class="confirmpassworderror">{tmpl_var name='password_mismatch_txt'}</div>
            <div id="confirmpasswordOK" style="display:none;" class="confirmpasswordok">{tmpl_var name='password_match_txt'}</div>
        </fieldset>

        <input type="hidden" name="id" value="{tmpl_var name='id'}">

        <div class="buttonHolder buttons">
            <button class="positive iconstxt icoPositive" type="button" value="{tmpl_var name='btn_save_txt'}" onClick="submitForm('pageForm','sites/database_user_edit.php');"><span>{tmpl_var name='btn_save_txt'}</span></button>
            <button class="negative iconstxt icoNegative" type="button" value="{tmpl_var name='btn_cancel_txt'}" onClick="loadContent('sites/database_user_list.php');"><span>{tmpl_var name='btn_cancel_txt'}</span></button>
        </div>
    </div>

</div>

 interface/web/sites/templates/database_user_list.htm

New file
@@ -0,0 +1,51 @@
<h2><tmpl_var name="list_head_txt"></h2>
<p><tmpl_var name="list_desc_txt"></p>

<div class="panel panel_list_database">

    <div class="pnl_toolsarea">
        <fieldset><legend>{tmpl_var name="toolsarea_head_txt"}</legend>
            <div class="buttons">
                <button class="iconstxt icoAdd" type="button" onClick="loadContent('sites/database_user_edit.php');">
                    <span>{tmpl_var name="add_new_record_txt"}</span>
                </button>
            </div>
        </fieldset>
    </div>

    <div class="pnl_listarea">
        <fieldset><legend><tmpl_var name="list_head_txt"></legend>
            <table class="list">
                <thead>
                    <tr>
                        <th class="tbl_col_database_user" scope="col"><tmpl_var name="database_user_txt"></th>
                        <th class="tbl_col_limit" scope="col">{tmpl_var name='search_limit'}</th>
                    </tr>
                    <tr>
                        <td class="tbl_col_database_user"><input type="text" name="search_database_user" value="{tmpl_var name='search_database_user'}"/></td>
                        <td class="tbl_col_buttons"><div class="buttons"><button type="button" class="icons16 icoFilter" name="Filter" id="Filter" value="{tmpl_var name="filter_txt"}" onClick="submitForm('pageForm','sites/database_user_list.php');"><span>{tmpl_var name="filter_txt"}</span></button></div></td>
                    </tr>
                </thead>
                <tbody>
                    <tmpl_loop name="records">
                        <tr class="tbl_row_<tmpl_if name='__EVEN__'}even<tmpl_else>uneven</tmpl_if>">
                            <td class="tbl_col_database_user"><a href="#" onClick="loadContent('sites/database_user_edit.php?id={tmpl_var name='id'}');">{tmpl_var name="database_user"}</a></td>
                            <td class="tbl_col_buttons">
                                <div class="buttons icons16">    
                                    <a class="icons16 icoEdit" href="javascript: loadContent('sites/database_user_edit.php?id={tmpl_var name='id'}');"><span>{tmpl_var name='edit_txt'}</span></a>
                                    <a class="icons16 icoDelete" href="javascript: del_record('sites/database_user_del.php?id={tmpl_var name='id'}&phpsessid={tmpl_var name='phpsessid'}','{tmpl_var name='delete_confirmation'}');"><span>{tmpl_var name='delete_txt'}</span></a>
                                </div>
                            </td>
                        </tr>
                    </tmpl_loop>
                </tbody>
                <tfoot>
                    <tr>
                        <td class="tbl_footer tbl_paging" colspan="2"><tmpl_var name="paging"></td>
                    </tr>
                </tfoot>
            </table>
        </fieldset>
    </div>

</div>

 server/mods-available/database_module.inc.php

@@ -34,7 +34,10 @@
    var $class_name = 'database_module';
    var $actions_available = array(    'database_insert',
                                    'database_update',
                                    'database_delete'
                                    'database_delete',
                                    'database_user_insert',
                                    'database_user_update',
                                    'database_user_delete'
                                    );
    
    //* This function is called during ispconfig installation to determine
@@ -75,7 +78,7 @@
        */
        
        $app->modules->registerTableHook('web_database','database_module','process');
        //$app->modules->registerTableHook('web_database_user','database_module','process');
        $app->modules->registerTableHook('web_database_user','database_module','process');
        
        // Register service
        //$app->services->registerService('httpd','web_module','restartHttpd');
@@ -96,13 +99,13 @@
                if($action == 'u') $app->plugins->raiseEvent('database_update',$data);
                if($action == 'd') $app->plugins->raiseEvent('database_delete',$data);
            break;
            /*
			
            case 'web_database_user':
                if($action == 'i') $app->plugins->raiseEvent('database_user_insert',$data);
                if($action == 'u') $app->plugins->raiseEvent('database_user_update',$data);
                if($action == 'd') $app->plugins->raiseEvent('database_user_delete',$data);
            break;
            */
			
        } // end switch
    } // end function


 server/plugins-available/mysql_clientdb_plugin.inc.php

@@ -64,14 +64,14 @@
        $app->plugins->registerEvent('database_delete',$this->plugin_name,'db_delete');
        
        //* Database users
        //$app->plugins->registerEvent('database_user_insert',$this->plugin_name,'db_user_insert');
        //$app->plugins->registerEvent('database_user_update',$this->plugin_name,'db_user_update');
        //$app->plugins->registerEvent('database_user_delete',$this->plugin_name,'db_user_delete');
        $app->plugins->registerEvent('database_user_insert',$this->plugin_name,'db_user_insert');
        $app->plugins->registerEvent('database_user_update',$this->plugin_name,'db_user_update');
        $app->plugins->registerEvent('database_user_delete',$this->plugin_name,'db_user_delete');
        
        
    }
    
  function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '') {
  function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '', $user_read_only = false) {
      global $app;
      
      $action = strtoupper($action);
@@ -105,9 +105,9 @@
          if($valid == false) continue;
          
          if($action == 'GRANT') {
              if(!$link->query("GRANT ALL ON ".$link->escape_string($database_name).".* TO '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."';")) $success = false;
              if(!$link->query("GRANT " . ($user_read_only ? "SELECT" : "ALL") . " ON ".$link->escape_string($database_name).".* TO '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."';")) $success = false;
          } elseif($action == 'REVOKE') {
              //mysql_query("REVOKE ALL PRIVILEGES ON ".mysql_real_escape_string($database_name,$link).".* FROM '".mysql_real_escape_string($database_user,$link)."';",$link);
              if(!$link->query("REVOKE ALL PRIVILEGES ON ".$link->escape_string($database_name).".* FROM '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."';")) $success = false;
          } elseif($action == 'DROP') {
              if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host';")) $success = false;
          } elseif($action == 'RENAME') {
@@ -129,11 +129,6 @@
                return;
            }
            
            if($data['new']['database_user'] == 'root') {
                $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                return;
            }
		
            //* Connect to the database
            $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
            if ($link->connect_error) {
@@ -158,13 +153,26 @@
            // Create the database user if database is active
            if($data['new']['active'] == 'y') {
                
                if($data['new']['remote_access'] == 'y') {
          $this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
                }
				
                $db_host = 'localhost';
                $link->query("GRANT ALL ON `".str_replace(array('_','%'),array('\\_','\\%'),$link->escape_string($data['new']['database_name']))."`.* TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($data['new']['database_password'])."';");

                // get the users for this database
                $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");
                
                $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_ro_user_id']) . "'");
                
                $host_list = '';
                if($data['new']['remote_access'] == 'y') {
                    $host_list = $data['new']['remote_ips'];
                }
                if($host_list != '') $host_list .= ',';
                $host_list .= 'localhost';
                
                if($db_user) {
                    if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
                }
                if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
                    if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
                }
                
            }
            
@@ -182,11 +190,6 @@
                return;
            }
            
            if($data['new']['database_user'] == 'root') {
                $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                return;
            }
			
            //* Connect to the database
            $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
            if ($link->connect_error) {
@@ -194,41 +197,66 @@
                return;
            }
            
            // Create the database user if database was disabled before
            // get the users for this database
            $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");
            
            $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_ro_user_id']) . "'");
            
            $host_list = '';
            if($data['new']['remote_access'] == 'y') {
                $host_list = $data['new']['remote_ips'];
            }
            if($host_list != '') $host_list .= ',';
            $host_list .= 'localhost';
            
            // Create the database user if database was disabled before
            if($data['new']['active'] == 'y' && $data['old']['active'] == 'n') {
				
                if($data['new']['remote_access'] == 'y') {
                  $this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
                }
				
                $db_host = 'localhost';
                $link->query("GRANT ALL ON `".str_replace(array('_','%'),array('\\_','\\%'),$link->escape_string($data['new']['database_name']))."`.* TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($data['new']['database_password'])."';");
				
                // mysql_query("GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"],$link)."';",$link);
                //echo "GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"]).".* TO '".mysql_real_escape_string($data["new"]["database_user"])."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"])."';";
                if($db_user) {
                    if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
                }
                if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
                    if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
                }
            } else if($data['new']['active'] == 'n' && $data['old']['active'] == 'y') { // revoke database user, if inactive
                if($db_user) {
                    if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
                }
                if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
                    if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link);
                }
            }
			
            // Remove database user, if inactive
            if($data['new']['active'] == 'n' && $data['old']['active'] == 'y') {
				
                if($data['old']['remote_access'] == 'y') {
          $this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link);
                }
				
                $db_host = 'localhost';
                $link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';");
                //mysql_query("REVOKE ALL PRIVILEGES ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* FROM '".mysql_real_escape_string($data["new"]["database_user"],$link)."';",$link);
            }
			
            //* Rename User
            if($data['new']['database_user'] != $data['old']['database_user']) {
                $db_host = 'localhost';
                $link->query("RENAME USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host' TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host'");
                if($data['old']['remote_access'] == 'y') {
                    $this->process_host_list('RENAME', '', $data['old']['database_user'], '', $data['new']['remote_ips'], $link, $data['new']['database_user']);
                }
                $app->log('Renaming MySQL user: '.$data['old']['database_user'].' to '.$data['new']['database_user'],LOGLEVEL_DEBUG);
            }
            
            //* selected Users have changed
            if($data['new']['database_user_id'] != $data['old']['database_user_id']) {
                if($data['old']['database_user_id'] && $data['old']['database_user_id'] != $data['new']['database_ro_user_id']) {
                    $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
                    if($old_db_user) {
                        if($old_db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        else $this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $host_list, $link);
                    }
                }
                if($db_user) {
                    if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
                }
            }
            if($data['new']['database_ro_user_id'] != $data['old']['database_ro_user_id']) {
                if($data['old']['database_ro_user_id'] && $data['old']['database_ro_user_id'] != $data['new']['database_user_id']) {
                    $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_ro_user_id']) . "'");
                    if($old_db_user) {
                        if($old_db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        else $this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $host_list, $link);
                    }
                }
                if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
                    if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
                }
            }
            
            //* Remote access option has changed.
            if($data['new']['remote_access'] != $data['old']['remote_access']) {
@@ -238,27 +266,43 @@
                
                //* set new priveliges
                if($data['new']['remote_access'] == 'y') {         
                    $this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
                    if($db_user) {
                        if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
                    }
                    if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
                        if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['new']['remote_ips'], $link, '', true);
                    }
                } else {
                    $this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link);
                    if($db_user) {
                        if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        else $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
                    }
                    if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
                        if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        else $this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['new']['remote_ips'], $link);
                    }
                }
                $app->log('Changing MySQL remote access privileges for database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
            } elseif($data['new']['remote_access'] == 'y' && $data['new']['remote_ips'] != $data['old']['remote_ips']) {
          //* Change remote access list
          $this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link);
          $this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
      }
                //* Change remote access list
                if($db_user) {
                    if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else {
                        $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link);
                        $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
                    }
                }
                if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
                    if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    else {
                        $this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link);
                        $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['new']['remote_ips'], $link, '', true);
                    }
                }
          }
      
            //* Change password
            if($data['new']['database_password'] != $data['old']['database_password']) {
                $db_host = 'localhost';
                $link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");

                if($data['new']['remote_access'] == 'y') {
                    $this->process_host_list('PASSWORD', '', $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'],$link);
                }
                $app->log('Changing MySQL user password for: '.$data['new']['database_user'],LOGLEVEL_DEBUG);
            }
            
            $link->query('FLUSH PRIVILEGES;');
            $link->close();
@@ -282,21 +326,6 @@
                return;
            }
            
            //* Get the db host setting for the access priveliges
            if($data['old']['remote_access'] == 'y') {
                 if($this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link)) {
            $app->log('Dropping MySQL user: '.$data['old']['database_user'],LOGLEVEL_DEBUG);
                } else {
                    $app->log('Error while dropping MySQL user: '.$data['old']['database_user'].' '.$link->error,LOGLEVEL_WARNING);
                }
            }
            $db_host = 'localhost';
            if($link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';")) {
                $app->log('Dropping MySQL user: '.$data['old']['database_user'],LOGLEVEL_DEBUG);
            } else {
                $app->log('Error while dropping MySQL user: '.$data['old']['database_user'].' '.$link->error,LOGLEVEL_WARNING);
            }
			
            if($link->query('DROP DATABASE '.$link->escape_string($data['old']['database_name']))) {
                $app->log('Dropping MySQL database: '.$data['old']['database_name'],LOGLEVEL_DEBUG);
            } else {
@@ -310,24 +339,100 @@
        
    }
    
    /*
	 
    function db_user_insert($event_name,$data) {
        global $app, $conf;
		
        // we have nothing to do here, stale user accounts are useless ;)
    }
    
    function db_user_update($event_name,$data) {
        global $app, $conf;
        
        if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
            $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
            return;
        }
        
        //* Connect to the database
        $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
        if ($link->connect_error) {
            $app->log('Unable to connect to mysql'.$link->connect_error,LOGLEVEL_ERROR);
            return;
        }
        
        
        if($data['old']['database_user'] == $data['new']['database_user'] && $data['old']['database_password'] == $data['new']['database_password']) {
            return;
        }
        
        
        $host_list = array('localhost');
        // get all databases this user was active for
        $db_list = $app->db->queryAllRecords("SELECT `remote_access`, `remote_ips` FROM `web_database` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
        foreach($db_list as $database) {
            if($database['remote_access'] != 'y') continue;
            
            if($database['remote_ips'] != '') $ips = explode(',', $database['remote_ips']);
            else $ips = array('%');
            
            foreach($ips as $ip) {
                $ip = trim($ip);
                if(!in_array($ip, $host_list)) $host_list[] = $ip;
            }
        }
        
        foreach($host_list as $db_host) {
            if($data['new']['database_user'] != $data['old']['database_user']) {
                $link->query("RENAME USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host' TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host'");
                $app->log('Renaming MySQL user: '.$data['old']['database_user'].' to '.$data['new']['database_user'],LOGLEVEL_DEBUG);
            }

            if($data['new']['database_password'] != $data['old']['database_password']) {
                $db_host = 'localhost';
                $link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");
                $app->log('Changing MySQL user password for: '.$data['new']['database_user'],LOGLEVEL_DEBUG);
            }
        }
        
        $link->query('FLUSH PRIVILEGES;');
        $link->close();
        
    }
    
    function db_user_delete($event_name,$data) {
        global $app, $conf;
        
        if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
            $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
            return;
        }
        
        //* Connect to the database
        $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
        if ($link->connect_error) {
            $app->log('Unable to connect to mysql'.$link->connect_error,LOGLEVEL_ERROR);
            return;
        }
        
        $host_list = array();
        // read all mysql users with this username
        $result = $link->query("SELECT `User`, `Host` FROM `mysql`.`user` WHERE `User` = '" . $link->escape_string($data['old']['database_user']) . "' AND `Create_user_priv` = 'N'"); // basic protection against accidently deleting system users like debian-sys-maint
        if($result) {
            while($row = $result->fetch_assoc()) {
                $host_list[] = $row['Host'];
            }
            $result->free();
        }
        
        foreach($host_list as $db_host) {
            if($link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';")) {
                $app->log('Dropping MySQL user: '.$data['old']['database_user'],LOGLEVEL_DEBUG);
            }
        }
        
        $link->query('FLUSH PRIVILEGES;');
        $link->close();
    }
    */
	

} // end class

?>