From aa370627b211a51dc46891cfa4b6e3d2ef3e52db Mon Sep 17 00:00:00 2001
From: mcramer <m.cramer@pixcept.de>
Date: Tue, 16 Jul 2013 10:45:17 -0400
Subject: [PATCH] - Fixed FS#2924 - the month will not set automatically in the autoresponder by click now Along with this fixed some display problems with the combo boxes introduced in 3.0.5. Some fields were not correctly displayed with the predefined values if value and text of the underlying option element differ.
---
server/plugins-available/mysql_clientdb_plugin.inc.php | 565 +++++++++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 454 insertions(+), 111 deletions(-)
diff --git a/server/plugins-available/mysql_clientdb_plugin.inc.php b/server/plugins-available/mysql_clientdb_plugin.inc.php
index eaf85e7..b4fa6d3 100644
--- a/server/plugins-available/mysql_clientdb_plugin.inc.php
+++ b/server/plugins-available/mysql_clientdb_plugin.inc.php
@@ -58,151 +58,396 @@
Register for the events
*/
- //* Mailboxes
+ //* Databases
$app->plugins->registerEvent('database_insert',$this->plugin_name,'db_insert');
$app->plugins->registerEvent('database_update',$this->plugin_name,'db_update');
$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');
+
}
+ 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);
+
+ // set to all hosts if none given
+ if(trim($host_list) == '') $host_list = '%';
+
+ // process arrays and comma separated strings
+ if(!is_array($host_list)) $host_list = explode(',', $host_list);
+
+ $success = true;
+
+ // loop through hostlist
+ foreach($host_list as $db_host) {
+ $db_host = trim($db_host);
+
+ $app->log($action . ' for user ' . $database_user . ' at host ' . $db_host, LOGLEVEL_DEBUG);
+
+ // check if entry is valid ip address
+ $valid = true;
+ if($db_host == '%' || $db_host == 'localhost') {
+ $valid = true;
+ } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
+ $groups = explode('.', $db_host);
+ foreach($groups as $group){
+ if($group<0 OR $group>255)
+ $valid=false;
+ }
+ } else {
+ $valid = false;
+ }
+
+ if($valid == false) continue;
+
+ if($action == 'GRANT') {
+ 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;
+ $app->log("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? " . ($success ? 'yes' : 'no'), LOGLEVEL_DEBUG);
+ } elseif($action == 'REVOKE') {
+ 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') {
+ if(!$link->query("RENAME USER '".$link->escape_string($database_user)."'@'$db_host' TO '".$link->escape_string($database_rename_user)."'@'$db_host'")) $success = false;
+ } elseif($action == 'PASSWORD') {
+ if(!$link->query("SET PASSWORD FOR '".$link->escape_string($database_user)."'@'$db_host' = '".$link->escape_string($database_password)."';")) $success = false;
+ }
+ }
+
+ return $success;
+ }
+
+ function drop_or_revoke_user($database_id, $user_id, $host_list){
+ global $app;
+
+ // set to all hosts if none given
+ if(trim($host_list) == '') $host_list = '%';
+
+ $db_user_databases = $app->db->queryAllRecords("SELECT * FROM web_database WHERE (database_user_id = ".$user_id." OR database_ro_user_id = ".$user_id.") AND active = 'y' AND database_id != ".$database_id);
+ $db_user_host_list = array();
+ if(is_array($db_user_databases) && !empty($db_user_databases)){
+ foreach($db_user_databases as $db_user_database){
+ if($db_user_database['remote_access'] == 'y'){
+ if($db_user_database['remote_ips'] == ''){
+ $db_user_host_list[] = '%';
+ } else {
+ $tmp_remote_ips = explode(',', $db_user_database['remote_ips']);
+ if(is_array($tmp_remote_ips) && !empty($tmp_remote_ips)){
+ foreach($tmp_remote_ips as $tmp_remote_ip){
+ $tmp_remote_ip = trim($tmp_remote_ip);
+ if($tmp_remote_ip != '') $db_user_host_list[] = $tmp_remote_ip;
+ }
+ }
+ unset($tmp_remote_ips);
+ }
+ }
+ $db_user_host_list[] = 'localhost';
+ }
+ }
+ $host_list_arr = explode(',', $host_list);
+ //print_r($host_list_arr);
+ $drop_hosts = array_diff($host_list_arr, $db_user_host_list);
+ //print_r($drop_hosts);
+ $revoke_hosts = array_diff($host_list_arr, $drop_hosts);
+ //print_r($revoke_hosts);
+
+ $drop_host_list = implode(',', $drop_hosts);
+ $revoke_host_list = implode(',', $revoke_hosts);
+ //echo $drop_host_list."\n";
+ //echo $revoke_host_list."\n";
+ return array('revoke_hosts' => $revoke_host_list, 'drop_hosts' => $drop_host_list);
+ }
function db_insert($event_name,$data) {
global $app, $conf;
- if($data["new"]["type"] == 'mysql') {
+ if($data['new']['type'] == 'mysql') {
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 = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password);
- if (!$link) {
- $app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
+ $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;
}
// Charset for the new table
- if($data["new"]["database_charset"] != '') {
- $query_charset_table = ' DEFAULT CHARACTER SET '.$data["new"]["database_charset"];
+ if($data['new']['database_charset'] != '') {
+ $query_charset_table = ' DEFAULT CHARACTER SET '.$data['new']['database_charset'];
} else {
$query_charset_table = '';
}
//* Create the new database
- if (mysql_query('CREATE DATABASE '.mysql_real_escape_string($data["new"]["database_name"]).$query_charset_table,$link)) {
- $app->log('Created MySQL database: '.$data["new"]["database_name"],LOGLEVEL_DEBUG);
+ if ($link->query('CREATE DATABASE '.$link->escape_string($data['new']['database_name']).$query_charset_table)) {
+ $app->log('Created MySQL database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
} else {
- $app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
+ $app->log('Unable to create the database: '.$link->error,LOGLEVEL_WARNING);
}
// Create the database user if database is active
- if($data["new"]["active"] == 'y') {
+ if($data['new']['active'] == 'y') {
- if($data["new"]["remote_access"] == 'y') {
- $db_host = '%';
- 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);
- }
-
- $db_host = 'localhost';
- 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);
-
+ // 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 = '%';
+ }
+ 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);
+ }
}
- mysql_query("FLUSH PRIVILEGES;",$link);
- mysql_close($link);
+ $link->query('FLUSH PRIVILEGES;');
+ $link->close();
}
}
function db_update($event_name,$data) {
global $app, $conf;
- if($data["new"]["type"] == 'mysql') {
+ // skip processing if database was and is inactive
+ if($data['new']['active'] == 'n' && $data['old']['active'] == 'n') return;
+
+ if($data['new']['type'] == 'mysql') {
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 = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password);
- if (!$link) {
- $app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
+ $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
+ if ($link->connect_error) {
+ $app->log('Unable to connect to the database: '.$link->connect_error,LOGLEVEL_ERROR);
return;
}
- // Create the database user if database was disabled before
- if($data["new"]["active"] == 'y' && $data["old"]["active"] == 'n') {
-
- if($data["new"]["remote_access"] == 'y') {
- $db_host = '%';
- 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);
- }
-
- $db_host = 'localhost';
- 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);
-
- // 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"])."';";
- }
+ // 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']) . "'");
+ $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']) . "'");
+
+ $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']) . "'");
+ $old_db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_ro_user_id']) . "'");
+
+ $host_list = '';
+ if($data['new']['remote_access'] == 'y') {
+ $host_list = $data['new']['remote_ips'];
+ if($host_list == '') $host_list = '%';
+ }
+ if($host_list != '') $host_list .= ',';
+ $host_list .= 'localhost';
- // Remove database user, if inactive
- if($data["new"]["active"] == 'n' && $data["old"]["active"] == 'y') {
-
- if($data["old"]["remote_access"] == 'y') {
- $db_host = '%';
- mysql_query("DROP USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host';",$link);
- }
-
- $db_host = 'localhost';
- mysql_query("DROP USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host';",$link);
-
-
- //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);
+ // REVOKES and DROPS have to be done on old host list, not new host list
+ $old_host_list = '';
+ if($data['old']['remote_access'] == 'y') {
+ $old_host_list = $data['old']['remote_ips'];
+ if($old_host_list == '') $old_host_list = '%';
+ }
+ if($old_host_list != '') $old_host_list .= ',';
+ $old_host_list .= 'localhost';
+
+ // Create the database user if database was disabled before
+ if($data['new']['active'] == 'y' && $data['old']['active'] == 'n') {
+ 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($old_db_user) {
+ if($old_db_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+
+
+ //$this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link);
+ //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link);
+ }
+ }
+ if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
+ if($old_db_ro_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+
+ //$this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $old_host_list, $link);
+ //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $old_host_list, $link);
+ }
+ }
+ // Database is not active, so stop processing here
+ $link->query('FLUSH PRIVILEGES;');
+ $link->close();
+ return;
}
-
- //* Rename User
- if($data["new"]["database_user"] != $data["old"]["database_user"]) {
- $db_host = 'localhost';
- mysql_query("RENAME USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host' TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host'",$link);
- if($data["old"]["remote_access"] == 'y') {
- $db_host = '%';
- mysql_query("RENAME USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host' TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host'",$link);
- }
- $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']) {
+ if($old_db_user) {
+ if($old_db_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+
+ //$this->process_host_list('DROP', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link);
+ //$this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_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']) {
+ if($old_db_ro_user) {
+ if($old_db_ro_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+
+ //$this->process_host_list('DROP', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link);
+ //$this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_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"]) {
+ if($data['new']['remote_access'] != $data['old']['remote_access']) {
//* revoke old priveliges
//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);
//* set new priveliges
- $db_host = '%';
- if($data["new"]["remote_access"] == 'y') {
- 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);
+ if($data['new']['remote_access'] == 'y') {
+ 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 {
- mysql_query("DROP USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host';",$link);
+ if($old_db_user) {
+ if($old_db_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+
+ //$this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link);
+ //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link);
+ }
+ }
+ if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
+ if($old_db_ro_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+
+ //$this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link);
+ //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link);
+ }
+ }
}
- $app->log('Changing mysql remote access priveliges for database: '.$data["new"]["database_name"],LOGLEVEL_DEBUG);
+ $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
+ if($old_db_user) {
+ if($old_db_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $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($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
+ if($old_db_ro_user['database_user'] == 'root'){
+ $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
+ } else {
+ // Find out users to drop and users to revoke
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $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);
+ }
+ }
}
+
- //* Change password
- if($data["new"]["database_password"] != $data["old"]["database_password"]) {
- $db_host = 'localhost';
- mysql_query("SET PASSWORD FOR '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' = PASSWORD('".mysql_real_escape_string($data["new"]["database_password"],$link)."');",$link);
-
- if($data["new"]["remote_access"] == 'y') {
- $db_host = '%';
- mysql_query("SET PASSWORD FOR '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' = PASSWORD('".mysql_real_escape_string($data["new"]["database_password"],$link)."');",$link);
- }
- $app->log('Changing mysql user password for: '.$data["new"]["database_user"],LOGLEVEL_DEBUG);
- }
-
- mysql_query("FLUSH PRIVILEGES;",$link);
- mysql_close($link);
+ $link->query('FLUSH PRIVILEGES;');
+ $link->close();
}
}
@@ -210,51 +455,149 @@
function db_delete($event_name,$data) {
global $app, $conf;
- if($data["old"]["type"] == 'mysql') {
+ if($data['old']['type'] == 'mysql') {
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 = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password);
- if (!$link) {
- $app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_ERROR);
+ $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;
}
- //* Get the db host setting for the access priveliges
- if($data["old"]["remote_access"] == 'y') {
- $db_host = '%';
- if(mysql_query("DROP USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host';",$link)) {
- $app->log('Dropping mysql user: '.$data["old"]["database_user"],LOGLEVEL_DEBUG);
- } else {
- $app->log('Error while dropping mysql user: '.$data["old"]["database_user"].' '.mysql_error($link),LOGLEVEL_ERROR);
- }
- }
- $db_host = 'localhost';
- if(mysql_query("DROP USER '".mysql_real_escape_string($data["old"]["database_user"],$link)."'@'$db_host';",$link)) {
- $app->log('Dropping mysql user: '.$data["old"]["database_user"],LOGLEVEL_DEBUG);
+ $old_host_list = '';
+ if($data['old']['remote_access'] == 'y') {
+ $old_host_list = $data['old']['remote_ips'];
+ if($old_host_list == '') $old_host_list = '%';
+ }
+ if($old_host_list != '') $old_host_list .= ',';
+ $old_host_list .= 'localhost';
+
+ if($data['old']['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_user_id']) . "'");
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+ }
+ if($data['old']['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_ro_user_id']) . "'");
+ $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
+ }
+
+
+ if($link->query('DROP DATABASE '.$link->escape_string($data['old']['database_name']))) {
+ $app->log('Dropping MySQL database: '.$data['old']['database_name'],LOGLEVEL_DEBUG);
} else {
- $app->log('Error while dropping mysql user: '.$data["old"]["database_user"].' '.mysql_error($link),LOGLEVEL_ERROR);
+ $app->log('Error while dropping MySQL database: '.$data['old']['database_name'].' '.$link->error,LOGLEVEL_WARNING);
}
- if(mysql_query('DROP DATABASE '.mysql_real_escape_string($data["old"]["database_name"],$link),$link)) {
- $app->log('Dropping mysql database: '.$data["old"]["database_name"],LOGLEVEL_DEBUG);
- } else {
- $app->log('Error while dropping mysql database: '.$data["old"]["database_name"].' '.mysql_error($link),LOGLEVEL_ERROR);
- }
-
- mysql_query("FLUSH PRIVILEGES;",$link);
- mysql_close($link);
+ $link->query('FLUSH PRIVILEGES;');
+ $link->close();
}
}
+
+ 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'] || $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']) . "'");
+ if(count($db_list) < 1) return; // nothing to do on this server for this db user
+
+ 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'] && $data['new']['database_password'] != '') {
+ $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'].'@'.$db_host,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
-?>
\ No newline at end of file
+?>
--
Gitblit v1.9.1