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

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

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice,
      this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.
    * Neither the name of ISPConfig nor the names of its contributors
      may be used to endorse or promote products derived from this software without
      specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

31
class mysql_clientdb_plugin {
32

33
34
	var $plugin_name = 'mysql_clientdb_plugin';
	var $class_name  = 'mysql_clientdb_plugin';
35

36
37
38
39
	//* This function is called during ispconfig installation to determine
	//  if a symlink shall be created for this plugin.
	function onInstall() {
		global $conf;
40

41
42
43
44
45
		if($conf['services']['db'] == true) {
			return true;
		} else {
			return false;
		}
46

47
	}
48
49


tbrehm's avatar
tbrehm committed
50
51
52
	/*
	 	This function is called when the plugin is loaded
	*/
53

tbrehm's avatar
tbrehm committed
54
55
	function onLoad() {
		global $app;
56

tbrehm's avatar
tbrehm committed
57
58
59
		/*
		Register for the events
		*/
60

61
		//* Databases
62
63
64
65
		$app->plugins->registerEvent('database_insert', $this->plugin_name, 'db_insert');
		$app->plugins->registerEvent('database_update', $this->plugin_name, 'db_update');
		$app->plugins->registerEvent('database_delete', $this->plugin_name, 'db_delete');

66
		//* Database users
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
		$app->plugins->registerEvent('database_user_insert', $this->plugin_name, 'db_user_insert');
		$app->plugins->registerEvent('database_user_update', $this->plugin_name, 'db_user_update');
		$app->plugins->registerEvent('database_user_delete', $this->plugin_name, 'db_user_delete');


	}

	function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '', $user_read_only = false) {
		global $app;

		$action = strtoupper($action);

		// set to all hosts if none given
		if(trim($host_list) == '') $host_list = '%';

		// process arrays and comma separated strings
		if(!is_array($host_list)) $host_list = explode(',', $host_list);

		$success = true;
86
87
88
89
90
91
92
93
		if(!preg_match('/\*[A-F0-9]{40}$/', $database_password)) {
				$result = $link->query("SELECT PASSWORD('" . $link->escape_string($database_password) . "') as `crypted`");
				if($result) {
						$row = $result->fetch_assoc();
						$database_password = $row['crypted'];
						$result->free();
				}
		}
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
		// loop through hostlist
		foreach($host_list as $db_host) {
			$db_host = trim($db_host);

			$app->log($action . ' for user ' . $database_user . ' at host ' . $db_host, LOGLEVEL_DEBUG);

			// check if entry is valid ip address
			$valid = true;
			if($db_host == '%' || $db_host == 'localhost') {
				$valid = true;
			} elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
				$groups = explode('.', $db_host);
				foreach($groups as $group){
					if($group<0 or $group>255)
						$valid=false;
				}
			} else {
				$valid = false;
			}

			if($valid == false) continue;

			if($action == 'GRANT') {
117
118
				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;
				$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
122
123
124
125
126
127
128
129
130
			} elseif($action == 'DROP') {
				if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host';")) $success = false;
			} elseif($action == 'RENAME') {
				if(!$link->query("RENAME USER '".$link->escape_string($database_user)."'@'$db_host' TO '".$link->escape_string($database_rename_user)."'@'$db_host'")) $success = false;
			} elseif($action == 'PASSWORD') {
				if(!$link->query("SET PASSWORD FOR '".$link->escape_string($database_user)."'@'$db_host' = '".$link->escape_string($database_password)."';")) $success = false;
			}
		}

		return $success;
tbrehm's avatar
tbrehm committed
131
	}
132

133
134
	function drop_or_revoke_user($database_id, $user_id, $host_list){
		global $app;
135

136
137
		// set to all hosts if none given
		if(trim($host_list) == '') $host_list = '%';
138

139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
		$db_user_databases = $app->db->queryAllRecords("SELECT * FROM web_database WHERE (database_user_id = ".$user_id." OR database_ro_user_id = ".$user_id.") AND active = 'y' AND database_id != ".$database_id);
		$db_user_host_list = array();
		if(is_array($db_user_databases) && !empty($db_user_databases)){
			foreach($db_user_databases as $db_user_database){
				if($db_user_database['remote_access'] == 'y'){
					if($db_user_database['remote_ips'] == ''){
						$db_user_host_list[] = '%';
					} else {
						$tmp_remote_ips = explode(',', $db_user_database['remote_ips']);
						if(is_array($tmp_remote_ips) && !empty($tmp_remote_ips)){
							foreach($tmp_remote_ips as $tmp_remote_ip){
								$tmp_remote_ip = trim($tmp_remote_ip);
								if($tmp_remote_ip != '') $db_user_host_list[] = $tmp_remote_ip;
							}
						}
						unset($tmp_remote_ips);
					}
				}
				$db_user_host_list[] = 'localhost';
			}
		}
		$host_list_arr = explode(',', $host_list);
		//print_r($host_list_arr);
		$drop_hosts = array_diff($host_list_arr, $db_user_host_list);
		//print_r($drop_hosts);
		$revoke_hosts = array_diff($host_list_arr, $drop_hosts);
		//print_r($revoke_hosts);
166

167
168
169
170
171
172
		$drop_host_list = implode(',', $drop_hosts);
		$revoke_host_list = implode(',', $revoke_hosts);
		//echo $drop_host_list."\n";
		//echo $revoke_host_list."\n";
		return array('revoke_hosts' => $revoke_host_list, 'drop_hosts' => $drop_host_list);
	}
173
174

	function db_insert($event_name, $data) {
tbrehm's avatar
tbrehm committed
175
		global $app, $conf;
176

177
		if($data['new']['type'] == 'mysql') {
178
179
			if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
tbrehm's avatar
tbrehm committed
180
				return;
tbrehm's avatar
tbrehm committed
181
			}
182

tbrehm's avatar
tbrehm committed
183
			//* Connect to the database
184
			$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
185
			if ($link->connect_error) {
186
				$app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR);
187
				return;
tbrehm's avatar
tbrehm committed
188
			}
redray's avatar
redray committed
189
190

			// Charset for the new table
191
			if($data['new']['database_charset'] != '') {
192
				$query_charset_table = ' DEFAULT CHARACTER SET '.$data['new']['database_charset'];
redray's avatar
redray committed
193
			} else {
194
				$query_charset_table = '';
redray's avatar
redray committed
195
196
			}

tbrehm's avatar
tbrehm committed
197
			//* Create the new database
198
			if ($link->query('CREATE DATABASE `'.$link->escape_string($data['new']['database_name']).'`'.$query_charset_table)) {
199
				$app->log('Created MySQL database: '.$data['new']['database_name'], LOGLEVEL_DEBUG);
tbrehm's avatar
tbrehm committed
200
			} else {
201
				$app->log('Unable to create the database: '.$link->error, LOGLEVEL_WARNING);
tbrehm's avatar
tbrehm committed
202
			}
203

204
			// Create the database user if database is active
205
			if($data['new']['active'] == 'y') {
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228

				// get the users for this database
				$db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");

				$db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_ro_user_id']) . "'");

				$host_list = '';
				if($data['new']['remote_access'] == 'y') {
					$host_list = $data['new']['remote_ips'];
					if($host_list == '') $host_list = '%';
				}
				if($host_list != '') $host_list .= ',';
				$host_list .= 'localhost';

				if($db_user) {
					if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
				}
				if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
					if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
				}

tbrehm's avatar
tbrehm committed
229
			}
230

231
232
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
233
234
		}
	}
235
236

	function db_update($event_name, $data) {
tbrehm's avatar
tbrehm committed
237
		global $app, $conf;
238

239
240
		// skip processing if database was and is inactive
		if($data['new']['active'] == 'n' && $data['old']['active'] == 'n') return;
241

242
		if($data['new']['type'] == 'mysql') {
243
244
			if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
245
				return;
tbrehm's avatar
tbrehm committed
246
			}
247

tbrehm's avatar
tbrehm committed
248
			//* Connect to the database
249
			$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
250
			if ($link->connect_error) {
251
				$app->log('Unable to connect to the database: '.$link->connect_error, LOGLEVEL_ERROR);
252
				return;
tbrehm's avatar
tbrehm committed
253
			}
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269

			// get the users for this database
			$db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");
			$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']) . "'");

			$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']) . "'");
			$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']) . "'");

			$host_list = '';
			if($data['new']['remote_access'] == 'y') {
				$host_list = $data['new']['remote_ips'];
				if($host_list == '') $host_list = '%';
			}
			if($host_list != '') $host_list .= ',';
			$host_list .= 'localhost';

270
271
			// REVOKES and DROPS have to be done on old host list, not new host list
			$old_host_list = '';
272
273
274
275
276
277
278
279
			if($data['old']['remote_access'] == 'y') {
				$old_host_list = $data['old']['remote_ips'];
				if($old_host_list == '') $old_host_list = '%';
			}
			if($old_host_list != '') $old_host_list .= ',';
			$old_host_list .= 'localhost';

			// Create the database user if database was disabled before
280
			if($data['new']['active'] == 'y') {
281
282
283
284
285
286
287
288
				if($db_user) {
					if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
				}
				if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
					if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
				}
289
			} else if($data['new']['active'] == 'n' && $data['old']['active'] == 'y') { // revoke database user, if inactive
290
291
292
293
294
295
296
297
298
299
300
301
302
					if($old_db_user) {
						if($old_db_user['database_user'] == 'root'){
							$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						} else {
							// Find out users to drop and users to revoke
							$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
							if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
							if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);


							//$this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link);
							//$this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link);
						}
303
					}
304
305
306
307
308
309
310
311
312
313
314
315
					if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
						if($old_db_ro_user['database_user'] == 'root'){
							$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						} else {
							// Find out users to drop and users to revoke
							$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list);
							if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
							if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);

							//$this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $old_host_list, $link);
							//$this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $old_host_list, $link);
						}
316
					}
317
318
319
320
321
322
323
324
325
326
327
328
329
					// Database is not active, so stop processing here
					$link->query('FLUSH PRIVILEGES;');
					$link->close();
					return;
				}

			//* selected Users have changed
			if($data['new']['database_user_id'] != $data['old']['database_user_id']) {
				if($data['old']['database_user_id'] && $data['old']['database_user_id'] != $data['new']['database_ro_user_id']) {
					if($old_db_user) {
						if($old_db_user['database_user'] == 'root'){
							$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						} else {
330
							// Find out users to drop and users to revoke
331
332
333
							$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
							if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
							if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
334

335
336
337
							//$this->process_host_list('DROP', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link);
							//$this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link);
						}
338
339
340
341
342
343
344
345
346
347
348
349
350
					}
				}
				if($db_user) {
					if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
				}
			}
			if($data['new']['database_ro_user_id'] != $data['old']['database_ro_user_id']) {
				if($data['old']['database_ro_user_id'] && $data['old']['database_ro_user_id'] != $data['new']['database_user_id']) {
					if($old_db_ro_user) {
						if($old_db_ro_user['database_user'] == 'root'){
							$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						} else {
351
							// Find out users to drop and users to revoke
352
353
354
							$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
							if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
							if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
355

356
357
358
							//$this->process_host_list('DROP', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link);
							//$this->process_host_list('REVOKE', $data['new']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $old_host_list, $link);
						}
359
360
361
362
363
364
365
366
					}
				}
				if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
					if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $host_list, $link, '', true);
				}
			}

tbrehm's avatar
tbrehm committed
367
			//* Remote access option has changed.
368
			if($data['new']['remote_access'] != $data['old']['remote_access']) {
369

370
				//* revoke old priveliges
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);
372

373
				//* set new priveliges
374
375
376
377
378
				if($data['new']['remote_access'] == 'y') {
					if($db_user) {
						if($db_user['database_user'] == 'root'){
							$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						} else {
379
380
							$this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
						}
381
382
383
384
385
					}
					if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
						if($db_ro_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						else $this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['new']['remote_ips'], $link, '', true);
					}
tbrehm's avatar
tbrehm committed
386
				} else {
387
388
389
390
					if($old_db_user) {
						if($old_db_user['database_user'] == 'root'){
							$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						} else {
391
							// Find out users to drop and users to revoke
392
393
394
							$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
							if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
							if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
395

396
397
398
							//$this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link);
							//$this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['old']['remote_ips'], $link);
						}
399
400
401
402
403
					}
					if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
						if($old_db_ro_user['database_user'] == 'root'){
							$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
						} else {
404
							// Find out users to drop and users to revoke
405
406
407
							$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $data['old']['remote_ips']);
							if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
							if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
408

409
410
411
							//$this->process_host_list('DROP', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link);
							//$this->process_host_list('REVOKE', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['old']['remote_ips'], $link);
						}
412
					}
tbrehm's avatar
tbrehm committed
413
				}
414
				$app->log('Changing MySQL remote access privileges for database: '.$data['new']['database_name'], LOGLEVEL_DEBUG);
415
			} elseif($data['new']['remote_access'] == 'y' && $data['new']['remote_ips'] != $data['old']['remote_ips']) {
416
417
418
419
420
				//* Change remote access list
				if($old_db_user) {
					if($old_db_user['database_user'] == 'root'){
						$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					} else {
421
422
423
424
						// Find out users to drop and users to revoke
						$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
						if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
						if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
425
426
427
428
429
430
431
432
433
434
435
436
437
438
					}
				}
				if($db_user) {
					if($db_user['database_user'] == 'root'){
						$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					} else {
						$this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $data['new']['remote_ips'], $link);
					}
				}

				if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
					if($old_db_ro_user['database_user'] == 'root'){
						$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					} else {
439
440
441
442
						// Find out users to drop and users to revoke
						$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $data['old']['remote_ips']);
						if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
						if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_ro_user['database_user'], $old_db_ro_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
443
444
445
446
447
448
449
450
451
452
					}
				}

				if($db_ro_user && $data['new']['database_user_id'] != $data['new']['database_ro_user_id']) {
					if($db_ro_user['database_user'] == 'root'){
						$app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
					} else {
						$this->process_host_list('GRANT', $data['new']['database_name'], $db_ro_user['database_user'], $db_ro_user['database_password'], $data['new']['remote_ips'], $link, '', true);
					}
				}
453
			}
454
455


456
457
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
458
		}
459

tbrehm's avatar
tbrehm committed
460
	}
461
462

	function db_delete($event_name, $data) {
tbrehm's avatar
tbrehm committed
463
		global $app, $conf;
464

465
		if($data['old']['type'] == 'mysql') {
466
467
			if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
468
				return;
tbrehm's avatar
tbrehm committed
469
			}
470

tbrehm's avatar
tbrehm committed
471
			//* Connect to the database
472
473
			$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
			if ($link->connect_error) {
474
				$app->log('Unable to connect to mysql: '.$link->connect_error, LOGLEVEL_ERROR);
475
				return;
tbrehm's avatar
tbrehm committed
476
			}
477

478
			$old_host_list = '';
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
			if($data['old']['remote_access'] == 'y') {
				$old_host_list = $data['old']['remote_ips'];
				if($old_host_list == '') $old_host_list = '%';
			}
			if($old_host_list != '') $old_host_list .= ',';
			$old_host_list .= 'localhost';

			if($data['old']['database_user_id']) {
				$old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
				$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
				if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
				if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
			}
			if($data['old']['database_ro_user_id']) {
				$old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_ro_user_id']) . "'");
				$drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list);
				if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
				if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
			}


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

506
507
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
508
		}
509
510


tbrehm's avatar
tbrehm committed
511
	}
512
513
514


	function db_user_insert($event_name, $data) {
515
		global $app, $conf;
516
		// we have nothing to do here, stale user accounts are useless ;)
517
	}
518
519

	function db_user_update($event_name, $data) {
520
		global $app, $conf;
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558

		if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
			$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
			return;
		}

		//* Connect to the database
		$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
		if ($link->connect_error) {
			$app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR);
			return;
		}


		if($data['old']['database_user'] == $data['new']['database_user'] && ($data['old']['database_password'] == $data['new']['database_password'] || $data['new']['database_password'] == '')) {
			return;
		}


		$host_list = array('localhost');
		// get all databases this user was active for
		$db_list = $app->db->queryAllRecords("SELECT `remote_access`, `remote_ips` FROM `web_database` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
		if(count($db_list) < 1) return; // nothing to do on this server for this db user

		foreach($db_list as $database) {
			if($database['remote_access'] != 'y') continue;

			if($database['remote_ips'] != '') $ips = explode(',', $database['remote_ips']);
			else $ips = array('%');

			foreach($ips as $ip) {
				$ip = trim($ip);
				if(!in_array($ip, $host_list)) $host_list[] = $ip;
			}
		}

		foreach($host_list as $db_host) {
			if($data['new']['database_user'] != $data['old']['database_user']) {
559
				$link->query("RENAME USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host' TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host'");
560
				$app->log('Renaming MySQL user: '.$data['old']['database_user'].' to '.$data['new']['database_user'], LOGLEVEL_DEBUG);
561
562
			}

563
			if($data['new']['database_password'] != $data['old']['database_password'] && $data['new']['database_password'] != '') {
564
				$link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");
565
				$app->log('Changing MySQL user password for: '.$data['new']['database_user'].'@'.$db_host, LOGLEVEL_DEBUG);
566
			}
567
568
569
570
571
		}

		$link->query('FLUSH PRIVILEGES;');
		$link->close();

572
	}
573
574

	function db_user_delete($event_name, $data) {
575
		global $app, $conf;
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606

		if(!include ISPC_LIB_PATH.'/mysql_clientdb.conf') {
			$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf', LOGLEVEL_ERROR);
			return;
		}

		//* Connect to the database
		$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
		if ($link->connect_error) {
			$app->log('Unable to connect to mysql'.$link->connect_error, LOGLEVEL_ERROR);
			return;
		}

		$host_list = array();
		// read all mysql users with this username
		$result = $link->query("SELECT `User`, `Host` FROM `mysql`.`user` WHERE `User` = '" . $link->escape_string($data['old']['database_user']) . "' AND `Create_user_priv` = 'N'"); // basic protection against accidently deleting system users like debian-sys-maint
		if($result) {
			while($row = $result->fetch_assoc()) {
				$host_list[] = $row['Host'];
			}
			$result->free();
		}

		foreach($host_list as $db_host) {
			if($link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';")) {
				$app->log('Dropping MySQL user: '.$data['old']['database_user'], LOGLEVEL_DEBUG);
			}
		}

		$link->query('FLUSH PRIVILEGES;');
		$link->close();
607
	}
608

tbrehm's avatar
tbrehm committed
609
610
} // end class

611
?>