ISPConfig3 devel
view | history | commit | commitdiff
mcramer
2012-08-21 381520c8866a5f3be7e743e3ae16b6fb2988c495
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 {
d83fcf 32     
a61345 33     var $plugin_name = 'mysql_clientdb_plugin';
T 34     var $class_name  = 'mysql_clientdb_plugin';
d83fcf 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;
40         
41         if($conf['services']['db'] == true) {
42             return true;
43         } else {
44             return false;
45         }
46         
47     }
48     
d83fcf 49         
T 50     /*
51          This function is called when the plugin is loaded
52     */
53     
54     function onLoad() {
55         global $app;
56         
57         /*
58         Register for the events
59         */
60         
acf18c 61         //* Databases
d83fcf 62         $app->plugins->registerEvent('database_insert',$this->plugin_name,'db_insert');
T 63         $app->plugins->registerEvent('database_update',$this->plugin_name,'db_update');
64         $app->plugins->registerEvent('database_delete',$this->plugin_name,'db_delete');
acf18c 65         
T 66         //* Database users
381520 67         $app->plugins->registerEvent('database_user_insert',$this->plugin_name,'db_user_insert');
M 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');
d83fcf 70         
T 71         
72     }
73     
381520 74   function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '', $user_read_only = false) {
086696 75       global $app;
M 76       
77       $action = strtoupper($action);
78       
79       // set to all hosts if none given
663caf 80       if(trim($host_list) == '') $host_list = '%';
086696 81       
M 82       // process arrays and comma separated strings
209ce8 83       if(!is_array($host_list)) $host_list = explode(',', $host_list);
086696 84       
M 85       $success = true;
86       
87       // loop through hostlist
88       foreach($host_list as $db_host) {
89           $db_host = trim($db_host);
90           
91           // check if entry is valid ip address
92           $valid = true;
663caf 93           if($db_host == '%') {
341d2b 94               $valid = true;
T 95           } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
663caf 96               $groups = explode('.', $db_host);
086696 97               foreach($groups as $group){
M 98                 if($group<0 OR $group>255)
99                 $valid=false;
100               }
101           } else {
102               $valid = false;
103           }
104           
105           if($valid == false) continue;
106           
663caf 107           if($action == 'GRANT') {
381520 108               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;
663caf 109           } elseif($action == 'REVOKE') {
381520 110               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;
663caf 111           } elseif($action == 'DROP') {
1d8f7f 112               if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host';")) $success = false;
663caf 113           } elseif($action == 'RENAME') {
1d8f7f 114               if(!$link->query("RENAME USER '".$link->escape_string($database_user)."'@'$db_host' TO '".$link->escape_string($database_rename_user)."'@'$db_host'")) $success = false;
663caf 115           } elseif($action == 'PASSWORD') {
5a43e7 116               if(!$link->query("SET PASSWORD FOR '".$link->escape_string($database_user)."'@'$db_host' = '".$link->escape_string($database_password)."';")) $success = false;
086696 117           }
M 118       }
119       
120       return $success;
121   }
d83fcf 122     
T 123     function db_insert($event_name,$data) {
124         global $app, $conf;
125         
663caf 126         if($data['new']['type'] == 'mysql') {
673365 127             if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
d83fcf 128                 $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
7c99ef 129                 return;
d83fcf 130             }
e5b353 131             
d83fcf 132             //* Connect to the database
1d8f7f 133             $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
a7cb2b 134             if ($link->connect_error) {
1d8f7f 135                 $app->log('Unable to connect to mysql'.$link->connect_error,LOGLEVEL_ERROR);
a61345 136                 return;
d83fcf 137             }
be9816 138
R 139             // Charset for the new table
663caf 140             if($data['new']['database_charset'] != '') {
J 141         $query_charset_table = ' DEFAULT CHARACTER SET '.$data['new']['database_charset'];
be9816 142             } else {
R 143         $query_charset_table = '';
144             }
145
d83fcf 146             //* Create the new database
1d8f7f 147             if ($link->query('CREATE DATABASE '.$link->escape_string($data['new']['database_name']).$query_charset_table)) {
663caf 148                 $app->log('Created MySQL database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
d83fcf 149             } else {
1d8f7f 150                 $app->log('Unable to create the database: '.$link->error,LOGLEVEL_WARNING);
d83fcf 151             }
T 152             
abad78 153             // Create the database user if database is active
663caf 154             if($data['new']['active'] == 'y') {
abad78 155                 
381520 156                 // get the users for this database
M 157                 $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");
158                 
159                 $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']) . "'");
160                 
161                 $host_list = '';
162                 if($data['new']['remote_access'] == 'y') {
163                     $host_list = $data['new']['remote_ips'];
164                 }
165                 if($host_list != '') $host_list .= ',';
166                 $host_list .= 'localhost';
167                 
168                 if($db_user) {
169                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
170                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
171                 }
172                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
173                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
174                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
175                 }
abad78 176                 
d83fcf 177             }
T 178             
1d8f7f 179             $link->query('FLUSH PRIVILEGES;');
N 180             $link->close();
d83fcf 181         }
T 182     }
183     
184     function db_update($event_name,$data) {
185         global $app, $conf;
186         
663caf 187         if($data['new']['type'] == 'mysql') {
673365 188             if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
d83fcf 189                 $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
a61345 190                 return;
d83fcf 191             }
abad78 192             
d83fcf 193             //* Connect to the database
1d8f7f 194             $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
a7cb2b 195             if ($link->connect_error) {
1d8f7f 196                 $app->log('Unable to connect to the database: '.$link->connect_error,LOGLEVEL_ERROR);
88d899 197                 return;
d83fcf 198             }
T 199             
381520 200             // get the users for this database
M 201             $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");
202             
203             $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']) . "'");
204             
205             $host_list = '';
206             if($data['new']['remote_access'] == 'y') {
207                 $host_list = $data['new']['remote_ips'];
208             }
209             if($host_list != '') $host_list .= ',';
210             $host_list .= 'localhost';
211             
212             // Create the database user if database was disabled before
663caf 213             if($data['new']['active'] == 'y' && $data['old']['active'] == 'n') {
381520 214                 if($db_user) {
M 215                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
216                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
217                 }
218                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
219                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
220                     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                 }
222             } else if($data['new']['active'] == 'n' && $data['old']['active'] == 'y') { // revoke database user, if inactive
223                 if($db_user) {
224                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
225                     else $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
226                 }
227                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
228                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
229                     else $this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link);
230                 }
abad78 231             }
381520 232             
M 233             //* selected Users have changed
234             if($data['new']['database_user_id'] != $data['old']['database_user_id']) {
235                 if($data['old']['database_user_id'] && $data['old']['database_user_id'] != $data['new']['database_ro_user_id']) {
236                     $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']) . "'");
237                     if($old_db_user) {
238                         if($old_db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
239                         else $this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $host_list, $link);
240                     }
241                 }
242                 if($db_user) {
243                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
244                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
245                 }
246             }
247             if($data['new']['database_ro_user_id'] != $data['old']['database_ro_user_id']) {
248                 if($data['old']['database_ro_user_id'] && $data['old']['database_ro_user_id'] != $data['new']['database_user_id']) {
249                     $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']) . "'");
250                     if($old_db_user) {
251                         if($old_db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
252                         else $this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $host_list, $link);
253                     }
254                 }
255                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
256                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
257                     else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
258                 }
259             }
d83fcf 260             
T 261             //* Remote access option has changed.
663caf 262             if($data['new']['remote_access'] != $data['old']['remote_access']) {
673365 263                 
T 264                 //* revoke old priveliges
eece36 265                 //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);
673365 266                 
T 267                 //* set new priveliges
663caf 268                 if($data['new']['remote_access'] == 'y') {         
381520 269                     if($db_user) {
M 270                         if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
271                         else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
272                     }
273                     if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
274                         if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
275                         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);
276                     }
d83fcf 277                 } else {
381520 278                     if($db_user) {
M 279                         if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
280                         else $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
281                     }
282                     if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
283                         if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
284                         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);
285                     }
d83fcf 286                 }
663caf 287                 $app->log('Changing MySQL remote access privileges for database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
J 288             } elseif($data['new']['remote_access'] == 'y' && $data['new']['remote_ips'] != $data['old']['remote_ips']) {
381520 289                 //* Change remote access list
M 290                 if($db_user) {
291                     if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
292                     else {
293                         $this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link);
294                         $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
295                     }
296                 }
297                 if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
298                     if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
299                     else {
300                         $this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link);
301                         $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);
302                     }
303                 }
304           }
086696 305       
d83fcf 306             
1d8f7f 307             $link->query('FLUSH PRIVILEGES;');
N 308             $link->close();
d83fcf 309         }
T 310         
311     }
312     
313     function db_delete($event_name,$data) {
314         global $app, $conf;
315         
663caf 316         if($data['old']['type'] == 'mysql') {
673365 317             if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
d83fcf 318                 $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
a61345 319                 return;
d83fcf 320             }
T 321         
322             //* Connect to the database
1d8f7f 323             $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
N 324             if ($link->connect_error) {
325                 $app->log('Unable to connect to mysql: '.$link->connect_error,LOGLEVEL_ERROR);
88d899 326                 return;
d83fcf 327             }
T 328             
1d8f7f 329             if($link->query('DROP DATABASE '.$link->escape_string($data['old']['database_name']))) {
663caf 330                 $app->log('Dropping MySQL database: '.$data['old']['database_name'],LOGLEVEL_DEBUG);
614365 331             } else {
4a8b7e 332                 $app->log('Error while dropping MySQL database: '.$data['old']['database_name'].' '.$link->error,LOGLEVEL_WARNING);
614365 333             }
d83fcf 334             
1d8f7f 335             $link->query('FLUSH PRIVILEGES;');
N 336             $link->close();
d83fcf 337         }
T 338         
339         
340     }
341     
381520 342      
acf18c 343     function db_user_insert($event_name,$data) {
T 344         global $app, $conf;
381520 345         // we have nothing to do here, stale user accounts are useless ;)
acf18c 346     }
d83fcf 347     
acf18c 348     function db_user_update($event_name,$data) {
T 349         global $app, $conf;
350         
381520 351         if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
M 352             $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
353             return;
354         }
355         
356         //* Connect to the database
357         $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
358         if ($link->connect_error) {
359             $app->log('Unable to connect to mysql'.$link->connect_error,LOGLEVEL_ERROR);
360             return;
361         }
362         
363         
364         if($data['old']['database_user'] == $data['new']['database_user'] && $data['old']['database_password'] == $data['new']['database_password']) {
365             return;
366         }
367         
368         
369         $host_list = array('localhost');
370         // get all databases this user was active for
371         $db_list = $app->db->queryAllRecords("SELECT `remote_access`, `remote_ips` FROM `web_database` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
372         foreach($db_list as $database) {
373             if($database['remote_access'] != 'y') continue;
374             
375             if($database['remote_ips'] != '') $ips = explode(',', $database['remote_ips']);
376             else $ips = array('%');
377             
378             foreach($ips as $ip) {
379                 $ip = trim($ip);
380                 if(!in_array($ip, $host_list)) $host_list[] = $ip;
381             }
382         }
383         
384         foreach($host_list as $db_host) {
385             if($data['new']['database_user'] != $data['old']['database_user']) {
386                 $link->query("RENAME USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host' TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host'");
387                 $app->log('Renaming MySQL user: '.$data['old']['database_user'].' to '.$data['new']['database_user'],LOGLEVEL_DEBUG);
388             }
389
390             if($data['new']['database_password'] != $data['old']['database_password']) {
391                 $db_host = 'localhost';
392                 $link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");
393                 $app->log('Changing MySQL user password for: '.$data['new']['database_user'],LOGLEVEL_DEBUG);
394             }
395         }
396         
397         $link->query('FLUSH PRIVILEGES;');
398         $link->close();
399         
acf18c 400     }
T 401     
402     function db_user_delete($event_name,$data) {
403         global $app, $conf;
404         
381520 405         if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
M 406             $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
407             return;
408         }
409         
410         //* Connect to the database
411         $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
412         if ($link->connect_error) {
413             $app->log('Unable to connect to mysql'.$link->connect_error,LOGLEVEL_ERROR);
414             return;
415         }
416         
417         $host_list = array();
418         // read all mysql users with this username
419         $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
420         if($result) {
421             while($row = $result->fetch_assoc()) {
422                 $host_list[] = $row['Host'];
423             }
424             $result->free();
425         }
426         
427         foreach($host_list as $db_host) {
428             if($link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';")) {
429                 $app->log('Dropping MySQL user: '.$data['old']['database_user'],LOGLEVEL_DEBUG);
430             }
431         }
432         
433         $link->query('FLUSH PRIVILEGES;');
434         $link->close();
acf18c 435     }
d83fcf 436 } // end class
T 437
663caf 438 ?>