From 381520c8866a5f3be7e743e3ae16b6fb2988c495 Mon Sep 17 00:00:00 2001
From: mcramer <m.cramer@pixcept.de>
Date: Tue, 21 Aug 2012 13:51:27 -0400
Subject: [PATCH] Implemented FS#1448 - one database user name and multiple databases Bugfix on db-Class (datalog Update)
---
interface/web/sites/templates/database_user_admin_list.htm | 50 ++
interface/web/sites/templates/database_user_edit.htm | 54 ++
interface/web/sites/templates/database_edit.htm | 24
interface/web/sites/lib/module.conf.php | 3
interface/web/sites/form/database_user.tform.php | 99 ++++
interface/web/sites/database_del.php | 37 +
interface/web/sites/database_edit.php | 158 +++++-
interface/web/sites/lib/lang/de_database_list.lng | 2
interface/web/sites/database_user_del.php | 78 +++
interface/web/sites/lib/lang/de_database.lng | 8
server/plugins-available/mysql_clientdb_plugin.inc.php | 289 ++++++++---
install/sql/ispconfig3.sql | 26
server/mods-available/database_module.inc.php | 11
interface/web/sites/database_user_list.php | 65 ++
interface/web/sites/database_user_edit.php | 212 ++++++++
interface/web/sites/form/database.tform.php | 40
install/sql/incremental/upd_0039.sql | 36 +
interface/web/sites/lib/lang/en_database_user_admin_list.lng | 6
interface/lib/classes/db_mysql.inc.php | 2
interface/web/sites/templates/database_list.htm | 6
interface/web/sites/lib/lang/en_database.lng | 4
interface/web/sites/templates/database_user_list.htm | 51 ++
interface/web/sites/lib/lang/de_database_user_admin_list.lng | 6
interface/web/sites/lib/lang/en_database_user.lng | 23
interface/web/sites/lib/lang/de_database_user_list.lng | 5
interface/web/sites/list/database_user.list.php | 81 +++
interface/web/sites/lib/lang/de_database_user.lng | 23
interface/web/sites/lib/lang/en_database_user_list.lng | 5
interface/web/sites/list/database.list.php | 17
interface/web/sites/lib/remote.conf.php | 2
interface/web/sites/templates/database_admin_list.htm | 4
31 files changed, 1,244 insertions(+), 183 deletions(-)
diff --git a/install/sql/incremental/upd_0039.sql b/install/sql/incremental/upd_0039.sql
new file mode 100644
index 0000000..b5f6463
--- /dev/null
+++ b/install/sql/incremental/upd_0039.sql
@@ -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`;
+
diff --git a/install/sql/ispconfig3.sql b/install/sql/ispconfig3.sql
index 58e0178..5777459 100644
--- a/install/sql/ispconfig3.sql
+++ b/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`
--
diff --git a/interface/lib/classes/db_mysql.inc.php b/interface/lib/classes/db_mysql.inc.php
index 8354be6..e2232e8 100644
--- a/interface/lib/classes/db_mysql.inc.php
+++ b/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);
diff --git a/interface/web/sites/database_del.php b/interface/web/sites/database_del.php
index f5f8dbc..b1263b3 100644
--- a/interface/web/sites/database_del.php
+++ b/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();
?>
\ No newline at end of file
diff --git a/interface/web/sites/database_edit.php b/interface/web/sites/database_edit.php
index 7f675fa..d098625 100644
--- a/interface/web/sites/database_edit.php
+++ b/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;
diff --git a/interface/web/sites/database_user_del.php b/interface/web/sites/database_user_del.php
new file mode 100644
index 0000000..5ecdde5
--- /dev/null
+++ b/interface/web/sites/database_user_del.php
@@ -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();
+
+?>
\ No newline at end of file
diff --git a/interface/web/sites/database_user_edit.php b/interface/web/sites/database_user_edit.php
new file mode 100644
index 0000000..65bf255
--- /dev/null
+++ b/interface/web/sites/database_user_edit.php
@@ -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();
+
+?>
\ No newline at end of file
diff --git a/interface/web/sites/database_user_list.php b/interface/web/sites/database_user_list.php
new file mode 100644
index 0000000..52a48e4
--- /dev/null
+++ b/interface/web/sites/database_user_list.php
@@ -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();
+
+
+?>
\ No newline at end of file
diff --git a/interface/web/sites/form/database.tform.php b/interface/web/sites/form/database.tform.php
index b26a1e4..c8a7ac9 100644
--- a/interface/web/sites/form/database.tform.php
+++ b/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',
diff --git a/interface/web/sites/form/database_user.tform.php b/interface/web/sites/form/database_user.tform.php
new file mode 100644
index 0000000..f8cd38c
--- /dev/null
+++ b/interface/web/sites/form/database_user.tform.php
@@ -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
+ ##################################
+ )
+);
+
+
+?>
\ No newline at end of file
diff --git a/interface/web/sites/lib/lang/de_database.lng b/interface/web/sites/lib/lang/de_database.lng
index a10771f..36ac619 100644
--- a/interface/web/sites/lib/lang/de_database.lng
+++ b/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';
diff --git a/interface/web/sites/lib/lang/de_database_list.lng b/interface/web/sites/lib/lang/de_database_list.lng
index 62086c9..bae444c 100644
--- a/interface/web/sites/lib/lang/de_database_list.lng
+++ b/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';
diff --git a/interface/web/sites/lib/lang/de_database_user.lng b/interface/web/sites/lib/lang/de_database_user.lng
new file mode 100644
index 0000000..78884bc
--- /dev/null
+++ b/interface/web/sites/lib/lang/de_database_user.lng
@@ -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';
+?>
diff --git a/interface/web/sites/lib/lang/de_database_user_admin_list.lng b/interface/web/sites/lib/lang/de_database_user_admin_list.lng
new file mode 100644
index 0000000..cac3712
--- /dev/null
+++ b/interface/web/sites/lib/lang/de_database_user_admin_list.lng
@@ -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';
+?>
diff --git a/interface/web/sites/lib/lang/de_database_user_list.lng b/interface/web/sites/lib/lang/de_database_user_list.lng
new file mode 100644
index 0000000..ac80077
--- /dev/null
+++ b/interface/web/sites/lib/lang/de_database_user_list.lng
@@ -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';
+?>
diff --git a/interface/web/sites/lib/lang/en_database.lng b/interface/web/sites/lib/lang/en_database.lng
index 8a470c7..a494efc 100644
--- a/interface/web/sites/lib/lang/en_database.lng
+++ b/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.';
diff --git a/interface/web/sites/lib/lang/en_database_user.lng b/interface/web/sites/lib/lang/en_database_user.lng
new file mode 100644
index 0000000..6c4ee7c
--- /dev/null
+++ b/interface/web/sites/lib/lang/en_database_user.lng
@@ -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";
+?>
diff --git a/interface/web/sites/lib/lang/en_database_user_admin_list.lng b/interface/web/sites/lib/lang/en_database_user_admin_list.lng
new file mode 100644
index 0000000..4c17da1
--- /dev/null
+++ b/interface/web/sites/lib/lang/en_database_user_admin_list.lng
@@ -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';
+?>
\ No newline at end of file
diff --git a/interface/web/sites/lib/lang/en_database_user_list.lng b/interface/web/sites/lib/lang/en_database_user_list.lng
new file mode 100644
index 0000000..549374b
--- /dev/null
+++ b/interface/web/sites/lib/lang/en_database_user_list.lng
@@ -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';
+?>
\ No newline at end of file
diff --git a/interface/web/sites/lib/module.conf.php b/interface/web/sites/lib/module.conf.php
index 1e11450..d79d3f4 100644
--- a/interface/web/sites/lib/module.conf.php
+++ b/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,
diff --git a/interface/web/sites/lib/remote.conf.php b/interface/web/sites/lib/remote.conf.php
index b2d6ff6..4cdc6fc 100644
--- a/interface/web/sites/lib/remote.conf.php
+++ b/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';
diff --git a/interface/web/sites/list/database.list.php b/interface/web/sites/list/database.list.php
index 9b2111d..6163f37 100644
--- a/interface/web/sites/list/database.list.php
+++ b/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' => "");
diff --git a/interface/web/sites/list/database_user.list.php b/interface/web/sites/list/database_user.list.php
new file mode 100644
index 0000000..67796a8
--- /dev/null
+++ b/interface/web/sites/list/database_user.list.php
@@ -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' => "");
+
+
+?>
\ No newline at end of file
diff --git a/interface/web/sites/templates/database_admin_list.htm b/interface/web/sites/templates/database_admin_list.htm
index b6c90b5..fda3ba7 100644
--- a/interface/web/sites/templates/database_admin_list.htm
+++ b/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">
diff --git a/interface/web/sites/templates/database_edit.htm b/interface/web/sites/templates/database_edit.htm
index b8b4b3f..4207a78 100644
--- a/interface/web/sites/templates/database_edit.htm
+++ b/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');" /> <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> {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"> </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>
diff --git a/interface/web/sites/templates/database_list.htm b/interface/web/sites/templates/database_list.htm
index d3b9ac5..7b49e87 100644
--- a/interface/web/sites/templates/database_list.htm
+++ b/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">
diff --git a/interface/web/sites/templates/database_user_admin_list.htm b/interface/web/sites/templates/database_user_admin_list.htm
new file mode 100644
index 0000000..c1bee67
--- /dev/null
+++ b/interface/web/sites/templates/database_user_admin_list.htm
@@ -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>
\ No newline at end of file
diff --git a/interface/web/sites/templates/database_user_edit.htm b/interface/web/sites/templates/database_user_edit.htm
new file mode 100644
index 0000000..40e8c96
--- /dev/null
+++ b/interface/web/sites/templates/database_user_edit.htm
@@ -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');" /> <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"> </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>
\ No newline at end of file
diff --git a/interface/web/sites/templates/database_user_list.htm b/interface/web/sites/templates/database_user_list.htm
new file mode 100644
index 0000000..0a0ea1a
--- /dev/null
+++ b/interface/web/sites/templates/database_user_list.htm
@@ -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>
\ No newline at end of file
diff --git a/server/mods-available/database_module.inc.php b/server/mods-available/database_module.inc.php
index 199a6c7..c3a99b6 100644
--- a/server/mods-available/database_module.inc.php
+++ b/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
diff --git a/server/plugins-available/mysql_clientdb_plugin.inc.php b/server/plugins-available/mysql_clientdb_plugin.inc.php
index df8749a..98efd8c 100644
--- a/server/plugins-available/mysql_clientdb_plugin.inc.php
+++ b/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
?>
--
Gitblit v1.9.1