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