source: alternc/trunk/bureau/class/m_mysql.php @ 1707

Revision 1707, 20.9 KB checked in by anarcat, 7 years ago (diff)

ne pas donner les permissions sur les bases deja existante. reste a tester. see #698
Line 
1<?php
2/*
3 $Id: m_mysql.php,v 1.35 2005/12/18 09:51:32 benjamin Exp $
4 ----------------------------------------------------------------------
5 AlternC - Web Hosting System
6 Copyright (C) 2002 by the AlternC Development Team.
7 http://alternc.org/
8 ----------------------------------------------------------------------
9 Based on:
10 Valentin Lacambre's web hosting softwares: http://altern.org/
11 ----------------------------------------------------------------------
12 LICENSE
13
14 This program is free software; you can redistribute it and/or
15 modify it under the terms of the GNU General Public License (GPL)
16 as published by the Free Software Foundation; either version 2
17 of the License, or (at your option) any later version.
18
19 This program is distributed in the hope that it will be useful,
20 but WITHOUT ANY WARRANTY; without even the implied warranty of
21 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
22 GNU General Public License for more details.
23
24 To read the license please visit http://www.gnu.org/copyleft/gpl.html
25 ----------------------------------------------------------------------
26 Original Author of file: Benjamin Sonntag
27 Purpose of file: Manage mysql database for users.
28 ----------------------------------------------------------------------
29*/
30/**
31 * MySQL user database management for AlternC.
32 * This class manage user's databases in MySQL, and user's MySQL accounts.
33 *
34 * @copyright    AlternC-Team 2002-2005 http://alternc.org/
35 */
36class m_mysql {
37
38  var $server;
39  var $client;
40
41  /*---------------------------------------------------------------------------*/
42  /** Constructor
43  * m_mysql([$mid]) Constructeur de la classe m_mysql, initialise le membre concerne
44  */
45  function m_mysql() {
46      $this->server = $GLOBALS['L_MYSQL_HOST'];
47      $this->client = $GLOBALS['L_MYSQL_CLIENT'];
48  }
49
50  /* ----------------------------------------------------------------- */
51  /** Hook called by m_quota to obtain the quota managed by this class.
52   * Quota name
53   */
54  function alternc_quota_names() {
55    return array("mysql","mysql_users");
56  }
57
58  /*---------------------------------------------------------------------------*/
59  /** Get the list of the database for the current user.
60   * @return array returns an associative array as follow : <br>
61   *  "db" => database name "bck" => backup mode for this db
62   *  "dir" => Backup folder.
63   *  "size" => Size of the database (in bytes)
64   *  Returns FALSE if the user has no database.
65   */
66  function get_dblist() {
67    global $db,$err,$bro,$cuid;
68    $err->log("mysql","get_dblist");
69    $db->query("SELECT login,pass,db, bck_mode, bck_dir FROM db WHERE uid='$cuid';");
70    if (!$db->num_rows()) {
71      $err->raise("mysql",11);
72      return false;
73    }
74    $c=array();
75    while ($db->next_record()) {
76      list($dbu,$dbn)=split_mysql_database_name($db->f("db"));
77      $c[]=array("db"=>$db->f("db"), "name"=>$dbn,"bck"=>$db->f("bck_mode"), "dir"=>$db->f("bck_dir"), "login"=>$db->f("login"), "pass"=>$db->f("pass"));
78    }
79   
80    /* find the size of each database */
81    foreach ($c as $key => $val) {
82      $c[$key]['size'] = $this->get_db_size($c[$key]['db']);
83    }
84    return $c;
85  }
86
87  /*---------------------------------------------------------------------------*/
88  /** Returns the details of a user's database.
89   * $dbn is the name of the database (after the _) or nothing for the database "$user"
90   * @return array returns an associative array as follow :
91   *  "db" => Name of the database
92   *  "bck" => Current bckup mode
93   *  "dir" => Backup directory
94   *  "size" => Size of the database (in bytes)
95   *  "pass" => Password of the user
96   *  "history" => Number of backup we keep
97   *  "gzip" => Does we compress the dumps ?
98   *  Returns FALSE if the user has no database of if the database does not exist.
99   */
100  function get_mysql_details($dbn) {
101    global $db,$err,$bro,$mem,$cuid;
102    $root="/var/alternc/html/".substr($mem->user["login"],0,1)."/".$mem->user["login"];
103    $err->log("mysql","get_mysql_details");
104    $dbname=$mem->user["login"].($dbn?"_":"").$dbn;
105    $size=$this->get_db_size($dbname);
106    $db->query("SELECT login,pass,db, bck_mode, bck_gzip, bck_dir, bck_history FROM db WHERE uid='$cuid' AND db='$dbname';");
107    if (!$db->num_rows()) {
108      $err->raise("mysql",4);
109      return array("enabled"=>false);
110    }
111    $c=array();
112    $db->next_record();
113    list($dbu,$dbn)=split_mysql_database_name($db->f("db"));
114    return array("enabled"=>true,"login"=>$db->f("login"),"db"=>$db->f("db"), "name"=>$dbn,"bck"=>$db->f("bck_mode"), "dir"=>substr($db->f("bck_dir"),strlen($root)), "size"=>$size, "pass"=>$db->f("pass"), "history"=>$db->f("bck_history"), "gzip"=>$db->f("bck_gzip"));
115  }
116
117  /*---------------------------------------------------------------------------*/
118  /** Create a new database for the current user.
119   * @param $dbn string Database name ($user_$dbn is the mysql db name)
120   * @return TRUE if the database $user_$db has been successfully created, or FALSE if
121   * an error occured, such as over quota user.
122   */
123  function add_db($dbn) {
124    global $db,$err,$quota,$mem,$cuid;
125    $err->log("mysql","add_db",$dbn);
126    if (!$quota->cancreate("mysql")) {
127      $err->raise("mysql",1);
128      return false;
129    }
130    if (!ereg("^[0-9a-z]*$",$dbn)) {
131      $err->raise("mysql",2);
132      return false;
133    }
134    $dbname=$mem->user["login"].($dbn?"_":"").$dbn;
135    if (strlen($dbname) > 64) {
136      $err->raise("mysql",12);
137      return false;
138    }
139    $db->query("SELECT * FROM db WHERE db='$dbname';");
140    if ($db->num_rows()) {
141      $err->raise("mysql",3);
142      return false;
143    }
144    // find the login/pass for this user :
145    $db->query("SELECT login,pass FROM db WHERE uid='$cuid' LIMIT 0,1;");
146    if (!$db->num_rows()) {
147      $lo=$mem->user["login"];
148      $pa="";
149    } else {
150      $db->next_record();
151      $lo=addslashes($db->f("login"));
152      $pa=addslashes($db->f("pass"));
153    }
154    if ($db->query("CREATE DATABASE $dbname;")) {
155      // Ok, database does not exist, quota is ok and dbname is compliant. Let's proceed
156      $db->query("INSERT INTO db (uid,login,pass,db,bck_mode) VALUES ('$cuid','$lo','$pa','$dbname',0);");
157      // give everything but GRANT on db.*
158      // we assume there's already a user
159      $db->query("GRANT ALL PRIVILEGES ON `".$dbname."`.* TO '".$lo."'@'$this->client'");
160      return true;
161    } else {
162      $err->raise("mysql",3);
163      return false;
164    }
165  }
166
167  /*---------------------------------------------------------------------------*/
168  /** Delete a database for the current user.
169   * @param $dbn string Name of the database to delete. The db name is $user_$dbn
170   * @return TRUE if the database $user_$db has been successfully deleted, or FALSE if
171   *  an error occured, such as db does not exist.
172   */
173  function del_db($dbn) {
174    global $db,$err,$mem,$cuid;
175    $err->log("mysql","del_db",$dbn);
176    if (!ereg("^[0-9a-z]*$",$dbn)) {
177      $err->raise("mysql",2);
178      return false;
179    }
180    $dbname=$mem->user["login"].($dbn?"_":"").$dbn;
181    $db->query("SELECT login FROM db WHERE db='$dbname';");
182    if (!$db->num_rows()) {
183      $err->raise("mysql",4);
184      return false;
185    }
186    $db->next_record();
187    $login=$db->f("login");
188
189    // Ok, database exists and dbname is compliant. Let's proceed
190    $db->query("DELETE FROM db WHERE uid='$cuid' AND db='$dbname';");
191    $db->query("DROP DATABASE `$dbname`;");
192    $db->query("SELECT COUNT(*) AS cnt FROM db WHERE uid='$cuid';");
193    $db->next_record();
194    $db->query("REVOKE ALL PRIVILEGES ON `".$dbname."`.* FROM '".$login."'@'$this->client'");
195    if ($db->f("cnt")==0) {
196      $db->query("DELETE FROM mysql.user WHERE User='".$login."';");
197      $db->query("FLUSH PRIVILEGES;");
198    }
199    return true;
200  }
201 
202  /*---------------------------------------------------------------------------*/
203  /** Set the backup parameters for the database $db
204   * @param $db string database name
205   * @param $bck_mode integer Backup mode (0 = none 1 = daily 2 = weekly)
206   * @param $bck_history integer How many backup should we keep ?
207   * @param $bck_gzip boolean shall we compress the backup ?
208   * @param $bck_dir string Directory relative to the user account where the backup will be stored
209   * @return boolean true if the backup parameters has been successfully changed, false if not.
210   */
211  function put_mysql_backup($dbn,$bck_mode,$bck_history,$bck_gzip,$bck_dir) {
212    global $db,$err,$mem,$bro,$cuid;
213    $err->log("mysql","put_mysql_backup");
214    if (!ereg("^[0-9a-z]*$",$dbn)) {
215      $err->raise("mysql",2);
216      return false;
217    }
218    $dbname=$mem->user["login"].($dbn?"_":"").$dbn;
219    $db->query("SELECT * FROM db WHERE uid='$cuid' AND db='$dbname';");
220    if (!$db->num_rows()) {
221      $err->raise("mysql",4);
222      return false;
223    }
224    $db->next_record();
225    $bck_mode=intval($bck_mode);
226    $bck_history=intval($bck_history);
227    if ($bck_gzip)
228      $bck_gzip="1";
229    else
230      $bck_gzip="0";
231    if (!$bck_mode)
232      $bck_mode="0";
233    if (!$bck_history) {
234      $err->raise("mysql",5);
235      return false;
236    }
237    if (($bck_dir=$bro->convertabsolute($bck_dir,0))===false) { // return a full path or FALSE
238      $err->raise("mysql",6);
239      return false;
240    }
241    $db->query("UPDATE db SET bck_mode='$bck_mode', bck_history='$bck_history', bck_gzip='$bck_gzip', bck_dir='$bck_dir' WHERE uid='$cuid' AND db='$dbname';");
242    return true;
243  }
244
245  /*---------------------------------------------------------------------------*/
246  /** Change the password of the user in MySQL
247   * @param $password string new password (cleartext)
248   * @return boolean TRUE if the password has been successfully changed, FALSE else.
249   */
250  function put_mysql_details($password) {
251    global $db,$err,$mem,$cuid;
252    $err->log("mysql","put_mysql_details");
253    $db->query("SELECT * FROM db WHERE uid='$cuid';");
254    if (!$db->num_rows()) {
255      $err->raise("mysql",7);
256      return false;
257    }
258    $db->next_record();
259    $login=$db->f("login");
260
261    if (strlen($password)>16) {
262      $err->raise("mysql",8);
263      return false;
264    }
265    // Update all the "pass" fields for this user :
266    $db->query("UPDATE db SET pass='$password' WHERE uid='$cuid';");
267    $db->query("SET PASSWORD FOR '$login'@'$this->client' = PASSWORD('$password')");
268    return true;
269  }
270
271  /* ----------------------------------------------------------------- */
272  /** Create a new mysql account for this user
273   * @param string cleartext password for the new account
274   * It also create the first database.
275   */
276  function new_mysql($password) {
277    global $db,$err,$mem,$cuid;
278    $err->log("mysql","new_mysql");
279    if (strlen($password)>16) {
280      $err->raise("mysql",8);
281      return false;
282    }
283    $db->query("SELECT * FROM db WHERE uid='$cuid';");
284    if ($db->num_rows()) {
285      $err->raise("mysql",10);
286      return false;
287    }
288    $login=$mem->user["login"];
289    $dbname=$mem->user["login"];
290    // OK, creation now...
291    $db->query("INSERT INTO db (uid,login,pass,db) VALUES ('$cuid','".$login."','$password','".$dbname."');");
292    // give everything but GRANT on $user.*
293    $db->query("GRANT ALL PRIVILEGES ON `".$dbname."`.* TO '".$login."'@'$this->client' IDENTIFIED BY '".$password."'");
294    $db->query("CREATE DATABASE `".$dbname."`;");
295    return true;
296  }
297
298
299  /* ----------------------------------------------------------------- */
300  /** Restore a sql backup script on a user's database.
301   */
302  function restore($file,$stdout,$id) { 
303    global $err,$bro,$mem,$L_MYSQL_HOST;
304    if (!$r=$this->get_mysql_details($id)) { 
305      return false; 
306    } 
307    if (!($fi=$bro->convertabsolute($file,0))) {
308      $err->raise("mysql",9);
309      return false; 
310    }
311    if (substr($fi,-3)==".gz") {
312      $exe="/bin/gzip -d -c <".escapeshellarg($fi)." | /usr/bin/mysql -h".escapeshellarg($L_MYSQL_HOST)." -u".escapeshellarg($r["login"])." -p".escapeshellarg($r["pass"])." ".escapeshellarg($r["db"]); 
313    } elseif (substr($fi,-4)==".bz2") { 
314      $exe="/bin/bunzip2 -d -c <".escapeshellarg($fi)." | /usr/bin/mysql -h".escapeshellarg($L_MYSQL_HOST)." -u".escapeshellarg($r["login"])." -p".escapeshellarg($r["pass"])." ".escapeshellarg($r["db"]); 
315    } else { 
316      $exe="/usr/bin/mysql -h".escapeshellarg($L_MYSQL_HOST)." -u".escapeshellarg($r["login"])." -p".escapeshellarg($r["pass"])." ".escapeshellarg($r["db"])." <".escapeshellarg($fi); 
317    }
318    $exe .= " 2>&1";
319   
320    echo "<code><pre>" ;
321    if ($stdout) {
322      passthru($exe,$ret);
323    } else {
324      exec ($exe,$ret);
325    }
326    echo "</pre></code>" ;
327    if ($ret != 0) {
328      return false ;
329    } else {
330      return true ;
331    }
332  }
333 
334  /* ----------------------------------------------------------------- */
335  /** Get size of a database
336   * @param $dbname name of the database
337   * @return integer database size
338   * @access private
339   */
340 function get_db_size($dbname) {
341   global $db,$err;
342
343   $db->query("SHOW TABLE STATUS FROM `$dbname`;");
344   $size = 0;
345   while ($db->next_record()) {
346     $size += $db->f('Data_length') + $db->f('Index_length')
347              + $db->f('Data_free');
348   }
349   return $size;
350 }
351 
352  /* ----------------------------------------------------------------- */
353  /** Hook function called by the quota class to compute user used quota
354   * Returns the used quota for the $name service for the current user.
355   * @param $name string name of the quota
356   * @return integer the number of service used or false if an error occured
357   * @access private
358   */
359  function alternc_get_quota($name) {
360    global $err,$db,$cuid;
361    if ($name=="mysql") {
362      $err->log("mysql","alternc_get_quota");
363      $c=$this->get_dblist();
364      if (is_array($c)) {
365        return count($c);
366      } else {
367        return 0;
368      }
369    } elseif ($name=="mysql_users") {
370      $err->log("mysql","alternc_get_quota");
371      $c=$this->get_userslist();
372      if(is_array($c))
373        return count($c);
374      else
375        return 0;
376    } else return false;
377  }
378
379
380  /* ----------------------------------------------------------------- */
381  /** Hook function called when a user is deleted.
382   * AlternC's standard function that delete a member
383   */
384  function alternc_del_member() {
385    global $db,$err,$cuid;
386    $err->log("mysql","alternc_del_member");
387    $c=$this->get_dblist();
388    if (is_array($c)) {
389      for($i=0;$i<count($c);$i++) {
390        $this->del_db($c[$i]["name"]);
391      }
392    }
393    return true;
394  }
395
396
397  /* ----------------------------------------------------------------- */
398  /**
399   * Exporte toutes les informations mysql du compte.
400   * @access private
401   * EXPERIMENTAL 'sid' function ;)
402   */
403  function alternc_export($tmpdir) {
404    global $db,$err,$cuid;
405    $err->log("mysql","export");
406    $db->query("SELECT login, pass, db, bck_mode, bck_dir, bck_history, bck_gzip FROM db WHERE uid='$cuid';");
407    if ($db->next_record()) {
408      $str="<mysql>\n";
409      $str.="  <login>".xml_entities($db->Record["login"])."</login>";
410      $str.="  <pass>".xml_entities($db->Record["pass"])."</pass>";
411      do {
412        // Do the dump :
413        $filename=$tmpdir."/mysql.".$db->Record["db"].".sql.gz";
414        exec("/usr/bin/mysqldump --add-drop-table --allow-keywords -Q -f -q -a -e -u".escapeshellarg($db->Record["login"])." -p".escapeshellarg($db->Record["pass"])." ".escapeshellarg($db->Record["db"])." |/bin/gzip >".escapeshellarg($filename));
415        $str.="  <db>\n";
416        $str.="    <name>".xml_entities($db->Record["db"])."</name>\n";
417        if ($s["bck_mode"]!=0) {
418          $str.="    <backup>\n";
419          $str.="      <mode>".xml_entities($db->Record["bck_mode"])."</mode>\n";
420          $str.="      <dir>".xml_entities($db->Record["bck_dir"])."</dir>\n";
421          $str.="      <history>".xml_entities($db->Record["bck_history"])."</history>\n";
422          $str.="      <gzip>".xml_entities($db->Record["bck_gzip"])."</gzip>\n";
423          $str.="    </backup>\n";
424        }
425        $str.="  </db>\n";
426      } while ($db->next_record());
427      $str.="</mysql>\n";
428    }
429    return $str;
430  }
431
432  function get_userslist() {
433    global $db,$err,$bro,$cuid;
434    $err->log("mysql","get_userslist");
435    $db->query("SELECT name FROM dbusers WHERE uid='$cuid';");
436    if (!$db->num_rows()) {
437      $err->raise("mysql",19);
438      return false;
439    }
440    $c=array();
441    while ($db->next_record()) {
442      $c[]=array("name"=>substr($db->f("name"),strpos($db->f("name"),"_")+1));
443    }
444
445    return $c;
446  }
447
448  function add_user($usern,$password,$passconf) {
449    global $db,$err,$quota,$mem,$cuid;
450    $err->log("mysql","add_user",$usern);
451   
452    $user=addslashes($mem->user["login"]."_$usern");
453    $pass=addslashes($password);
454       
455    if (!$quota->cancreate("mysql_users")) {
456      $err->raise("mysql",13);
457      return false;
458    }
459    if (!ereg("^[0-9a-z]",$usern)) {
460      $err->raise("mysql",14);
461      return false;
462    }
463   
464    if (strlen($user) > 16 || strlen($usern) == 0 ) {
465      $err->raise("mysql",15);
466      return false;
467    }
468    $db->query("SELECT * FROM dbusers WHERE name='$user';");
469    if ($db->num_rows()) {
470      $err->raise("mysql",16);
471      return false;
472    }
473    if ($password != $passconf || !$password) {
474      $err->raise("mysql",17);
475      return false;
476    }
477
478
479    // On créé l'utilisateur
480    $db->query("GRANT USAGE ON *.* TO '$user'@'$this->client' IDENTIFIED BY '$pass';");
481    // On le rajoute dans la table des utilisateurs
482    $db->query("INSERT INTO dbusers (uid,name) VALUES($cuid,'$user');");
483    return true;
484  }
485
486  function del_user($user) {
487    global $db,$err,$mem,$cuid,$L_MYSQL_DATABASE;
488    $err->log("mysql","del_user",$user);
489    if (!ereg("^[0-9a-z]",$user)) {
490      $err->raise("mysql",14);
491      return false;
492    }
493    $db->query("SELECT name FROM dbusers WHERE name='".$mem->user["login"]."_$user';");
494    if (!$db->num_rows()) {
495      $err->raise("mysql",18);
496      return false;
497    }
498    $db->next_record();
499    $login=$db->f("name");
500
501    // Ok, database exists and dbname is compliant. Let's proceed
502    $db->query("REVOKE ALL PRIVILEGES ON *.* FROM '".$mem->user["login"]."_$user'@'$this->client';");
503    $db->query("DELETE FROM mysql.db WHERE User='".$mem->user["login"]."_$user' AND Host='$this->client';");
504    $db->query("DELETE FROM mysql.user WHERE User='".$mem->user["login"]."_$user' AND Host='$this->client';");
505    $db->query("FLUSH PRIVILEGES");
506    $db->query("DELETE FROM dbusers WHERE uid='$cuid' AND name='".$mem->user["login"]."_$user';");
507    return true;
508  }
509
510  function get_user_dblist($user) {
511    global $db,$err,$mem,$cuid,$L_MYSQL_DATABASE;
512    $err->log("mysql","get_user_dblist");
513
514    $r=array();
515    $dblist=$this->get_dblist();
516
517    for ( $i=0 ; $i<count($dblist) ; $i++ ) {
518      $db->query("SELECT Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv FROM mysql.db WHERE User='".$mem->user["login"].($user?"_":"").$user."' AND Host='$this->client' AND Db='".$dblist[$i]["db"]."';");
519      if ($db->next_record())
520        $r[]=array("db"=>$dblist[$i]["name"], "select"=>$db->f("Select_priv"), "insert"=>$db->f("Insert_priv"), "update"=>$db->f("Update_priv"), "delete"=>$db->f("Delete_priv"), "create"=>$db->f("Create_priv"), "drop"=>$db->f("Drop_priv"), "references"=>$db->f("References_priv"), "index"=>$db->f("Index_priv"), "alter"=>$db->f("Alter_priv"), "create_tmp"=>$db->f("Create_tmp_table_priv"), "lock"=>$db->f("Lock_tables_priv"));
521      else
522        $r[]=array("db"=>$dblist[$i]["name"], "select"=>"N", "insert"=>"N", "update"=>"N", "delete"=>"N", "create"=>"N", "drop"=>"N", "references"=>"N", "index"=>"N", "alter"=>"N", "Create_tmp"=>"N", "lock"=>"N" );
523    }
524
525    return $r;
526  }
527
528  function set_user_rights($user,$dbn,$rights) {
529    global $mem, $db;
530
531    $usern=addslashes($mem->user["login"].($user?"_":"").$user);
532    $dbname=addslashes($mem->user["login"].($dbn?"_":"").$dbn);
533    // On génère les droits en fonction du tableau de droits
534    for( $i=0 ; $i<count($rights) ; $i++ ) {
535      switch ($rights[$i]) {
536        case "select":
537          $strrights.="SELECT,";
538          break;
539        case "insert":
540          $strrights.="INSERT,";
541          break;
542        case "update":
543          $strrights.="UPDATE,";
544          break;
545        case "delete":
546          $strrights.="DELETE,";
547          break;
548        case "create":
549          $strrights.="CREATE,";
550          break;
551        case "drop":
552          $strrights.="DROP,";
553          break;
554        case "references":
555          $strrights.="REFERENCES,";
556          break;
557        case "index":
558          $strrights.="INDEX,";
559          break;
560        case "alter":
561          $strrights.="ALTER,";
562          break;
563        case "create_tmp":
564          $strrights.="CREATE TEMPORARY TABLES,";
565          break;
566        case "lock":
567          $strrights.="LOCK TABLES,";
568          break;
569      }
570    }
571
572   
573    // On remet à zéro tous les droits de l'utilisateur
574    $db->query("SELECT * FROM mysql.db WHERE User = '$usern' AND Db = '$dbname';");
575    if($db->num_rows())
576      $db->query("REVOKE ALL PRIVILEGES ON $dbname.* FROM '$usern'@'$this->client';");
577    if( $strrights ){
578      $strrights=substr($strrights,0,strlen($strrights)-1);
579      $db->query("GRANT $strrights ON $dbname.* TO '$usern'@'$this->client';");     
580    }
581    $db->query("FLUSH PRIVILEGES");
582    return TRUE;
583  }
584
585} /* Class m_mysql */
586
587?>
Note: See TracBrowser for help on using the repository browser.