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)

---
 server/plugins-available/mysql_clientdb_plugin.inc.php |  289 +++++++++++++++++++++++++++++++++++++++------------------
 1 files changed, 197 insertions(+), 92 deletions(-)

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