gitlabFork/ISPConfig3.git - Solinfo Gitblit

Marius Burkard

2016-04-20 4569cae57f127afd093794310ccd290d2d9fdf36

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