| 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 | |
| MC | 74 | function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '', $user_read_only = false) { |
| cc6568 | 75 | global $app; |
| 7fe908 | 76 | |
| MC | 77 | $action = strtoupper($action); |
| 78 | ||
| cc6568 | 79 | // set to all hosts if none given |
| H | 80 | if(trim($host_list) == '') $host_list = '%'; |
| 7fe908 | 81 | |
| MC | 82 | // process arrays and comma separated strings |
| 83 | if(!is_array($host_list)) $host_list = explode(',', $host_list); | |
| 84 | ||
| 85 | $success = true; | |
| 51569e | 86 | if(!preg_match('/\*[A-F0-9]{40}$/', $database_password)) { |
| MC | 87 | $result = $link->query("SELECT PASSWORD('" . $link->escape_string($database_password) . "') as `crypted`"); |
| 88 | if($result) { | |
| 89 | $row = $result->fetch_assoc(); | |
| 90 | $database_password = $row['crypted']; | |
| 91 | $result->free(); | |
| 92 | } | |
| 93 | } | |
| 7fe908 | 94 | // loop through hostlist |
| MC | 95 | foreach($host_list as $db_host) { |
| 96 | $db_host = trim($db_host); | |
| 97 | ||
| 98 | $app->log($action . ' for user ' . $database_user . ' at host ' . $db_host, LOGLEVEL_DEBUG); | |
| 99 | ||
| 100 | // check if entry is valid ip address | |
| 101 | $valid = true; | |
| 102 | if($db_host == '%' || $db_host == 'localhost') { | |
| 103 | $valid = true; | |
| 104 | } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) { | |
| 105 | $groups = explode('.', $db_host); | |
| 106 | foreach($groups as $group){ | |
| 107 | if($group<0 or $group>255) | |
| 108 | $valid=false; | |
| 109 | } | |
| 110 | } else { | |
| 111 | $valid = false; | |
| 112 | } | |
| 113 | ||
| 114 | if($valid == false) continue; | |
| 115 | ||
| 116 | if($action == 'GRANT') { | |
| 745a6b | 117 | 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; |
| FS | 118 | $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); |
| 7fe908 | 119 | } elseif($action == 'REVOKE') { |
| 745a6b | 120 | 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; |
| 7fe908 | 121 | } elseif($action == 'DROP') { |
| MC | 122 | if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host';")) $success = false; |
| 123 | } elseif($action == 'RENAME') { | |
| 124 | if(!$link->query("RENAME USER '".$link->escape_string($database_user)."'@'$db_host' TO '".$link->escape_string($database_rename_user)."'@'$db_host'")) $success = false; | |
| 125 | } elseif($action == 'PASSWORD') { | |
| 126 | if(!$link->query("SET PASSWORD FOR '".$link->escape_string($database_user)."'@'$db_host' = '".$link->escape_string($database_password)."';")) $success = false; | |
| 127 | } | |
| 128 | } | |
| 129 | ||
| 130 | return $success; | |
| 131 | } | |
| 132 | ||
| 133 | function drop_or_revoke_user($database_id, $user_id, $host_list){ | |
| 134 | global $app; | |
| 135 | ||
| 136 | // set to all hosts if none given | |
| 137 | if(trim($host_list) == '') $host_list = '%'; | |
| 138 | ||
| 4f9dee | 139 | $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 | 140 | $db_user_host_list = array(); |
| H | 141 | if(is_array($db_user_databases) && !empty($db_user_databases)){ |
| 142 | foreach($db_user_databases as $db_user_database){ | |
| 143 | if($db_user_database['remote_access'] == 'y'){ | |
| 144 | if($db_user_database['remote_ips'] == ''){ | |
| 145 | $db_user_host_list[] = '%'; | |
| 146 | } else { | |
| 147 | $tmp_remote_ips = explode(',', $db_user_database['remote_ips']); | |
| 148 | if(is_array($tmp_remote_ips) && !empty($tmp_remote_ips)){ | |
| 149 | foreach($tmp_remote_ips as $tmp_remote_ip){ | |
| 150 | $tmp_remote_ip = trim($tmp_remote_ip); | |
| 151 | if($tmp_remote_ip != '') $db_user_host_list[] = $tmp_remote_ip; | |
| 152 | } | |
| 153 | } | |
| 154 | unset($tmp_remote_ips); | |
| 155 | } | |
| 156 | } | |
| 157 | $db_user_host_list[] = 'localhost'; | |
| 158 | } | |
| 159 | } | |
| 160 | $host_list_arr = explode(',', $host_list); | |
| 161 | //print_r($host_list_arr); | |
| 162 | $drop_hosts = array_diff($host_list_arr, $db_user_host_list); | |
| 163 | //print_r($drop_hosts); | |
| 164 | $revoke_hosts = array_diff($host_list_arr, $drop_hosts); | |
| 165 | //print_r($revoke_hosts); | |
| 7fe908 | 166 | |
| cc6568 | 167 | $drop_host_list = implode(',', $drop_hosts); |
| H | 168 | $revoke_host_list = implode(',', $revoke_hosts); |
| 169 | //echo $drop_host_list."\n"; | |
| 170 | //echo $revoke_host_list."\n"; | |
| 171 | return array('revoke_hosts' => $revoke_host_list, 'drop_hosts' => $drop_host_list); | |
| 172 | } | |
| 7fe908 | 173 | |
| MC | 174 | function db_insert($event_name, $data) { |
| d83fcf | 175 | global $app, $conf; |
| 7fe908 | 176 | |
| 663caf | 177 | if($data['new']['type'] == 'mysql') { |
| 7fe908 | 178 | if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') { |
| MC | 179 | $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR); |
| 7c99ef | 180 | return; |
| d83fcf | 181 | } |
| 7fe908 | 182 | |
| d83fcf | 183 | //* Connect to the database |
| 1d8f7f | 184 | $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password); |
| a7cb2b | 185 | if ($link->connect_error) { |
| 7fe908 | 186 | $app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR); |
| a61345 | 187 | return; |
| d83fcf | 188 | } |
| be9816 | 189 | |
| R | 190 | // Charset for the new table |
| 663caf | 191 | if($data['new']['database_charset'] != '') { |
| 7fe908 | 192 | $query_charset_table = ' DEFAULT CHARACTER SET '.$data['new']['database_charset']; |
| be9816 | 193 | } else { |
| 7fe908 | 194 | $query_charset_table = ''; |
| be9816 | 195 | } |
| R | 196 | |
| d83fcf | 197 | //* Create the new database |
| 1e8c9b | 198 | if ($link->query('CREATE DATABASE `'.$link->escape_string($data['new']['database_name']).'`'.$query_charset_table)) { |
| 7fe908 | 199 | $app->log('Created MySQL database: '.$data['new']['database_name'], LOGLEVEL_DEBUG); |
| d83fcf | 200 | } else { |
| 7fe908 | 201 | $app->log('Unable to create the database: '.$link->error, LOGLEVEL_WARNING); |
| d83fcf | 202 | } |
| 7fe908 | 203 | |
| abad78 | 204 | // Create the database user if database is active |
| 663caf | 205 | if($data['new']['active'] == 'y') { |
| 7fe908 | 206 | |
| MC | 207 | // get the users for this database |
| fffb1e | 208 | $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_user_id']); |
| FS | 209 | $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 | 210 | |
| MC | 211 | $host_list = ''; |
| 212 | if($data['new']['remote_access'] == 'y') { | |
| 213 | $host_list = $data['new']['remote_ips']; | |
| 214 | if($host_list == '') $host_list = '%'; | |
| 215 | } | |
| 216 | if($host_list != '') $host_list .= ','; | |
| 217 | $host_list .= 'localhost'; | |
| 218 | ||
| 219 | if($db_user) { | |
| 220 | if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 221 | else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link); | |
| 222 | } | |
| 223 | if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) { | |
| 224 | if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 225 | else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true); | |
| 226 | } | |
| 227 | ||
| d83fcf | 228 | } |
| 7fe908 | 229 | |
| 1d8f7f | 230 | $link->query('FLUSH PRIVILEGES;'); |
| N | 231 | $link->close(); |
| d83fcf | 232 | } |
| T | 233 | } |
| 7fe908 | 234 | |
| MC | 235 | function db_update($event_name, $data) { |
| d83fcf | 236 | global $app, $conf; |
| 7fe908 | 237 | |
| cc6568 | 238 | // skip processing if database was and is inactive |
| H | 239 | if($data['new']['active'] == 'n' && $data['old']['active'] == 'n') return; |
| 7fe908 | 240 | |
| 663caf | 241 | if($data['new']['type'] == 'mysql') { |
| 7fe908 | 242 | if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') { |
| MC | 243 | $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR); |
| a61345 | 244 | return; |
| d83fcf | 245 | } |
| 7fe908 | 246 | |
| d83fcf | 247 | //* Connect to the database |
| 1d8f7f | 248 | $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password); |
| a7cb2b | 249 | if ($link->connect_error) { |
| 7fe908 | 250 | $app->log('Unable to connect to the database: '.$link->connect_error, LOGLEVEL_ERROR); |
| 88d899 | 251 | return; |
| d83fcf | 252 | } |
| 7fe908 | 253 | |
| MC | 254 | // get the users for this database |
| fffb1e | 255 | $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_user_id']); |
| FS | 256 | $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 | 257 | |
| fffb1e | 258 | $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 | 259 | $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 | 260 | |
| MC | 261 | $host_list = ''; |
| 262 | if($data['new']['remote_access'] == 'y') { | |
| 263 | $host_list = $data['new']['remote_ips']; | |
| 264 | if($host_list == '') $host_list = '%'; | |
| 265 | } | |
| 266 | if($host_list != '') $host_list .= ','; | |
| 267 | $host_list .= 'localhost'; | |
| 268 | ||
| cc6568 | 269 | // REVOKES and DROPS have to be done on old host list, not new host list |
| H | 270 | $old_host_list = ''; |
| 7fe908 | 271 | if($data['old']['remote_access'] == 'y') { |
| MC | 272 | $old_host_list = $data['old']['remote_ips']; |
| 273 | if($old_host_list == '') $old_host_list = '%'; | |
| abad78 | 274 | } |
| 7fe908 | 275 | if($old_host_list != '') $old_host_list .= ','; |
| MC | 276 | $old_host_list .= 'localhost'; |
| 277 | ||
| 278 | // Create the database user if database was disabled before | |
| 51569e | 279 | if($data['new']['active'] == 'y') { |
| 7fe908 | 280 | if($db_user) { |
| MC | 281 | if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); |
| 282 | else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link); | |
| 283 | } | |
| 284 | if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) { | |
| 285 | if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 286 | else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true); | |
| 287 | } | |
| 288 | } else if($data['new']['active'] == 'n' && $data['old']['active'] == 'y') { // revoke database user, if inactive | |
| 289 | if($old_db_user) { | |
| 290 | if($old_db_user['database_user'] == 'root'){ | |
| 291 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 292 | } else { | |
| cc6568 | 293 | // Find out users to drop and users to revoke |
| 615a0a | 294 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list); |
| T | 295 | 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); |
| 296 | 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 | 297 | |
| MC | 298 | |
| 299 | //$this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link); | |
| 300 | //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link); | |
| 301 | } | |
| 302 | } | |
| 303 | if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) { | |
| 304 | if($old_db_ro_user['database_user'] == 'root'){ | |
| 305 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 306 | } else { | |
| 307 | // Find out users to drop and users to revoke | |
| 308 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list); | |
| 309 | 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); | |
| 310 | 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); | |
| 311 | ||
| 312 | //$this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $old_host_list, $link); | |
| 313 | //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $old_host_list, $link); | |
| 314 | } | |
| 315 | } | |
| 316 | // Database is not active, so stop processing here | |
| 317 | $link->query('FLUSH PRIVILEGES;'); | |
| 318 | $link->close(); | |
| 319 | return; | |
| 320 | } | |
| 321 | ||
| 322 | //* selected Users have changed | |
| 323 | if($data['new']['database_user_id'] != $data['old']['database_user_id']) { | |
| 324 | if($data['old']['database_user_id'] && $data['old']['database_user_id'] != $data['new']['database_ro_user_id']) { | |
| 325 | if($old_db_user) { | |
| 326 | if($old_db_user['database_user'] == 'root'){ | |
| 327 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 328 | } else { | |
| 329 | // Find out users to drop and users to revoke | |
| 330 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list); | |
| 331 | 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); | |
| 332 | 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); | |
| 333 | ||
| cc6568 | 334 | //$this->process_host_list('DROP', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link); |
| H | 335 | //$this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link); |
| 336 | } | |
| 7fe908 | 337 | } |
| MC | 338 | } |
| 339 | if($db_user) { | |
| 340 | if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 341 | else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link); | |
| 342 | } | |
| 343 | } | |
| 344 | if($data['new']['database_ro_user_id'] != $data['old']['database_ro_user_id']) { | |
| 345 | if($data['old']['database_ro_user_id'] && $data['old']['database_ro_user_id'] != $data['new']['database_user_id']) { | |
| 346 | if($old_db_ro_user) { | |
| 347 | if($old_db_ro_user['database_user'] == 'root'){ | |
| 348 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 349 | } else { | |
| cc6568 | 350 | // Find out users to drop and users to revoke |
| 615a0a | 351 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list); |
| T | 352 | 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); |
| 353 | 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 | 354 | |
| cc6568 | 355 | //$this->process_host_list('DROP', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link); |
| H | 356 | //$this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link); |
| 357 | } | |
| 7fe908 | 358 | } |
| MC | 359 | } |
| 360 | if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) { | |
| 361 | if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 362 | else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true); | |
| 363 | } | |
| 364 | } | |
| 365 | ||
| d83fcf | 366 | //* Remote access option has changed. |
| 663caf | 367 | if($data['new']['remote_access'] != $data['old']['remote_access']) { |
| 7fe908 | 368 | |
| 673365 | 369 | //* revoke old priveliges |
| eece36 | 370 | //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); |
| 7fe908 | 371 | |
| 673365 | 372 | //* set new priveliges |
| 7fe908 | 373 | if($data['new']['remote_access'] == 'y') { |
| MC | 374 | if($db_user) { |
| 375 | if($db_user['database_user'] == 'root'){ | |
| 376 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 377 | } else { | |
| cc6568 | 378 | $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link); |
| H | 379 | } |
| 7fe908 | 380 | } |
| MC | 381 | if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) { |
| 382 | if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 383 | 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); | |
| 384 | } | |
| d83fcf | 385 | } else { |
| 7fe908 | 386 | if($old_db_user) { |
| MC | 387 | if($old_db_user['database_user'] == 'root'){ |
| 388 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 389 | } else { | |
| cc6568 | 390 | // Find out users to drop and users to revoke |
| 615a0a | 391 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']); |
| T | 392 | 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); |
| 393 | 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 | 394 | |
| cc6568 | 395 | //$this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link); |
| H | 396 | //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link); |
| 397 | } | |
| 7fe908 | 398 | } |
| MC | 399 | if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) { |
| 400 | if($old_db_ro_user['database_user'] == 'root'){ | |
| 401 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 402 | } else { | |
| cc6568 | 403 | // Find out users to drop and users to revoke |
| 615a0a | 404 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $data['old']['remote_ips']); |
| T | 405 | 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); |
| 406 | 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 | 407 | |
| cc6568 | 408 | //$this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link); |
| H | 409 | //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link); |
| 410 | } | |
| 7fe908 | 411 | } |
| d83fcf | 412 | } |
| 7fe908 | 413 | $app->log('Changing MySQL remote access privileges for database: '.$data['new']['database_name'], LOGLEVEL_DEBUG); |
| 663caf | 414 | } elseif($data['new']['remote_access'] == 'y' && $data['new']['remote_ips'] != $data['old']['remote_ips']) { |
| 7fe908 | 415 | //* Change remote access list |
| MC | 416 | if($old_db_user) { |
| 417 | if($old_db_user['database_user'] == 'root'){ | |
| 418 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 419 | } else { | |
| 615a0a | 420 | // Find out users to drop and users to revoke |
| T | 421 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']); |
| 422 | 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); | |
| 423 | 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 | 424 | } |
| MC | 425 | } |
| 426 | if($db_user) { | |
| 427 | if($db_user['database_user'] == 'root'){ | |
| 428 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 429 | } else { | |
| 430 | $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link); | |
| 431 | } | |
| 432 | } | |
| 433 | ||
| 434 | if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) { | |
| 435 | if($old_db_ro_user['database_user'] == 'root'){ | |
| 436 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 437 | } else { | |
| 615a0a | 438 | // Find out users to drop and users to revoke |
| T | 439 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']); |
| 440 | 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); | |
| 441 | 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 | 442 | } |
| MC | 443 | } |
| 444 | ||
| 445 | if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) { | |
| 446 | if($db_ro_user['database_user'] == 'root'){ | |
| 447 | $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING); | |
| 448 | } else { | |
| 449 | $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); | |
| 450 | } | |
| 451 | } | |
| cc6568 | 452 | } |
| 7fe908 | 453 | |
| MC | 454 | |
| 1d8f7f | 455 | $link->query('FLUSH PRIVILEGES;'); |
| N | 456 | $link->close(); |
| d83fcf | 457 | } |
| 7fe908 | 458 | |
| d83fcf | 459 | } |
| 7fe908 | 460 | |
| MC | 461 | function db_delete($event_name, $data) { |
| d83fcf | 462 | global $app, $conf; |
| 7fe908 | 463 | |
| 663caf | 464 | if($data['old']['type'] == 'mysql') { |
| 7fe908 | 465 | if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') { |
| MC | 466 | $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR); |
| a61345 | 467 | return; |
| d83fcf | 468 | } |
| 7fe908 | 469 | |
| d83fcf | 470 | //* Connect to the database |
| 1d8f7f | 471 | $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password); |
| N | 472 | if ($link->connect_error) { |
| 7fe908 | 473 | $app->log('Unable to connect to mysql: '.$link->connect_error, LOGLEVEL_ERROR); |
| 88d899 | 474 | return; |
| d83fcf | 475 | } |
| 7fe908 | 476 | |
| 615a0a | 477 | $old_host_list = ''; |
| 7fe908 | 478 | if($data['old']['remote_access'] == 'y') { |
| MC | 479 | $old_host_list = $data['old']['remote_ips']; |
| 480 | if($old_host_list == '') $old_host_list = '%'; | |
| 614365 | 481 | } |
| 7fe908 | 482 | if($old_host_list != '') $old_host_list .= ','; |
| MC | 483 | $old_host_list .= 'localhost'; |
| 484 | ||
| 485 | if($data['old']['database_user_id']) { | |
| fffb1e | 486 | $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 | 487 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list); |
| MC | 488 | 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); |
| 489 | 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); | |
| 490 | } | |
| 491 | if($data['old']['database_ro_user_id']) { | |
| fffb1e | 492 | $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 | 493 | $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list); |
| MC | 494 | 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); |
| 495 | 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); | |
| 496 | } | |
| 497 | ||
| 498 | ||
| 745a6b | 499 | if($link->query('DROP DATABASE `'.$link->escape_string($data['old']['database_name'].'`'))) { |
| 7fe908 | 500 | $app->log('Dropping MySQL database: '.$data['old']['database_name'], LOGLEVEL_DEBUG); |
| MC | 501 | } else { |
| 502 | $app->log('Error while dropping MySQL database: '.$data['old']['database_name'].' '.$link->error, LOGLEVEL_WARNING); | |
| 503 | } | |
| 504 | ||
| 1d8f7f | 505 | $link->query('FLUSH PRIVILEGES;'); |
| N | 506 | $link->close(); |
| d83fcf | 507 | } |
| 7fe908 | 508 | |
| MC | 509 | |
| d83fcf | 510 | } |
| 7fe908 | 511 | |
| MC | 512 | |
| 513 | function db_user_insert($event_name, $data) { | |
| acf18c | 514 | global $app, $conf; |
| 381520 | 515 | // we have nothing to do here, stale user accounts are useless ;) |
| acf18c | 516 | } |
| 7fe908 | 517 | |
| MC | 518 | function db_user_update($event_name, $data) { |
| acf18c | 519 | global $app, $conf; |
| 7fe908 | 520 | |
| MC | 521 | if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') { |
| 522 | $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR); | |
| 523 | return; | |
| 524 | } | |
| 525 | ||
| 526 | //* Connect to the database | |
| 527 | $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password); | |
| 528 | if ($link->connect_error) { | |
| 529 | $app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR); | |
| 530 | return; | |
| 531 | } | |
| 532 | ||
| 533 | ||
| 534 | if($data['old']['database_user'] == $data['new']['database_user'] && ($data['old']['database_password'] == $data['new']['database_password'] || $data['new']['database_password'] == '')) { | |
| 535 | return; | |
| 536 | } | |
| 537 | ||
| 538 | ||
| 539 | $host_list = array('localhost'); | |
| 540 | // get all databases this user was active for | |
| 4f9dee | 541 | $user_id = intval($data['old']['database_user_id']); |
| FS | 542 | $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 | 543 | if(count($db_list) < 1) return; // nothing to do on this server for this db user |
| MC | 544 | |
| 545 | foreach($db_list as $database) { | |
| 546 | if($database['remote_access'] != 'y') continue; | |
| 547 | ||
| 548 | if($database['remote_ips'] != '') $ips = explode(',', $database['remote_ips']); | |
| 549 | else $ips = array('%'); | |
| 550 | ||
| 551 | foreach($ips as $ip) { | |
| 552 | $ip = trim($ip); | |
| 553 | if(!in_array($ip, $host_list)) $host_list[] = $ip; | |
| 554 | } | |
| 555 | } | |
| 556 | ||
| 557 | foreach($host_list as $db_host) { | |
| 558 | if($data['new']['database_user'] != $data['old']['database_user']) { | |
| 381520 | 559 | $link->query("RENAME USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host' TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host'"); |
| 7fe908 | 560 | $app->log('Renaming MySQL user: '.$data['old']['database_user'].' to '.$data['new']['database_user'], LOGLEVEL_DEBUG); |
| 381520 | 561 | } |
| M | 562 | |
| bfcdef | 563 | if($data['new']['database_password'] != $data['old']['database_password'] && $data['new']['database_password'] != '') { |
| 43b345 | 564 | $link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';"); |
| 7fe908 | 565 | $app->log('Changing MySQL user password for: '.$data['new']['database_user'].'@'.$db_host, LOGLEVEL_DEBUG); |
| 381520 | 566 | } |
| 7fe908 | 567 | } |
| MC | 568 | |
| 569 | $link->query('FLUSH PRIVILEGES;'); | |
| 570 | $link->close(); | |
| 571 | ||
| acf18c | 572 | } |
| 7fe908 | 573 | |
| MC | 574 | function db_user_delete($event_name, $data) { |
| acf18c | 575 | global $app, $conf; |
| 7fe908 | 576 | |
| MC | 577 | if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') { |
| 578 | $app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR); | |
| 579 | return; | |
| 580 | } | |
| 581 | ||
| 582 | //* Connect to the database | |
| 583 | $link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password); | |
| 584 | if ($link->connect_error) { | |
| 585 | $app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR); | |
| 586 | return; | |
| 587 | } | |
| 588 | ||
| 589 | $host_list = array(); | |
| 590 | // read all mysql users with this username | |
| 591 | $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 | |
| 592 | if($result) { | |
| 593 | while($row = $result->fetch_assoc()) { | |
| 594 | $host_list[] = $row['Host']; | |
| 595 | } | |
| 596 | $result->free(); | |
| 597 | } | |
| 598 | ||
| 599 | foreach($host_list as $db_host) { | |
| 600 | if($link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';")) { | |
| 601 | $app->log('Dropping MySQL user: '.$data['old']['database_user'], LOGLEVEL_DEBUG); | |
| 602 | } | |
| 603 | } | |
| 604 | ||
| 605 | $link->query('FLUSH PRIVILEGES;'); | |
| 606 | $link->close(); | |
| acf18c | 607 | } |
| 7fe908 | 608 | |
| d83fcf | 609 | } // end class |
| T | 610 | |
| 663caf | 611 | ?> |
