mysql_clientdb_plugin.inc.php 13.6 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 {
tbrehm's avatar
tbrehm committed
32
	
33
34
	var $plugin_name = 'mysql_clientdb_plugin';
	var $class_name  = 'mysql_clientdb_plugin';
tbrehm's avatar
tbrehm committed
35
	
36
37
38
39
40
41
42
43
44
45
46
47
48
	//* This function is called during ispconfig installation to determine
	//  if a symlink shall be created for this plugin.
	function onInstall() {
		global $conf;
		
		if($conf['services']['db'] == true) {
			return true;
		} else {
			return false;
		}
		
	}
	
tbrehm's avatar
tbrehm committed
49
50
51
52
53
54
55
56
57
58
59
60
		
	/*
	 	This function is called when the plugin is loaded
	*/
	
	function onLoad() {
		global $app;
		
		/*
		Register for the events
		*/
		
61
		//* Databases
tbrehm's avatar
tbrehm committed
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
67
68
69
70
		//* Database users
		//$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');
		
tbrehm's avatar
tbrehm committed
71
72
73
		
	}
	
74
  function process_host_list($action, $database_name, $database_user, $database_password, $host_list, $link, $database_rename_user = '') {
75
76
77
78
79
      global $app;
      
      $action = strtoupper($action);
      
      // set to all hosts if none given
80
      if(trim($host_list) == '') $host_list = '%';
81
82
      
      // process arrays and comma separated strings
83
      if(!is_array($host_list)) $host_list = explode(',', $host_list);
84
85
86
87
88
89
90
91
92
      
      $success = true;
      
      // loop through hostlist
      foreach($host_list as $db_host) {
          $db_host = trim($db_host);
          
          // check if entry is valid ip address
          $valid = true;
93
		  if($db_host == '%') {
94
95
		  	$valid = true;
		  } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
96
              $groups = explode('.', $db_host);
97
98
99
100
101
102
103
104
105
106
              foreach($groups as $group){
                if($group<0 OR $group>255)
                $valid=false;
              }
          } else {
              $valid = false;
          }
          
          if($valid == false) continue;
          
107
          if($action == 'GRANT') {
108
              if(!$link->query("GRANT 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;
109
          } elseif($action == 'REVOKE') {
110
              //mysql_query("REVOKE ALL PRIVILEGES ON ".mysql_real_escape_string($database_name,$link).".* FROM '".mysql_real_escape_string($database_user,$link)."';",$link);
111
          } elseif($action == 'DROP') {
112
              if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host';")) $success = false;
113
          } elseif($action == 'RENAME') {
114
              if(!$link->query("RENAME USER '".$link->escape_string($database_user)."'@'$db_host' TO '".$link->escape_string($database_rename_user)."'@'$db_host'")) $success = false;
115
          } elseif($action == 'PASSWORD') {
116
              if(!$link->query("SET PASSWORD FOR '".$link->escape_string($database_user)."'@'$db_host' = '".$link->escape_string($database_password)."';")) $success = false;
117
118
119
120
121
          }
      }
      
      return $success;
  }
tbrehm's avatar
tbrehm committed
122
123
124
125
	
	function db_insert($event_name,$data) {
		global $app, $conf;
		
126
		if($data['new']['type'] == 'mysql') {
127
			if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
tbrehm's avatar
tbrehm committed
128
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
tbrehm's avatar
tbrehm committed
129
				return;
tbrehm's avatar
tbrehm committed
130
			}
131
			
132
			if($data['new']['database_user'] == 'root') {
133
134
135
				$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
				return;
			}
tbrehm's avatar
tbrehm committed
136
137
		
			//* Connect to the database
138
			$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
139
			if ($link->connect_error) {
140
				$app->log('Unable to connect to mysql'.$link->connect_error,LOGLEVEL_ERROR);
141
				return;
tbrehm's avatar
tbrehm committed
142
			}
redray's avatar
redray committed
143
144

			// Charset for the new table
145
146
			if($data['new']['database_charset'] != '') {
        $query_charset_table = ' DEFAULT CHARACTER SET '.$data['new']['database_charset'];
redray's avatar
redray committed
147
148
149
150
			} else {
        $query_charset_table = '';
			}

tbrehm's avatar
tbrehm committed
151
			//* Create the new database
152
			if ($link->query('CREATE DATABASE '.$link->escape_string($data['new']['database_name']).$query_charset_table)) {
153
				$app->log('Created MySQL database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
tbrehm's avatar
tbrehm committed
154
			} else {
155
				$app->log('Unable to create the database: '.$link->error,LOGLEVEL_WARNING);
tbrehm's avatar
tbrehm committed
156
157
			}
			
158
			// Create the database user if database is active
159
			if($data['new']['active'] == 'y') {
160
				
161
162
				if($data['new']['remote_access'] == 'y') {
          $this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
163
164
				}
				
165
				$db_host = 'localhost';
166
				$link->query("GRANT ALL ON `".str_replace(array('_','%'),array('\\_','\\%'),$link->escape_string($data['new']['database_name']))."`.* TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($data['new']['database_password'])."';");
167
168

				
tbrehm's avatar
tbrehm committed
169
170
			}
			
171
172
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
173
174
175
176
177
178
		}
	}
	
	function db_update($event_name,$data) {
		global $app, $conf;
		
179
		if($data['new']['type'] == 'mysql') {
180
			if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
tbrehm's avatar
tbrehm committed
181
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
182
				return;
tbrehm's avatar
tbrehm committed
183
			}
184
			
185
			if($data['new']['database_user'] == 'root') {
186
187
188
189
				$app->log('User root not allowed for Client databases',LOGLEVEL_WARNING);
				return;
			}
			
tbrehm's avatar
tbrehm committed
190
			//* Connect to the database
191
			$link = new mysqli($clientdb_host, $clientdb_user, $clientdb_password);
192
			if ($link->connect_error) {
193
				$app->log('Unable to connect to the database: '.$link->connect_error,LOGLEVEL_ERROR);
194
				return;
tbrehm's avatar
tbrehm committed
195
196
			}
			
197
			// Create the database user if database was disabled before
198
			if($data['new']['active'] == 'y' && $data['old']['active'] == 'n') {
199
				
200
				if($data['new']['remote_access'] == 'y') {
201
				  $this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
202
203
				}
				
204
				$db_host = 'localhost';
205
				$link->query("GRANT ALL ON `".str_replace(array('_','%'),array('\\_','\\%'),$link->escape_string($data['new']['database_name']))."`.* TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($data['new']['database_password'])."';");
206
				
207
				// mysql_query("GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"],$link).".* TO '".mysql_real_escape_string($data["new"]["database_user"],$link)."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"],$link)."';",$link);
208
209
210
211
				//echo "GRANT ALL ON ".mysql_real_escape_string($data["new"]["database_name"]).".* TO '".mysql_real_escape_string($data["new"]["database_user"])."'@'$db_host' IDENTIFIED BY '".mysql_real_escape_string($data["new"]["database_password"])."';";
			}
			
			// Remove database user, if inactive
212
			if($data['new']['active'] == 'n' && $data['old']['active'] == 'y') {
213
				
214
215
				if($data['old']['remote_access'] == 'y') {
          $this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link);
216
217
				}
				
218
				$db_host = 'localhost';
219
				$link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';");
220
				//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);
221
222
			}
			
tbrehm's avatar
tbrehm committed
223
			//* Rename User
224
			if($data['new']['database_user'] != $data['old']['database_user']) {
225
				$db_host = 'localhost';
226
				$link->query("RENAME USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host' TO '".$link->escape_string($data['new']['database_user'])."'@'$db_host'");
227
				if($data['old']['remote_access'] == 'y') {
228
					$this->process_host_list('RENAME', '', $data['old']['database_user'], '', $data['new']['remote_ips'], $link, $data['new']['database_user']);
229
				}
230
				$app->log('Renaming MySQL user: '.$data['old']['database_user'].' to '.$data['new']['database_user'],LOGLEVEL_DEBUG);
tbrehm's avatar
tbrehm committed
231
232
233
			}
			
			//* Remote access option has changed.
234
			if($data['new']['remote_access'] != $data['old']['remote_access']) {
235
236
				
				//* revoke old priveliges
237
				//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);
238
239
				
				//* set new priveliges
240
241
				if($data['new']['remote_access'] == 'y') { 		
					$this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
tbrehm's avatar
tbrehm committed
242
				} else {
243
					$this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link);
tbrehm's avatar
tbrehm committed
244
				}
245
246
				$app->log('Changing MySQL remote access privileges for database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
			} elseif($data['new']['remote_access'] == 'y' && $data['new']['remote_ips'] != $data['old']['remote_ips']) {
247
          //* Change remote access list
248
249
          $this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link);
          $this->process_host_list('GRANT', $data['new']['database_name'], $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'], $link);
250
251
      }
      
tbrehm's avatar
tbrehm committed
252
			//* Change password
253
			if($data['new']['database_password'] != $data['old']['database_password']) {
254
				$db_host = 'localhost';
255
				$link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");
256

257
				if($data['new']['remote_access'] == 'y') {
258
					$this->process_host_list('PASSWORD', '', $data['new']['database_user'], $data['new']['database_password'], $data['new']['remote_ips'],$link);
259
				}
260
				$app->log('Changing MySQL user password for: '.$data['new']['database_user'],LOGLEVEL_DEBUG);
tbrehm's avatar
tbrehm committed
261
262
			}
			
263
264
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
265
266
267
268
269
270
271
		}
		
	}
	
	function db_delete($event_name,$data) {
		global $app, $conf;
		
272
		if($data['old']['type'] == 'mysql') {
273
			if(!include(ISPC_LIB_PATH.'/mysql_clientdb.conf')) {
tbrehm's avatar
tbrehm committed
274
				$app->log('Unable to open'.ISPC_LIB_PATH.'/mysql_clientdb.conf',LOGLEVEL_ERROR);
275
				return;
tbrehm's avatar
tbrehm committed
276
277
278
			}
		
			//* Connect to the database
279
280
281
			$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);
282
				return;
tbrehm's avatar
tbrehm committed
283
284
			}
			
285
			//* Get the db host setting for the access priveliges
286
287
288
			if($data['old']['remote_access'] == 'y') {
			 	if($this->process_host_list('DROP', '', $data['old']['database_user'], '', $data['old']['remote_ips'], $link)) {
        	$app->log('Dropping MySQL user: '.$data['old']['database_user'],LOGLEVEL_DEBUG);
289
				} else {
290
					$app->log('Error while dropping MySQL user: '.$data['old']['database_user'].' '.$link->error,LOGLEVEL_WARNING);
291
				}
292
			}
293
			$db_host = 'localhost';
294
			if($link->query("DROP USER '".$link->escape_string($data['old']['database_user'])."'@'$db_host';")) {
295
				$app->log('Dropping MySQL user: '.$data['old']['database_user'],LOGLEVEL_DEBUG);
296
			} else {
297
				$app->log('Error while dropping MySQL user: '.$data['old']['database_user'].' '.$link->error,LOGLEVEL_WARNING);
298
			}
tbrehm's avatar
tbrehm committed
299
			
300
			if($link->query('DROP DATABASE '.$link->escape_string($data['old']['database_name']))) {
301
				$app->log('Dropping MySQL database: '.$data['old']['database_name'],LOGLEVEL_DEBUG);
302
			} else {
303
				$app->log('Error while dropping MySQL database: '.$data['old']['database_name'].' '.$link->error,LOGLEVEL_WARNING);
304
			}
tbrehm's avatar
tbrehm committed
305
			
306
307
			$link->query('FLUSH PRIVILEGES;');
			$link->close();
tbrehm's avatar
tbrehm committed
308
309
310
311
312
		}
		
		
	}
	
313
314
315
316
317
	/*
	function db_user_insert($event_name,$data) {
		global $app, $conf;
		
	}
tbrehm's avatar
tbrehm committed
318
	
319
320
321
322
323
324
325
326
327
328
	function db_user_update($event_name,$data) {
		global $app, $conf;
		
	}
	
	function db_user_delete($event_name,$data) {
		global $app, $conf;
		
	}
	*/
tbrehm's avatar
tbrehm committed
329
330
331
332
	

} // end class

333
?>