Pascal Dreissen
2016-07-08 f1193b43f4c9fd132741d30f03f0b35841011989
commit | author | age
d83fcf 1 <?php
T 2
3 /*
436ed8 4 Copyright (c) 2007, Till Brehm, projektfarm Gmbh
d83fcf 5 All rights reserved.
T 6
7 Redistribution and use in source and binary forms, with or without modification,
8 are permitted provided that the following conditions are met:
9
10     * Redistributions of source code must retain the above copyright notice,
11       this list of conditions and the following disclaimer.
12     * Redistributions in binary form must reproduce the above copyright notice,
13       this list of conditions and the following disclaimer in the documentation
14       and/or other materials provided with the distribution.
15     * Neither the name of ISPConfig nor the names of its contributors
16       may be used to endorse or promote products derived from this software without
17       specific prior written permission.
18
19 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
20 ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
21 WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
22 IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
23 INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
24 BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
25 DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
26 OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
27 NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
28 EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29 */
30
a61345 31 class mysql_clientdb_plugin {
7fe908 32
a61345 33     var $plugin_name = 'mysql_clientdb_plugin';
T 34     var $class_name  = 'mysql_clientdb_plugin';
7fe908 35
392450 36     //* This function is called during ispconfig installation to determine
T 37     //  if a symlink shall be created for this plugin.
38     function onInstall() {
39         global $conf;
7fe908 40
392450 41         if($conf['services']['db'] == true) {
T 42             return true;
43         } else {
44             return false;
45         }
7fe908 46
392450 47     }
7fe908 48
MC 49
d83fcf 50     /*
T 51          This function is called when the plugin is loaded
52     */
7fe908 53
d83fcf 54     function onLoad() {
T 55         global $app;
7fe908 56
d83fcf 57         /*
T 58         Register for the events
59         */
7fe908 60
acf18c 61         //* Databases
7fe908 62         $app->plugins->registerEvent('database_insert', $this->plugin_name, 'db_insert');
MC 63         $app->plugins->registerEvent('database_update', $this->plugin_name, 'db_update');
64         $app->plugins->registerEvent('database_delete', $this->plugin_name, 'db_delete');
65
acf18c 66         //* Database users
7fe908 67         $app->plugins->registerEvent('database_user_insert', $this->plugin_name, 'db_user_insert');
MC 68         $app->plugins->registerEvent('database_user_update', $this->plugin_name, 'db_user_update');
69         $app->plugins->registerEvent('database_user_delete', $this->plugin_name, 'db_user_delete');
70
71
d83fcf 72     }
7fe908 73
5512af 74     function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '', $user_access_mode = 'rw') {
cc6568 75         global $app;
5512af 76         
MB 77         if(!$user_access_mode) $user_access_mode = 'rw';
7fe908 78         $action = strtoupper($action);
MC 79
cc6568 80         // set to all hosts if none given
H 81         if(trim($host_list) == '') $host_list = '%';
7fe908 82
MC 83         // process arrays and comma separated strings
84         if(!is_array($host_list)) $host_list = explode(',', $host_list);
85
86         $success = true;
51569e 87         if(!preg_match('/\*[A-F0-9]{40}$/', $database_password)) {
MC 88                 $result = $link->query("SELECT PASSWORD('" . $link->escape_string($database_password) . "') as `crypted`");
89                 if($result) {
90                         $row = $result->fetch_assoc();
91                         $database_password = $row['crypted'];
92                         $result->free();
93                 }
94         }
6f97fc 95         
MB 96         $app->log("Calling $action for $database_name with access $user_access_mode and hosts " . implode(', ', $host_list), LOGLEVEL_DEBUG);
97         
7fe908 98         // loop through hostlist
MC 99         foreach($host_list as $db_host) {
100             $db_host = trim($db_host);
101
102             $app->log($action . ' for user ' . $database_user . ' at host ' . $db_host, LOGLEVEL_DEBUG);
103
104             // check if entry is valid ip address
105             $valid = true;
106             if($db_host == '%' || $db_host == 'localhost') {
107                 $valid = true;
2df8c0 108             } elseif(preg_match("/^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$/", $db_host)) {
7fe908 109                 $groups = explode('.', $db_host);
MC 110                 foreach($groups as $group){
111                     if($group<0 or $group>255)
112                         $valid=false;
113                 }
114             } else {
115                 $valid = false;
116             }
117
6f97fc 118             if($valid == false) {
MB 119                 $app->log("Invalid host " . $db_host . " for GRANT to " . $database_name, LOGLEVEL_DEBUG);
120                 continue;
121             }
5512af 122             
710dab 123             $grants = 'ALL PRIVILEGES';
5512af 124             if($user_access_mode == 'r') $grants = 'SELECT';
MB 125             elseif($user_access_mode == 'rd') $grants = 'SELECT, DELETE, ALTER, DROP';
126             
7fe908 127             if($action == 'GRANT') {
710dab 128                 if($user_access_mode == 'r' || $user_access_mode == 'rd') {
MB 129                     if(!$link->query("REVOKE ALL PRIVILEGES ON `".$link->escape_string($database_name)."`.* FROM '".$link->escape_string($database_user)."'@'$db_host'")) $success = false;
130                     $app->log("REVOKE ALL PRIVILEGES ON `".$link->escape_string($database_name)."`.* FROM '".$link->escape_string($database_user)."'@'$db_host' success? " . ($success ? 'yes' : 'no'), LOGLEVEL_DEBUG);
131                     $success = true;
132                 }
133                 
134                 if(!$link->query("GRANT " . $grants . " ON `".$link->escape_string($database_name)."`.* TO '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."'")) $success = false;
135                 $app->log("GRANT " . $grants . " 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);
7fe908 136             } elseif($action == 'REVOKE') {
710dab 137                 if(!$link->query("REVOKE ALL PRIVILEGES ON `".$link->escape_string($database_name)."`.* FROM '".$link->escape_string($database_user)."'@'$db_host'")) $success = false;
7fe908 138             } elseif($action == 'DROP') {
710dab 139                 if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host'")) $success = false;
7fe908 140             } elseif($action == 'RENAME') {
MC 141                 if(!$link->query("RENAME USER '".$link->escape_string($database_user)."'@'$db_host' TO '".$link->escape_string($database_rename_user)."'@'$db_host'")) $success = false;
142             } elseif($action == 'PASSWORD') {
710dab 143                 if(!$link->query("SET PASSWORD FOR '".$link->escape_string($database_user)."'@'$db_host' = '".$link->escape_string($database_password)."'")) $success = false;
7fe908 144             }
MC 145         }
146
147         return $success;
148     }
149
150     function drop_or_revoke_user($database_id, $user_id, $host_list){
151         global $app;
152
153         // set to all hosts if none given
154         if(trim($host_list) == '') $host_list = '%';
155
4f9dee 156         $db_user_databases = $app->db->queryAllRecords("SELECT * FROM web_database WHERE (database_user_id = ? OR database_ro_user_id = ?) AND active = 'y' AND database_id != ?", $user_id, $user_id, $database_id);
cc6568 157         $db_user_host_list = array();
H 158         if(is_array($db_user_databases) && !empty($db_user_databases)){
159             foreach($db_user_databases as $db_user_database){
160                 if($db_user_database['remote_access'] == 'y'){
161                     if($db_user_database['remote_ips'] == ''){
162                         $db_user_host_list[] = '%';
163                     } else {
164                         $tmp_remote_ips = explode(',', $db_user_database['remote_ips']);
165                         if(is_array($tmp_remote_ips) && !empty($tmp_remote_ips)){
166                             foreach($tmp_remote_ips as $tmp_remote_ip){
167                                 $tmp_remote_ip = trim($tmp_remote_ip);
168                                 if($tmp_remote_ip != '') $db_user_host_list[] = $tmp_remote_ip;
169                             }
170                         }
171                         unset($tmp_remote_ips);
172                     }
173                 }
174                 $db_user_host_list[] = 'localhost';
175             }
176         }
177         $host_list_arr = explode(',', $host_list);
178         //print_r($host_list_arr);
179         $drop_hosts = array_diff($host_list_arr, $db_user_host_list);
180         //print_r($drop_hosts);
181         $revoke_hosts = array_diff($host_list_arr, $drop_hosts);
182         //print_r($revoke_hosts);
7fe908 183
cc6568 184         $drop_host_list = implode(',', $drop_hosts);
H 185         $revoke_host_list = implode(',', $revoke_hosts);
186         //echo $drop_host_list."\n";
187         //echo $revoke_host_list."\n";
188         return array('revoke_hosts' => $revoke_host_list, 'drop_hosts' => $drop_host_list);
189     }
7fe908 190
MC 191     function db_insert($event_name, $data) {
d83fcf 192         global $app, $conf;
7fe908 193
663caf 194         if($data['new']['type'] == 'mysql') {
7fe908 195             if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
MC 196                 $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
7c99ef 197                 return;
d83fcf 198             }
7fe908 199
d83fcf 200             //* Connect to the database
1d8f7f 201             $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
a7cb2b 202             if ($link->connect_error) {
7fe908 203                 $app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR);
a61345 204                 return;
d83fcf 205             }
be9816 206
R 207             // Charset for the new table
663caf 208             if($data['new']['database_charset'] != '') {
7fe908 209                 $query_charset_table = ' DEFAULT CHARACTER SET '.$data['new']['database_charset'];
be9816 210             } else {
7fe908 211                 $query_charset_table = '';
be9816 212             }
R 213
d83fcf 214             //* Create the new database
1e8c9b 215             if ($link->query('CREATE DATABASE `'.$link->escape_string($data['new']['database_name']).'`'.$query_charset_table)) {
7fe908 216                 $app->log('Created MySQL database: '.$data['new']['database_name'], LOGLEVEL_DEBUG);
d83fcf 217             } else {
7fe908 218                 $app->log('Unable to create the database: '.$link->error, LOGLEVEL_WARNING);
d83fcf 219             }
7fe908 220
abad78 221             // Create the database user if database is active
663caf 222             if($data['new']['active'] == 'y') {
7fe908 223
MC 224                 // get the users for this database
fffb1e 225                 $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_user_id']);
FS 226                 $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_ro_user_id']);
7fe908 227
MC 228                 $host_list = '';
229                 if($data['new']['remote_access'] == 'y') {
230                     $host_list = $data['new']['remote_ips'];
231                     if($host_list == '') $host_list = '%';
232                 }
233                 if($host_list != '') $host_list .= ',';
234                 $host_list .= 'localhost';
235
236                 if($db_user) {
237                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
5512af 238                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link, '', ($data['new']['quota_exceeded'] == 'y' ? 'rd' : 'rw'));
7fe908 239                 }
MC 240                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
241                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
5512af 242                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', 'r');
7fe908 243                 }
MC 244
d83fcf 245             }
7fe908 246
1d8f7f 247             $link->close();
d83fcf 248         }
T 249     }
7fe908 250
MC 251     function db_update($event_name, $data) {
d83fcf 252         global $app, $conf;
7fe908 253
cc6568 254         // skip processing if database was and is inactive
H 255         if($data['new']['active'] == 'n' && $data['old']['active'] == 'n') return;
7fe908 256
663caf 257         if($data['new']['type'] == 'mysql') {
7fe908 258             if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
MC 259                 $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
a61345 260                 return;
d83fcf 261             }
7fe908 262
d83fcf 263             //* Connect to the database
1d8f7f 264             $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
a7cb2b 265             if ($link->connect_error) {
7fe908 266                 $app->log('Unable to connect to the database: '.$link->connect_error, LOGLEVEL_ERROR);
88d899 267                 return;
d83fcf 268             }
6f97fc 269             
7fe908 270             // get the users for this database
fffb1e 271             $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_user_id']);
FS 272             $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_user_id']);
7fe908 273
fffb1e 274             $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_ro_user_id']);
FS 275             $old_db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_ro_user_id']);
7fe908 276
MC 277             $host_list = '';
278             if($data['new']['remote_access'] == 'y') {
279                 $host_list = $data['new']['remote_ips'];
280                 if($host_list == '') $host_list = '%';
281             }
282             if($host_list != '') $host_list .= ',';
283             $host_list .= 'localhost';
284
cc6568 285             // REVOKES and DROPS have to be done on old host list, not new host list
H 286             $old_host_list = '';
7fe908 287             if($data['old']['remote_access'] == 'y') {
MC 288                 $old_host_list = $data['old']['remote_ips'];
289                 if($old_host_list == '') $old_host_list = '%';
abad78 290             }
7fe908 291             if($old_host_list != '') $old_host_list .= ',';
MC 292             $old_host_list .= 'localhost';
293
9e247a 294             //* rename database
FS 295             if ( $data['new']['database_name'] !=  $data['old']['database_name'] ) {
296                 $old_name = $link->escape_string($data['old']['database_name']);
297                 $new_name = $link->escape_string($data['new']['database_name']);
298                 $timestamp = time();
299
300                 $tables = $link->query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema='".$old_name."' AND TABLE_TYPE='BASE TABLE'");
301                 if ($tables->num_rows > 0) {
302                     while ($row = $tables->fetch_assoc()) {
303                         $tables_array[] = $row['TABLE_NAME'];
304                     }
305
306                     //* save triggers, routines and events
307                     $triggers = $link->query("SHOW TRIGGERS FROM ".$old_name);
308                     if ($triggers->num_rows > 0) {
309                         while ($row = $triggers->fetch_assoc()) {
310                             $triggers_array[] = $row;
311                         }
312                         $app->log('Dumping triggers from '.$old_name, LOGLEVEL_DEBUG);
313                         $command = "mysqldump -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$old_name." -d -t -R -E > ".$timestamp.$old_name.'.triggers';
314                         exec($command, $out, $ret);
315                         $app->system->chmod($timestamp.$old_name.'.triggers', 0600);
316                         if ($ret != 0) {
317                             unset($triggers_array);
318                             $app->system->unlink($timestamp.$old_name.'.triggers');
319                             $app->log('Unable to dump triggers from '.$old_name, LOGLEVEL_ERROR);
320                         }
321                         unset($out);
322                     }
323
324                     //* save views
325                     $views = $link->query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema='".$old_name."' and TABLE_TYPE='VIEW'");
326                     if ($views->num_rows > 0) {
327                         while ($row = $views->fetch_assoc()) {
328                             $views_array[] = $row;
329                         }
330                         foreach ($views_array as $_views) {
331                             $temp[] = $_views['TABLE_NAME'];
332                         }
333                         $app->log('Dumping views from '.$old_name, LOGLEVEL_DEBUG);
334                         $temp_views = implode(' ', $temp);
335                         $command = "mysqldump -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$old_name." ".$temp_views." > ".$timestamp.$old_name.'.views';
336                         exec($command, $out, $ret);
337                         $app->system->chmod($timestamp.$old_name.'.views', 0600);
338                         if ($ret != 0) {
339                             unset($views_array);
340                             $app->system->unlink($timestamp.$old_name.'.views');
341                             $app->log('Unable to dump views from '.$old_name, LOGLEVEL_ERROR);
342                         }
343                         unset($out);
344                         unset($temp);
345                         unset($temp_views);
346                     }
347
348                     //* create new database
349                     $this->db_insert($event_name, $data);
350
351                     $link->query("show databases like '".$new_name."'");
352                     if ($link) {
353                         //* rename tables
354                         foreach ($tables_array as $table) {
355                             $table = $link->escape_string($table);
356                             $sql = "RENAME TABLE ".$old_name.".".$table." TO ".$new_name.".".$table;
357                             $link->query($sql);
358                             $app->log($sql, LOGLEVEL_DEBUG);
359                             if(!$link) {
360                                 $app->log($sql." failed", LOGLEVEL_ERROR);
361                             }
362                         }
363
364                         //* drop old triggers
365                         if (@is_array($triggers_array)) {
366                             foreach($triggers_array as $trigger) {
367                                 $_trigger = $link->escape_string($trigger['Trigger']);
368                                 $sql = "DROP TRIGGER ".$old_name.".".$_trigger;
369                                 $link->query($sql);
370                                 $app->log($sql, LOGLEVEL_DEBUG);
371                                 unset($_trigger);
372                             }
373                             //* update triggers, routines and events
374                             $command = "mysql -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$new_name." < ".$timestamp.$old_name.'.triggers';
375                             exec($command, $out, $ret);
376                             if ($ret != 0) {
377                                 $app->log('Unable to import triggers for '.$new_name, LOGLEVEL_ERROR);
378                             } else {
379                                 $app->system->unlink($timestamp.$old_name.'.triggers');
380                             }
381                         }
382
383                         //* loading views
384                         if (@is_array($views_array)) {
385                             $command = "mysql -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$new_name." < ".$timestamp.$old_name.'.views';
386                             exec($command, $out, $ret);
387                             if ($ret != 0) {
388                                 $app->log('Unable to import views for '.$new_name, LOGLEVEL_ERROR);
389                             } else {
390                                 $app->system->unlink($timestamp.$old_name.'.views');
391                             }
392                         }
393
394                         //* drop old database
395                         $this->db_delete($event_name, $data);
396                     } else {
397                         $app->log('Connection to new databse '.$new_name.' failed', LOGLEVEL_ERROR);
398                         if (@is_array($triggers_array)) {
399                             $app->system->unlink($timestamp.$old_name.'.triggers');
400                         }
401                         if (@is_array($views_array)) {
402                             $app->system->unlink($timestamp.$old_name.'.views');
403                         }
404                     }
405
406                 } else { //* SELECT TABLE_NAME error
407                     $app->log('Unable to rename database '.$old_name.' to '.$new_name, LOGLEVEL_ERROR);
408                 }
409             }
410
7fe908 411             // Create the database user if database was disabled before
51569e 412             if($data['new']['active'] == 'y') {
7fe908 413                 if($db_user) {
MC 414                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
5512af 415                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link, '', ($data['new']['quota_exceeded'] == 'y' ? 'rd' : 'rw'));
7fe908 416                 }
MC 417                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
418                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
5512af 419                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', 'r');
7fe908 420                 }
6f97fc 421             } elseif($data['new']['active'] == 'n' && $data['old']['active'] == 'y') { // revoke database user, if inactive
MB 422                 if($old_db_user) {
423                     if($old_db_user['database_user'] == 'root'){
424                         $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
425                     } else {
426                         // Find out users to drop and users to revoke
427                         $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
428                         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);
429                         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);
430                     }
9e247a 431
7fe908 432                 }
6f97fc 433                 if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
MB 434                     if($old_db_ro_user['database_user'] == 'root'){
435                         $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
436                     } else {
437                         // Find out users to drop and users to revoke
438                         $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list);
439                         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);
440                         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);
441                     }
442                 }
443                 // Database is not active, so stop processing here
444                 $link->close();
445                 return;
446             }
7fe908 447
MC 448             //* selected Users have changed
449             if($data['new']['database_user_id'] != $data['old']['database_user_id']) {
450                 if($data['old']['database_user_id'] && $data['old']['database_user_id'] != $data['new']['database_ro_user_id']) {
451                     if($old_db_user) {
452                         if($old_db_user['database_user'] == 'root'){
453                             $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
454                         } else {
455                             // Find out users to drop and users to revoke
456                             $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
457                             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);
458                             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);
cc6568 459                         }
7fe908 460                     }
MC 461                 }
462                 if($db_user) {
463                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
5512af 464                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link, '', ($data['new']['quota_exceeded'] == 'y' ? 'rd' : 'rw'));
7fe908 465                 }
MC 466             }
467             if($data['new']['database_ro_user_id'] != $data['old']['database_ro_user_id']) {
468                 if($data['old']['database_ro_user_id'] && $data['old']['database_ro_user_id'] != $data['new']['database_user_id']) {
469                     if($old_db_ro_user) {
470                         if($old_db_ro_user['database_user'] == 'root'){
471                             $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
472                         } else {
cc6568 473                             // Find out users to drop and users to revoke
615a0a 474                             $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
T 475                             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);
476                             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);
cc6568 477                         }
7fe908 478                     }
MC 479                 }
480                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
481                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
5512af 482                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', 'r');
7fe908 483                 }
MC 484             }
485
d83fcf 486             //* Remote access option has changed.
663caf 487             if($data['new']['remote_access'] != $data['old']['remote_access']) {
7fe908 488
673365 489                 //* set new priveliges
7fe908 490                 if($data['new']['remote_access'] == 'y') {
MC 491                     if($db_user) {
492                         if($db_user['database_user'] == 'root'){
493                             $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
494                         } else {
5512af 495                             $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link, '', ($data['new']['quota_exceeded'] == 'y' ? 'rd' : 'rw'));
cc6568 496                         }
7fe908 497                     }
MC 498                     if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
499                         if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
5512af 500                         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, '', 'r');
7fe908 501                     }
d83fcf 502                 } else {
7fe908 503                     if($old_db_user) {
MC 504                         if($old_db_user['database_user'] == 'root'){
505                             $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
506                         } else {
cc6568 507                             // Find out users to drop and users to revoke
615a0a 508                             $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
T 509                             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);
510                             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);
cc6568 511                         }
7fe908 512                     }
MC 513                     if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
514                         if($old_db_ro_user['database_user'] == 'root'){
515                             $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
516                         } else {
cc6568 517                             // Find out users to drop and users to revoke
615a0a 518                             $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $data['old']['remote_ips']);
T 519                             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);
520                             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);
cc6568 521                         }
7fe908 522                     }
d83fcf 523                 }
7fe908 524                 $app->log('Changing MySQL remote access privileges for database: '.$data['new']['database_name'], LOGLEVEL_DEBUG);
663caf 525             } elseif($data['new']['remote_access'] == 'y' && $data['new']['remote_ips'] != $data['old']['remote_ips']) {
7fe908 526                 //* Change remote access list
MC 527                 if($old_db_user) {
528                     if($old_db_user['database_user'] == 'root'){
529                         $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
530                     } else {
615a0a 531                         // Find out users to drop and users to revoke
T 532                         $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
533                         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);
534                         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);
7fe908 535                     }
MC 536                 }
537                 if($db_user) {
538                     if($db_user['database_user'] == 'root'){
539                         $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
540                     } else {
5512af 541                         $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link, '', ($data['new']['quota_exceeded'] == 'y' ? 'rd' : 'rw'));
7fe908 542                     }
MC 543                 }
544
545                 if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
546                     if($old_db_ro_user['database_user'] == 'root'){
547                         $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
548                     } else {
615a0a 549                         // Find out users to drop and users to revoke
T 550                         $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
551                         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);
552                         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);
7fe908 553                     }
MC 554                 }
555
556                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
557                     if($db_ro_user['database_user'] == 'root'){
558                         $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
559                     } else {
5512af 560                         $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['new']['remote_ips'], $link, '', 'r');
7fe908 561                     }
MC 562                 }
cc6568 563             }
7fe908 564
1d8f7f 565             $link->close();
d83fcf 566         }
7fe908 567
d83fcf 568     }
7fe908 569
MC 570     function db_delete($event_name, $data) {
d83fcf 571         global $app, $conf;
7fe908 572
663caf 573         if($data['old']['type'] == 'mysql') {
7fe908 574             if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
MC 575                 $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
a61345 576                 return;
d83fcf 577             }
7fe908 578
d83fcf 579             //* Connect to the database
1d8f7f 580             $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
N 581             if ($link->connect_error) {
7fe908 582                 $app->log('Unable to connect to mysql: '.$link->connect_error, LOGLEVEL_ERROR);
88d899 583                 return;
d83fcf 584             }
7fe908 585
615a0a 586             $old_host_list = '';
7fe908 587             if($data['old']['remote_access'] == 'y') {
MC 588                 $old_host_list = $data['old']['remote_ips'];
589                 if($old_host_list == '') $old_host_list = '%';
614365 590             }
7fe908 591             if($old_host_list != '') $old_host_list .= ',';
MC 592             $old_host_list .= 'localhost';
593
594             if($data['old']['database_user_id']) {
fffb1e 595                 $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_user_id']);
7fe908 596                 $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
MC 597                 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);
598                 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);
599             }
600             if($data['old']['database_ro_user_id']) {
fffb1e 601                 $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_ro_user_id']);
7fe908 602                 $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list);
MC 603                 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);
604                 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);
605             }
606
607
745a6b 608             if($link->query('DROP DATABASE `'.$link->escape_string($data['old']['database_name'].'`'))) {
7fe908 609                 $app->log('Dropping MySQL database: '.$data['old']['database_name'], LOGLEVEL_DEBUG);
MC 610             } else {
611                 $app->log('Error while dropping MySQL database: '.$data['old']['database_name'].' '.$link->error, LOGLEVEL_WARNING);
612             }
613
1d8f7f 614             $link->close();
d83fcf 615         }
7fe908 616
MC 617
d83fcf 618     }
7fe908 619
MC 620
621     function db_user_insert($event_name, $data) {
acf18c 622         global $app, $conf;
381520 623         // we have nothing to do here, stale user accounts are useless ;)
acf18c 624     }
7fe908 625
MC 626     function db_user_update($event_name, $data) {
acf18c 627         global $app, $conf;
7fe908 628
MC 629         if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
630             $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
631             return;
632         }
633
634         //* Connect to the database
635         $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
636         if ($link->connect_error) {
637             $app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR);
638             return;
639         }
640
641
642         if($data['old']['database_user'] == $data['new']['database_user'] && ($data['old']['database_password'] == $data['new']['database_password'] || $data['new']['database_password'] == '')) {
643             return;
644         }
645
646
647         $host_list = array('localhost');
648         // get all databases this user was active for
4f9dee 649         $user_id = intval($data['old']['database_user_id']);
FS 650         $db_list = $app->db->queryAllRecords("SELECT `remote_access`, `remote_ips` FROM `web_database` WHERE `database_user_id` = ? OR database_ro_user_id = ?", $user_id, $user_id);;
7fe908 651         if(count($db_list) < 1) return; // nothing to do on this server for this db user
MC 652
653         foreach($db_list as $database) {
654             if($database['remote_access'] != 'y') continue;
655
656             if($database['remote_ips'] != '') $ips = explode(',', $database['remote_ips']);
657             else $ips = array('%');
658
659             foreach($ips as $ip) {
660                 $ip = trim($ip);
661                 if(!in_array($ip, $host_list)) $host_list[] = $ip;
662             }
663         }
664
665         foreach($host_list as $db_host) {
666             if($data['new']['database_user'] != $data['old']['database_user']) {
381520 667                 $link->query("RENAME USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host' TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host'");
7fe908 668                 $app->log('Renaming MySQL user: '.$data['old']['database_user'].' to '.$data['new']['database_user'], LOGLEVEL_DEBUG);
381520 669             }
M 670
bfcdef 671             if($data['new']['database_password'] != $data['old']['database_password'] && $data['new']['database_password'] != '') {
43b345 672                 $link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");
7fe908 673                 $app->log('Changing MySQL user password for: '.$data['new']['database_user'].'@'.$db_host, LOGLEVEL_DEBUG);
381520 674             }
7fe908 675         }
MC 676
677         $link->close();
678
acf18c 679     }
7fe908 680
MC 681     function db_user_delete($event_name, $data) {
acf18c 682         global $app, $conf;
7fe908 683
MC 684         if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
685             $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
686             return;
687         }
688
689         //* Connect to the database
690         $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
691         if ($link->connect_error) {
692             $app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR);
693             return;
694         }
695
696         $host_list = array();
697         // read all mysql users with this username
698         $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
699         if($result) {
700             while($row = $result->fetch_assoc()) {
701                 $host_list[] = $row['Host'];
702             }
703             $result->free();
704         }
705
706         foreach($host_list as $db_host) {
707             if($link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';")) {
708                 $app->log('Dropping MySQL user: '.$data['old']['database_user'], LOGLEVEL_DEBUG);
709             }
710         }
711
712         $link->close();
acf18c 713     }
7fe908 714
d83fcf 715 } // end class
T 716
663caf 717 ?>