From 6508d0c5b766fe56e96741a61ab7a1ece4191f2d Mon Sep 17 00:00:00 2001
From: mcramer <m.cramer@pixcept.de>
Date: Fri, 17 May 2013 12:09:10 -0400
Subject: [PATCH] - Possible Fix for: FS#2918 - multiserver - remote access db passwd not changed , when changed access from IP to % and vice versa
---
server/plugins-available/mysql_clientdb_plugin.inc.php | 187 ++++++++++++++++++++++++++++++++++++++++------
1 files changed, 161 insertions(+), 26 deletions(-)
diff --git a/server/plugins-available/mysql_clientdb_plugin.inc.php b/server/plugins-available/mysql_clientdb_plugin.inc.php
index 98efd8c..393c3fb 100644
--- a/server/plugins-available/mysql_clientdb_plugin.inc.php
+++ b/server/plugins-available/mysql_clientdb_plugin.inc.php
@@ -88,9 +88,11 @@
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 == '%') {
+ 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);
@@ -106,6 +108,7 @@
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') {
@@ -119,6 +122,47 @@
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;
@@ -161,6 +205,7 @@
$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';
@@ -184,6 +229,9 @@
function db_update($event_name,$data) {
global $app, $conf;
+ // 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);
@@ -205,9 +253,19 @@
$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';
+
+ // 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') {
@@ -221,13 +279,36 @@
}
} 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_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['new']['database_id'], $data['new']['database_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $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($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);
+ if($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['new']['database_id'], $data['new']['database_ro_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $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['new']['database_name'], $db_ro_user['database_user'], $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;
}
//* selected Users have changed
@@ -235,8 +316,17 @@
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($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['new']['database_id'], $data['old']['database_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['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['new']['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) {
@@ -248,8 +338,17 @@
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($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['new']['database_id'], $data['old']['database_user_id'], $old_host_list);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['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['new']['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_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
@@ -267,8 +366,11 @@
//* set new priveliges
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_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);
@@ -276,32 +378,64 @@
}
} else {
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_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['new']['database_id'], $data['new']['database_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $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($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);
+ if($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['new']['database_id'], $data['new']['database_ro_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $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['new']['database_name'], $db_ro_user['database_user'], $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 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($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);
+ if($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['new']['database_id'], $data['new']['database_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
+ if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $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);
$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);
+ if($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['new']['database_id'], $data['new']['database_user_id'], $data['old']['remote_ips']);
+ if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $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['new']['database_name'], $db_ro_user['database_user'], $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);
$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);
}
}
- }
+ }
$link->query('FLUSH PRIVILEGES;');
@@ -361,7 +495,7 @@
}
- if($data['old']['database_user'] == $data['new']['database_user'] && $data['old']['database_password'] == $data['new']['database_password']) {
+ if($data['old']['database_user'] == $data['new']['database_user'] && ($data['old']['database_password'] == $data['new']['database_password'] || $data['new']['database_password'] == '')) {
return;
}
@@ -369,6 +503,8 @@
$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;
@@ -387,10 +523,9 @@
$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';
+ 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'],LOGLEVEL_DEBUG);
+ $app->log('Changing MySQL user password for: '.$data['new']['database_user'].'@'.$db_host,LOGLEVEL_DEBUG);
}
}
--
Gitblit v1.9.1