mysql_clientdb_plugin.inc.php 33.4 KB
Newer Older
tbrehm's avatar
tbrehm committed
1
2
3
<?php

/*
redray's avatar
redray committed
4
Copyright (c) 2007, Till Brehm, projektfarm Gmbh
tbrehm's avatar
tbrehm committed
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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.
*/

31
class mysql_clientdb_plugin {
tbrehm's avatar
tbrehm committed
32
	
33
34
	var $plugin_name = 'mysql_clientdb_plugin';
	var $class_name  = 'mysql_clientdb_plugin';
tbrehm's avatar
tbrehm committed
35
	
36
37
38
39
40
41
42
43
44
45
46
47
48
	//* This function is called during ispconfig installation to determine
	//  if a symlink shall be created for this plugin.
	function onInstall() {
		global $conf;
		
		if($conf['services']['db'] == true) {
			return true;
		} else {
			return false;
		}
		
	}
	
tbrehm's avatar
tbrehm committed
49
50
51
52
53
54
55
56
57
58
59
60
		
	/*
	 	This function is called when the plugin is loaded
	*/
	
	function onLoad() {
		global $app;
		
		/*
		Register for the events
		*/
		
61
		//* Databases
tbrehm's avatar
tbrehm committed
62
63
64
65
		$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');
		
66
		//* Database users
67
68
69
		$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');
70
		
tbrehm's avatar
tbrehm committed
71
72
73
		
	}
	
74
  function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '', $user_read_only = false) {
75
76
77
78
79
      global $app;
      
      $action = strtoupper($action);
      
      // set to all hosts if none given
80
      if(trim($host_list) == '') $host_list = '%';
81
82
      
      // process arrays and comma separated strings
83
      if(!is_array($host_list)) $host_list = explode(',', $host_list);
84
85
86
87
88
89
90
      
      $success = true;
      
      // loop through hostlist
      foreach($host_list as $db_host) {
          $db_host = trim($db_host);
          
91
92
          $app->log($action . ' for user ' . $database_user . ' at host ' . $db_host, LOGLEVEL_DEBUG);
          
93
94
          // check if entry is valid ip address
          $valid = true;
95
		  if($db_host == '%' || $db_host == 'localhost') {
96
97
		  	$valid = true;
		  } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
98
              $groups = explode('.', $db_host);
99
100
101
102
103
104
105
106
107
108
              foreach($groups as $group){
                if($group<0 OR $group>255)
                $valid=false;
              }
          } else {
              $valid = false;
          }
          
          if($valid == false) continue;
          
109
          if($action == 'GRANT') {
110
              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;
111
              $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);
112
          } elseif($action == 'REVOKE') {
113
              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;
114
          } elseif($action == 'DROP') {
115
              if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host';")) $success = false;
116
          } elseif($action == 'RENAME') {
117
              if(!$link->query("RENAME USER '".$link->escape_string($database_user)."'@'$db_host' TO '".$link->escape_string($database_rename_user)."'@'$db_host'")) $success = false;
118
          } elseif($action == 'PASSWORD') {
119
              if(!$link->query("SET PASSWORD FOR '".$link->escape_string($database_user)."'@'$db_host' = '".$link->escape_string($database_password)."';")) $success = false;
120
121
122
123
124
          }
      }
      
      return $success;
  }
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
  
	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);
	}
tbrehm's avatar
tbrehm committed
166
167
168
169
	
	function db_insert($event_name,$data) {
		global $app, $conf;
		
170
		if($data['new']['type'] == 'mysql') {
171
			if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
tbrehm's avatar
tbrehm committed
172
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
tbrehm's avatar
tbrehm committed
173
				return;
tbrehm's avatar
tbrehm committed
174
			}
175
			
tbrehm's avatar
tbrehm committed
176
			//* Connect to the database
177
			$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
178
			if ($link->connect_error) {
179
				$app->log('Unable to connect to mysql'.$link->connect_error,LOGLEVEL_ERROR);
180
				return;
tbrehm's avatar
tbrehm committed
181
			}
redray's avatar
redray committed
182
183

			// Charset for the new table
184
185
			if($data['new']['database_charset'] != '') {
        $query_charset_table = ' DEFAULT CHARACTER SET '.$data['new']['database_charset'];
redray's avatar
redray committed
186
187
188
189
			} else {
        $query_charset_table = '';
			}

tbrehm's avatar
tbrehm committed
190
			//* Create the new database
191
			if ($link->query('CREATE DATABASE '.$link->escape_string($data['new']['database_name']).$query_charset_table)) {
192
				$app->log('Created MySQL database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
tbrehm's avatar
tbrehm committed
193
			} else {
194
				$app->log('Unable to create the database: '.$link->error,LOGLEVEL_WARNING);
tbrehm's avatar
tbrehm committed
195
196
			}
			
197
			// Create the database user if database is active
198
			if($data['new']['active'] == 'y') {
199
				
200
201
202
203
204
205
206
207
                // 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'];
208
                    if($host_list == '') $host_list = '%';
209
210
211
212
213
214
215
216
217
218
219
220
                }
                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);
                }
221
				
tbrehm's avatar
tbrehm committed
222
223
			}
			
224
225
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
226
227
228
229
230
231
		}
	}
	
	function db_update($event_name,$data) {
		global $app, $conf;
		
232
233
234
		// skip processing if database was and is inactive
		if($data['new']['active'] == 'n' && $data['old']['active'] == 'n') return;
		
235
		if($data['new']['type'] == 'mysql') {
236
			if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
tbrehm's avatar
tbrehm committed
237
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
238
				return;
tbrehm's avatar
tbrehm committed
239
			}
240
			
tbrehm's avatar
tbrehm committed
241
			//* Connect to the database
242
			$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
243
			if ($link->connect_error) {
244
				$app->log('Unable to connect to the database: '.$link->connect_error,LOGLEVEL_ERROR);
245
				return;
tbrehm's avatar
tbrehm committed
246
247
			}
			
248
249
            // 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']) . "'");
250
            $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']) . "'");
251
252
            
            $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']) . "'");
253
            $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']) . "'");
254
255
256
257
            
            $host_list = '';
            if($data['new']['remote_access'] == 'y') {
                $host_list = $data['new']['remote_ips'];
258
                if($host_list == '') $host_list = '%';
259
260
261
            }
            if($host_list != '') $host_list .= ',';
            $host_list .= 'localhost';
262
263
264
265
266
267
268
269
270
			
			// 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';
271
272
            
            // Create the database user if database was disabled before
273
			if($data['new']['active'] == 'y' && $data['old']['active'] == 'n') {
274
275
276
277
278
279
280
281
282
                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
283
284
                if($old_db_user) {
                    if($old_db_user['database_user'] == 'root'){
285
286
287
						$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    } else {
						// Find out users to drop and users to revoke
288
289
290
						$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);
291
292
293
294
295
						
						
						//$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);
					}
296
                }
297
298
                if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
                    if($old_db_ro_user['database_user'] == 'root'){
299
300
301
						$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    } else {
						// Find out users to drop and users to revoke
302
303
304
						$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);
305
306
307
308
						
						//$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);
					}
309
                }
310
311
312
313
				// Database is not active, so stop processing here
				$link->query('FLUSH PRIVILEGES;');
				$link->close();
				return;
tbrehm's avatar
tbrehm committed
314
			}
315
316
317
318
319
            
            //* 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) {
320
321
322
323
                        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
324
325
326
							$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);
327
328
329
330
						
							//$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);
						}
331
332
333
334
335
336
337
338
339
                    }
                }
                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']) {
340
341
                    if($old_db_ro_user) {
                        if($old_db_ro_user['database_user'] == 'root'){
342
343
344
							$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        } else {
							// Find out users to drop and users to revoke
345
346
347
							$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);
348
349
350
351
							
							//$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);
						}
352
353
354
355
356
357
358
                    }
                }
                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);
                }
            }
tbrehm's avatar
tbrehm committed
359
360
			
			//* Remote access option has changed.
361
			if($data['new']['remote_access'] != $data['old']['remote_access']) {
362
363
				
				//* revoke old priveliges
364
				//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);
365
366
				
				//* set new priveliges
367
				if($data['new']['remote_access'] == 'y') { 		
368
                    if($db_user) {
369
370
371
372
373
                        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);
						}
374
375
376
377
378
                    }
                    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);
                    }
tbrehm's avatar
tbrehm committed
379
				} else {
380
381
                    if($old_db_user) {
                        if($old_db_user['database_user'] == 'root'){
382
383
384
							$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        } else {
							// Find out users to drop and users to revoke
385
386
387
							$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);
388
389
390
391
							
							//$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);
						}
392
                    }
393
394
                    if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
                        if($old_db_ro_user['database_user'] == 'root'){
395
396
397
							$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                        } else {
							// Find out users to drop and users to revoke
398
399
400
							$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);
401
402
403
404
							
							//$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);
						}
405
                    }
tbrehm's avatar
tbrehm committed
406
				}
407
408
				$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']) {
409
                //* Change remote access list
410
411
412
413
414
415
416
417
418
419
                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);
                    }
                }
420
                if($db_user) {
421
422
423
                    if($db_user['database_user'] == 'root'){
						$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    } else {
424
425
426
                        $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
                    }
                }
427
428
429
430
431
432
433
434
435
436
437
438
                
                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);
                    }
                }
                
439
                if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
440
441
442
                    if($db_ro_user['database_user'] == 'root'){
						$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
                    } else {
443
444
445
                        $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);
                    }
                }
446
			}
447
      
tbrehm's avatar
tbrehm committed
448
			
449
450
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
451
452
453
454
455
456
457
		}
		
	}
	
	function db_delete($event_name,$data) {
		global $app, $conf;
		
458
		if($data['old']['type'] == 'mysql') {
459
			if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
tbrehm's avatar
tbrehm committed
460
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
461
				return;
tbrehm's avatar
tbrehm committed
462
463
464
			}
		
			//* Connect to the database
465
466
467
			$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);
468
				return;
tbrehm's avatar
tbrehm committed
469
470
			}
			
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
			$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);
            }
            
            
493
			if($link->query('DROP DATABASE '.$link->escape_string($data['old']['database_name']))) {
494
				$app->log('Dropping MySQL database: '.$data['old']['database_name'],LOGLEVEL_DEBUG);
495
			} else {
496
				$app->log('Error while dropping MySQL database: '.$data['old']['database_name'].' '.$link->error,LOGLEVEL_WARNING);
497
			}
tbrehm's avatar
tbrehm committed
498
			
499
500
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
501
502
503
504
505
		}
		
		
	}
	
506
	 
507
508
	function db_user_insert($event_name,$data) {
		global $app, $conf;
509
		// we have nothing to do here, stale user accounts are useless ;)
510
	}
tbrehm's avatar
tbrehm committed
511
	
512
513
514
	function db_user_update($event_name,$data) {
		global $app, $conf;
		
515
516
517
518
519
520
521
522
523
524
525
526
527
        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;
        }
        
        
528
        if($data['old']['database_user'] == $data['new']['database_user'] && ($data['old']['database_password'] == $data['new']['database_password'] || $data['new']['database_password'] == '')) {
529
530
531
532
533
534
535
            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']) . "'");
536
537
        if(count($db_list) < 1) return; // nothing to do on this server for this db user
        
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
        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);
			}

556
			if($data['new']['database_password'] != $data['old']['database_password'] && $data['new']['database_password'] != '') {
557
				$link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");
558
				$app->log('Changing MySQL user password for: '.$data['new']['database_user'].'@'.$db_host,LOGLEVEL_DEBUG);
559
560
561
562
563
564
			}
        }
        
        $link->query('FLUSH PRIVILEGES;');
        $link->close();
        
565
566
567
568
569
	}
	
	function db_user_delete($event_name,$data) {
		global $app, $conf;
		
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
        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();
600
	}
tbrehm's avatar
tbrehm committed
601
602
} // end class

603
?>