Alternc  3.2
Alternc logiel libre pour l'hébergement
 All Data Structures Namespaces Files Functions Variables Pages
m_mysql.php
Go to the documentation of this file.
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  */
36 
37 class DB_users extends DB_Sql {
39 
40  /**
41  * Creator
42  */
43  function DB_users($empty=false) { // Sometimes we need to create this object with empty parameters, but by default we fill them with those of the current user's DB
44  global $cuid, $db, $err;
45 
46  if (!$empty){
47  $db->query("select db_servers.* from db_servers, membres where membres.uid=$cuid and membres.db_server_id=db_servers.id;");
48  if (!$db->next_record()) {
49  $err->raise('db_user', _("There are no databases in db_servers for this user. Please contact your administrator."));
50  die();
51  }
52 
53  # Create the object
54  $this->HumanHostname = $db->f('name');
55  $this->Host = $db->f('host');
56  $this->User = $db->f('login');
57  $this->Password = $db->f('password');
58  $this->Client = $db->f('client');
59 
60  $this->Database = "mysql"; # We have to define a dabatase when we connect, and the database must exist.
61  }else{
62  # Create the object without any parameter
63  $this->HumanHostname = "";
64  $this->Host = "";
65  $this->User = "";
66  $this->Password = "";
67  $this->Client = "";
68 
69  $this->Database = "mysql"; # We have to define a dabatase when we connect, and the database must exist.
70  }
71  }
72 
73 }
74 
75 
76 class m_mysql {
77  var $dbus;
78 
79  /*---------------------------------------------------------------------------*/
80  /** Constructor
81  * m_mysql([$mid]) Constructeur de la classe m_mysql, initialise le membre concerne
82  */
83  function m_mysql() {
84  global $cuid;
85  if (!empty($cuid)) {
86  $this->dbus = new DB_users();
87  }
88  variable_get('sql_allow_users_backups', 1,'Set 1 to allow users to configure backup of their databases, 0 if you want do disable this feature. Warning: it will not stop configured backup made by sqlbackup.sh');
89  }
90 
91  function reload_dbus() {
92  $this->dbus = new DB_users();
93  }
94 
95  function list_db_servers() {
96  global $db;
97  $db->query("select d.*, IFNULL(count(m.uid),0) as nb_users from db_servers d left join membres m on d.id = m.db_server_id group by d.id,m.db_server_id order by d.name,d.id;");
98  $c=array();
99  while ($db->next_record()) {
100  $c[]=$db->Record;
101  }
102  return $c;
103  }
104 
105  function hook_menu() {
106  global $quota;
107  $q = $quota->getquota("mysql");
108 
109  $obj = array(
110  'title' => _("MySQL"),
111  'ico' => 'images/mysql.png',
112  'link' => 'toggle',
113  'pos' => 100,
114  'links' => array(),
115  ) ;
116 
117  $obj['links'][] =
118  array (
119  'txt' => _("Databases"),
120  'url' => "sql_list.php",
121  );
122  $obj['links'][] =
123  array (
124  'txt' => _("MySQL Users"),
125  'url' => "sql_users_list.php",
126  );
127  if ($q["u"] > 0 ) {
128  $obj['links'][] =
129  array (
130  'txt' => _("PhpMyAdmin"),
131  'url' => "sql_pma_sso.php",
132  'target' => '_blank',
133  );
134  }
135  return $obj;
136  }
137 
138 
139 
140  /* ----------------------------------------------------------------- */
141  /**
142  * Password kind used in this class (hook for admin class)
143  */
145  return array("mysql"=>"MySQL users");
146  }
147 
148  /*---------------------------------------------------------------------------*/
149  /** Get the list of the database for the current user.
150  * @return array returns an associative array as follow : <br>
151  * "db" => database name "bck" => backup mode for this db
152  * "dir" => Backup folder.
153  * Returns an array (empty) if no databases
154  */
155  function get_dblist() {
156  global $db,$err,$bro,$cuid;
157  $err->log("mysql","get_dblist");
158  $db->free();
159  $db->query("SELECT login,pass,db, bck_mode, bck_dir FROM db WHERE uid='$cuid' ORDER BY db;");
160  $c=array();
161  while ($db->next_record()) {
162  list($dbu,$dbn)=split_mysql_database_name($db->f("db"));
163  $c[]=array("db"=>$db->f("db"), "name"=>$db->f('db'),"bck"=>$db->f("bck_mode"), "dir"=>$db->f("bck_dir"), "login"=>$db->f("login"), "pass"=>$db->f("pass"));
164  }
165  return $c;
166  }
167 
168  /*---------------------------------------------------------------------------*/
169  /** Get the login and password of the special user able to connect to phpmyadmin
170  * @return array returns an associative array with login and password
171  * Returns FALSE if error
172  */
174  global $db,$cuid,$err;
175  $err->log("mysql","php_myadmin_connect");
176  $db->query("SELECT dbu.name,dbu.password, dbs.host FROM dbusers dbu, db_servers dbs, membres m WHERE dbu.uid='$cuid' and enable='ADMIN' and dbs.id=m.db_server_id and m.uid='$cuid';");
177  if (!$db->num_rows()) {
178  $err->raise("mysql",_("Cannot connect to PhpMyAdmin"));
179  return false;
180  }
181  $db->next_record();
182  $info=array(
183  "login"=>$db->f("name"),
184  "pass"=>$db->f("password"),
185  "host"=>$db->f("host")
186  );
187  return $info;
188  }
189 
190 
191  /*---------------------------------------------------------------------------*/
192  /** Returns the details of a user's database.
193  * $dbn is the name of the database (after the _) or nothing for the database "$user"
194  * @return array returns an associative array as follow :
195  * "db" => Name of the database
196  * "bck" => Current bckup mode
197  * "dir" => Backup directory
198  * "size" => Size of the database (in bytes)
199  * "pass" => Password of the user
200  * "history" => Number of backup we keep
201  * "gzip" => Does we compress the dumps ?
202  * Returns FALSE if the user has no database of if the database does not exist.
203  */
204  function get_mysql_details($dbn) {
205  global $db,$err,$bro,$mem,$cuid;
206  $root=getuserpath();
207  $err->log("mysql","get_mysql_details");
208  $pos=strpos($dbn,'_');
209  if($pos === false){
210  $dbname=$dbn;
211  }else{
212  $dbncomp=explode('_',$dbn);
213  $dbname=$dbn;
214  $dbn=$dbncomp[1];
215  }
216  $size=$this->get_db_size($dbname);
217  $db->query("SELECT login,pass,db, bck_mode, bck_gzip, bck_dir, bck_history FROM db WHERE uid='$cuid' AND db='$dbname';");
218  if (!$db->num_rows()) {
219  $err->raise("mysql",_("Database %s not found"),$dbn);
220  return array("enabled"=>false);
221  }
222  $c=array();
223  $db->next_record();
224  list($dbu,$dbn)=split_mysql_database_name($db->f("db"));
225  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"));
226  }
227 
228  function test_get_param($dbname){
229  global $db,$err,$cuid;
230  $db->query("SELECT ");
231 
232 
233  }
234 
235  /*---------------------------------------------------------------------------*/
236  /** Create a new database for the current user.
237  * @param $dbn string Database name ($user_$dbn is the mysql db name)
238  * @return TRUE if the database $user_$db has been successfully created, or FALSE if
239  * an error occured, such as over quota user.
240  */
241  function add_db($dbn) {
242  global $db,$err,$quota,$mem,$cuid,$admin;
243  $err->log("mysql","add_db",$dbn);
244  $password_user="";
245  if (!$quota->cancreate("mysql")) {
246  $err->raise("mysql",_("Your databases quota is over. You cannot create more databases"));
247  return false;
248  }
249  $pos=strpos($dbn,'_');
250  if($pos === false){
251  $dbname=$dbn;
252  }else{
253  $dbncomp=explode('_',$dbn);
254  $dbname=$dbn;
255  $dbn=$dbncomp[1];
256  if (empty($dbn)) { // If nothing after the '_'
257  $err->raise("mysql",_("Database can't have empty suffix"));
258  return false;
259  }
260  }
261  if (!preg_match("#^[0-9a-z]*$#",$dbn)) {
262  $err->raise("mysql",_("Database name can contain only letters and numbers"));
263  return false;
264  }
265 
266 
267  if (strlen($dbname) > 64) {
268  $err->raise("mysql",_("Database name cannot exceed 64 characters"));
269  return false;
270  }
271  $db->query("SELECT * FROM db WHERE db='$dbname';");
272  if ($db->num_rows()) {
273  $err->raise("mysql",_("Database %s already exists"),$dbn);
274  return false;
275  }
276 
277  $db->query("SELECT name from dbusers where name='".$dbname."' and enable='ACTIVATED' ;");
278  if(!$db->num_rows()){
279  $password_user=create_pass(8);
280  if(!$this->add_user($dbn,$password_user,$password_user)){
281  }
282  }
283 
284  //checking for the phpmyadmin user
285  $db->query("SELECT * FROM dbusers WHERE uid=$cuid AND enable='ADMIN';");
286  if ($db->num_rows()) {
287  $db->next_record();
288  $myadm=$db->f("name");
289  $password=$db->f("password");
290  }else{
291  $err->raise("mysql",_("There is a problem with the special PhpMyAdmin user. Contact the administrator"));
292  return false;
293  }
294 
295  //Grant the special user every rights.
296  if ($this->dbus->query("CREATE DATABASE `$dbname`;")) {
297  $err->log("mysql","add_db_succes",$dbn);
298  // Ok, database does not exist, quota is ok and dbname is compliant. Let's proceed
299  $db->query("INSERT INTO db (uid,login,pass,db,bck_mode) VALUES ('$cuid','$myadm','$password','$dbname',0);");
300  $dbuser=$dbname;
301  $dbname=str_replace('_','\_',$dbname);
302  $this->grant($dbname,$myadm,"ALL PRIVILEGES",$password);
303  if(!empty($password_user)){
304  $this->grant($dbname,$dbuser,"ALL PRIVILEGES",$password_user);
305  }
306  $this->dbus->query("FLUSH PRIVILEGES;");
307  return true;
308  } else {
309  $err->log("mysql","add_db",$dbn);
310  $err->raise("mysql",_("An error occured. The database could not be created"));
311  return false;
312  }
313  }
314 
315 
316  /*---------------------------------------------------------------------------*/
317  /** Delete a database for the current user.
318  * @param $dbn string Name of the database to delete. The db name is $user_$dbn
319  * @return TRUE if the database $user_$db has been successfully deleted, or FALSE if
320  * an error occured, such as db does not exist.
321  */
322  function del_db($dbn) {
323  global $db,$err,$mem,$cuid;
324  $err->log("mysql","del_db",$dbn);
325  $dbname=addslashes($dbn);
326  $db->query("SELECT uid FROM db WHERE db='$dbname';");
327  if (!$db->num_rows()) {
328  $err->raise("mysql",_("The database was not found. I can't delete it"));
329  return false;
330  }
331  $db->next_record();
332 
333  // Ok, database exists and dbname is compliant. Let's proceed
334  $db->query("DELETE FROM size_db WHERE db='$dbname';");
335  $db->query("DELETE FROM db WHERE uid='$cuid' AND db='$dbname';");
336  $this->dbus->query("DROP DATABASE `$dbname`;");
337 
338  $db_esc=str_replace('_','\_',$dbname);
339  $this->dbus->query("DELETE FROM mysql.db WHERE Db='$db_esc';");
340 
341  #We test if the user created with the database is associated with more than 1 database.
342  $this->dbus->query("select User from mysql.db where User='".$dbname."' and (Select_priv='Y' or Insert_priv='Y' or Update_priv='Y' or Delete_priv='Y' or Create_priv='Y' or Drop_priv='Y' or References_priv='Y' or Index_priv='Y' or Alter_priv='Y' or Create_tmp_table_priv='Y' or Lock_tables_priv='Y');");
343  if(($this->dbus->num_rows()) == 0){
344  #If not we can delete it.
345  $this->del_user($dbname);
346  }
347  return true;
348  }
349 
350 
351 
352 
353  /*---------------------------------------------------------------------------*/
354  /** Set the backup parameters for the database $db
355  * @param $db string database name
356  * @param $bck_mode integer Backup mode (0 = none 1 = daily 2 = weekly)
357  * @param $bck_history integer How many backup should we keep ?
358  * @param $bck_gzip boolean shall we compress the backup ?
359  * @param $bck_dir string Directory relative to the user account where the backup will be stored
360  * @return boolean true if the backup parameters has been successfully changed, false if not.
361  */
362  function put_mysql_backup($dbn,$bck_mode,$bck_history,$bck_gzip,$bck_dir) {
363  global $db,$err,$mem,$bro,$cuid;
364  $err->log("mysql","put_mysql_backup");
365 
366  if ( ! variable_get('sql_allow_users_backups') ) {
367  $err->raise("mysql",_("User aren't allowed to configure their backups"));
368  return false;
369  }
370 
371  $pos=strpos($dbn,'_');
372  if($pos === false){
373  $dbname=$dbn;
374  }else{
375  $dbncomp=explode('_',$dbn);
376  $dbname=$dbn;
377  $dbn=$dbncomp[1];
378  }
379  if (!preg_match("#^[0-9a-z]*$#",$dbn)) {
380  $err->raise("mysql",_("Database name can contain only letters and numbers"));
381  return false;
382  }
383  $db->query("SELECT * FROM db WHERE uid='$cuid' AND db='$dbname';");
384  if (!$db->num_rows()) {
385  $err->raise("mysql",_("Database %s not found"),$dbn);
386  return false;
387  }
388  $db->next_record();
389  $bck_mode=intval($bck_mode);
390  $bck_history=intval($bck_history);
391  if ($bck_gzip)
392  $bck_gzip="1";
393  else
394  $bck_gzip="0";
395  if (!$bck_mode)
396  $bck_mode="0";
397  if (!$bck_history) {
398  $err->raise("mysql",_("You have to choose how many backups you want to keep"));
399  return false;
400  }
401  if (($bck_dir=$bro->convertabsolute($bck_dir,0))===false) { // return a full path or FALSE
402  $err->raise("mysql",_("Directory does not exist"));
403  return false;
404  }
405  $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';");
406  return true;
407  }
408 
409 
410  /*---------------------------------------------------------------------------*/
411  /** Change the password of the user in MySQL
412  * @param $password string new password (cleartext)
413  * @return boolean TRUE if the password has been successfully changed, FALSE else.
414  */
416  global $db,$err,$mem,$cuid,$admin;
417  $err->log("mysql","put_mysql_details");
418  $db->query("SELECT * FROM db WHERE uid='$cuid';");
419  if (!$db->num_rows()) {
420  $err->raise("mysql",_("Database not found"));
421  return false;
422  }
423  $db->next_record();
424  $login=$db->f("login");
425 
426  if (!$password) {
427  $err->raise("mysql",_("The password is mandatory"));
428  return false;
429  }
430 
431  if (strlen($password)>16) {
432  $err->raise("mysql",_("MySQL password cannot exceed 16 characters"));
433  return false;
434  }
435 
436  // Check this password against the password policy using common API :
437  if (is_callable(array($admin,"checkPolicy"))) {
438  if (!$admin->checkPolicy("mysql",$login,$password)) {
439  return false; // The error has been raised by checkPolicy()
440  }
441  }
442 
443  // Update all the "pass" fields for this user :
444  $db->query("UPDATE db SET pass='$password' WHERE uid='$cuid';");
445  $this->dbus->query("SET PASSWORD FOR ".$login."@".$this->dbus->Client." = PASSWORD('$password');");
446  return true;
447  }
448 
449  /**
450  * Function used to grant SQL rights to users:
451  * @base :database
452  * @user : database user
453  * @rights : rights to apply ( optional, every rights apply given if missing
454  * @pass : user password ( optional, if not given the pass stays the same, else it takes the new value )
455  * @table : sql tables to apply rights
456  **/
457  function grant($base,$user,$rights=null,$pass=null,$table='*'){
458  global $err,$db;
459  $err->log("mysql","grant",$base."-".$rights."-".$user);
460 
461  if(!preg_match("#^[0-9a-z_\\*\\\\]*$#",$base)){
462  $err->raise("mysql",_("Database name can contain only letters and numbers"));
463  return false;
464  } elseif (!$this->dbus->query("select db from db where db='$base';")){
465  $err->raise("mysql",_("Database not found"));
466  return false;
467  }
468 
469  if($rights==null){
470  $rights='ALL PRIVILEGES';
471  }elseif(!preg_match("#^[a-zA-Z,\s]*$#",$rights)){
472  $err->raise("mysql",_("Databases rights are not correct"));
473  return false;
474  }
475 
476  if (!preg_match("#^[0-9a-z]#",$user)) {
477  $err->raise("mysql",_("The username can contain only letters and numbers."));
478  return false;
479  }
480  $db->query("select name from dbusers where name='".$user."' ;");
481 
482  if(!$db->num_rows()){
483  $err->raise("mysql",_("Database user not found"));
484  return false;
485  }
486 
487  # Protect database name if not wildcard
488  if ($base != '*' ) $base = "`".$base."`" ;
489 
490  $grant="grant ".$rights." on ".$base.".".$table." to '".$user."'@'".$this->dbus->Client."'" ;
491 
492  if($pass){
493  $grant .= " identified by '".$pass."';";
494  }else{
495  $grant .= ";";
496  }
497  if(!$this->dbus->query($grant)){
498  $err->raise("mysql",_("Could not grant rights"));
499  return false;
500  }
501  return true;
502 
503  }
504 
505 
506  /* ----------------------------------------------------------------- */
507  /** Restore a sql database.
508  * @param $file string The filename, relative to the user root dir, which contains a sql dump
509  * @param $stdout boolean shall-we dump the error to stdout ?
510  * @param $id integer The ID of the database to dump to.
511  * @return boolean TRUE if the database has been restored, or FALSE if an error occurred
512  */
513  function restore($file,$stdout,$id) {
514  global $err,$bro,$mem,$L_MYSQL_HOST,$db;
515  if (empty($file)) {
516  $err->raise("mysql",_("No file specified"));
517  return false;
518  }
519  if (!$r=$this->get_mysql_details($id)) {
520  return false;
521  }
522  if (!($fi=$bro->convertabsolute($file,0))) {
523  $err->raise("mysql",_("File not found"));
524  return false;
525  }
526  if (!file_exists($fi)) {
527  $err->raise("mysql",_("File not found"));
528  return false;
529  }
530 
531  if (substr($fi,-3)==".gz") {
532  $exe="/bin/gzip -d -c <".escapeshellarg($fi)." | /usr/bin/mysql -h".escapeshellarg($this->dbus->Host)." -u".escapeshellarg($r["login"])." -p".escapeshellarg($r["pass"])." ".escapeshellarg($r["db"]);
533  } elseif (substr($fi,-4)==".bz2") {
534  $exe="/usr/bin/bunzip2 -d -c <".escapeshellarg($fi)." | /usr/bin/mysql -h".escapeshellarg($this->dbus->Host)." -u".escapeshellarg($r["login"])." -p".escapeshellarg($r["pass"])." ".escapeshellarg($r["db"]);
535  } else {
536  $exe="/usr/bin/mysql -h".escapeshellarg($this->dbus->Host)." -u".escapeshellarg($r["login"])." -p".escapeshellarg($r["pass"])." ".escapeshellarg($r["db"])." <".escapeshellarg($fi);
537  }
538  $exe .= " 2>&1";
539 
540  echo "<code><pre>" ;
541  if ($stdout) {
542  passthru($exe,$ret);
543  } else {
544  exec ($exe,$ret);
545  }
546  echo "</pre></code>" ;
547  if ($ret != 0) {
548  return false ;
549  } else {
550  return true ;
551  }
552  }
553 
554 
555  /* ----------------------------------------------------------------- */
556  /** Get the size of a database
557  * @param $dbname name of the database
558  * @return integer database size
559  * @access private
560  */
561  function get_db_size($dbname) {
562  global $db,$err;
563 
564  $this->dbus->query("SHOW TABLE STATUS FROM `$dbname`;");
565  $size = 0;
566  while ($db->next_record()) {
567  $size += $db->f('Data_length') + $db->f('Index_length');
568  if ( $db->f('Engine') != 'InnoDB') $size += $db->f('Data_free');
569  }
570  return $size;
571  }
572 
573 
574  /* ------------------------------------------------------------ */
575  /**
576  * Returns the list of database users of an account
577  **/
578  function get_userslist($all=null) {
579  global $db,$err,$bro,$cuid;
580  $err->log("mysql","get_userslist");
581  $c=array();
582  if(!$all){
583  $db->query("SELECT name FROM dbusers WHERE uid='$cuid' and enable not in ('ADMIN','HIDDEN') ORDER BY name;");
584  }else{
585  $db->query("SELECT name FROM dbusers WHERE uid='$cuid' ORDER BY name;");
586  }
587  while ($db->next_record()) {
588  $pos=strpos($db->f("name"),"_");
589  if($pos === false){
590  $c[]=array("name"=>($db->f("name")));
591  }else{
592  $c[]=array("name"=>($db->f("name")));
593  //$c[]=array("name"=>substr($db->f("name"),strpos($db->f("name"),"_")+1));
594  }
595  }
596 
597  return $c;
598  }
599 
600  function get_defaultsparam($dbn){
601  global $db,$err,$bro,$cuid;
602  $err->log("mysql","getdefaults");
603 
604  $dbu=$dbn;
605  $r=array();
606  $dbn=str_replace('_','\_',$dbn);
607  $q=$this->dbus->query("Select * from mysql.db where Db='".$dbn."' and User!='".$cuid."_myadm';");
608 
609  if(!$db->num_rows()){
610  return $r;
611  }
612  while ($db->next_record()) {
613  $variable = $db->Record;
614  if($variable['User'] == $dbu){
615  $r['Host']=$db->f('Host');
616 
617  if($db->f('Select_priv') !== "Y"){
618  return $r;
619  }
620  if($db->f('Insert_priv') !== "Y"){
621  return $r;
622  }
623  if($db->f('Update_priv') !== "Y"){
624  return $r;
625  }
626  if($db->f('Delete_priv') !== "Y"){
627  return $r;
628  }
629  if($db->f('Create_priv') !== "Y"){
630  return $r;
631  }
632  if($db->f('Drop_priv') !== "Y"){
633  return $r;
634  }
635  if($db->f('References_priv') !== "Y"){
636  return $r;
637  }
638  if($db->f('Index_priv') !== "Y"){
639  return $r;
640  }
641  if($db->f('Alter_priv') !== "Y"){
642  return $r;
643  }
644  if($db->f('Create_tmp_table_priv') !== "Y"){
645  return $r;
646  }
647  if($db->f('Lock_tables_priv') !== "Y"){
648  return $r;
649  }
650  if($db->f('Create_view_priv') !== "Y"){
651  return $r;
652  }
653  if($db->f('Show_view_priv') !== "Y"){
654  return $r;
655  }
656  if($db->f('Create_routine_priv') !== "Y"){
657  return $r;
658  }
659  if($db->f('Alter_routine_priv') !== "Y"){
660  return $r;
661  }
662  if($db->f('Execute_priv') !== "Y"){
663  return $r;
664  }
665  if($db->f('Event_priv') !== "Y"){
666  return $r;
667  }
668  if($db->f('Trigger_priv') !== "Y"){
669  return $r;
670  }
671  }
672  }//endwhile
673  if(!$db->query("SELECT name,password from dbusers where name='".$dbu."';")){
674  return $r;
675  }
676 
677  if(!$db->num_rows()){
678  return $r;
679  }
680  $db->next_record();
681  $r['user']=$db->f('name');
682  $r['password']=$db->f('password');
683  return $r;
684 
685  }
686 
687 
688  /* ------------------------------------------------------------ */
689  /**
690  * Create a new user in MySQL rights tables
691  * @param $usern the username (we will add _[alternc-account] to it)
692  * @param $password The password for this username
693  * @param $passconf The password confirmation
694  * @return TRUE if the user has been created in MySQL or FALSE if an error occurred
695  **/
696  function add_user($usern,$password,$passconf) {
697  global $db,$err,$quota,$mem,$cuid,$admin;
698  $err->log("mysql","add_user",$usern);
699 
700  $usern=trim($usern);
701  $login=$mem->user["login"];
702  if($login != $usern){
703  $user=addslashes($login."_".$usern);
704  }else{
705  $user=$usern;
706  }
707  $pass=addslashes($password);
708 
709  if (!$usern) {
710  $err->raise("mysql",_("The username is mandatory"));
711  return false;
712  }
713  if (!$pass) {
714  $err->raise("mysql",_("The password is mandatory"));
715  return false;
716  }
717  if (!preg_match("#^[0-9a-z]#",$usern)) {
718  $err->raise("mysql",_("The username can contain only letters and numbers"));
719  return false;
720  }
721 
722 
723  // We check the length of the COMPLETE username, not only the part after _
724  if (strlen($user) > 16) {
725  $err->raise("mysql",_("MySQL username cannot exceed 16 characters"));
726  return false;
727  }
728  $db->query("SELECT * FROM dbusers WHERE name='$user';");
729  if ($db->num_rows()) {
730  $err->raise("mysql",_("The database user already exists"));
731  return false;
732  }
733  if ($password != $passconf || !$password) {
734  $err->raise("mysql",_("The passwords do not match"));
735  return false;
736  }
737 
738  // Check this password against the password policy using common API :
739  if (is_callable(array($admin,"checkPolicy"))) {
740  if (!$admin->checkPolicy("mysql",$user,$password)) {
741  return false; // The error has been raised by checkPolicy()
742  }
743  }
744 
745  // We add him to the user table
746  $db->query("INSERT INTO dbusers (uid,name,password,enable) VALUES($cuid,'$user','$password','ACTIVATED');");
747 
748  $this->grant("*",$user,"USAGE",$pass);
749  return true;
750  }
751 
752  /* ------------------------------------------------------------ */
753  /**
754  * Change a user's MySQL password
755  * @param $usern the username
756  * @param $password The password for this username
757  * @param $passconf The password confirmation
758  * @return TRUE if the password has been changed in MySQL or FALSE if an error occurred
759  **/
760  function change_user_password($usern,$password,$passconf) {
761  global $db,$err,$quota,$mem,$cuid,$admin;
762  $err->log("mysql","change_user_pass",$usern);
763 
764  $usern=trim($usern);
765  $user=addslashes($usern);
766  $pass=addslashes($password);
767  if ($password != $passconf || !$password) {
768  $err->raise("mysql",_("The passwords do not match"));
769  return false;
770  }
771 
772  // Check this password against the password policy using common API :
773  if (is_callable(array($admin,"checkPolicy"))) {
774  if (!$admin->checkPolicy("mysql",$user,$password)) {
775  return false; // The error has been raised by checkPolicy()
776  }
777  }
778  $this->dbus->query("SET PASSWORD FOR '".$user."'@'".$this->dbus->Client."' = PASSWORD('".$pass."');");
779  $db->query("UPDATE dbusers set password='".$pass."' where name='".$usern."' and uid=$cuid ;");
780  return true;
781  }
782 
783 
784 
785  /* ------------------------------------------------------------ */
786  /**
787  * Delete a user in MySQL rights tables
788  * @param $user the username (we will add "[alternc-account]_" to it) to delete
789  * @return TRUE if the user has been deleted in MySQL or FALSE if an error occurred
790  **/
791  function del_user($user,$all=null) {
792  global $db,$err,$mem,$cuid;
793  $err->log("mysql","del_user",$user);
794  if (!preg_match("#^[0-9a-z]#",$user)) {
795  $err->raise("mysql",_("The username can contain only letters and numbers"));
796  return false;
797  }
798  if(!$all){
799  $db->query("SELECT name FROM dbusers WHERE name='".$user."' and enable not in ('ADMIN','HIDDEN');");
800  }else{
801  $db->query("SELECT name FROM dbusers WHERE uid='".$cuid."' ;");
802  }
803 
804  if (!$db->num_rows()) {
805  $err->raise("mysql",_("The username was not found"));
806  return false;
807  }
808  $db->next_record();
809  $login=$db->f("name");
810 
811  // Ok, database exists and dbname is compliant. Let's proceed
812  $this->dbus->query("REVOKE ALL PRIVILEGES ON *.* FROM '".$user."'@'".$this->dbus->Client."';");
813  $this->dbus->query("DELETE FROM mysql.db WHERE User='".$user."' AND Host='".$this->dbus->Client."';");
814  $this->dbus->query("DELETE FROM mysql.user WHERE User='".$user."' AND Host='".$this->dbus->Client."';");
815  $this->dbus->query("FLUSH PRIVILEGES");
816 
817  $db->query("DELETE FROM dbusers WHERE uid='$cuid' AND name='".$user."';");
818  return true;
819  }
820 
821 
822  /* ------------------------------------------------------------ */
823  /**
824  * Return the list of the database rights of user $user
825  * @param $user the username
826  * @return array An array of database name and rights
827  **/
828 
830  global $db,$err,$mem,$cuid;
831 
832  $this->dbus->query("SELECT * FROM mysql.user WHERE User='".$user."' AND Host='".$this->dbus->Client."';");
833  if (!$this->dbus->next_record() ) {
834  $err->raise('mysql',_("This user does not exist in the MySQL/User database"));
835  return false;
836  }
837 
838  $r=array();
839  $db->free();
840  $dblist=$this->get_dblist();
841  foreach($dblist as $tab){
842  $pos=strpos($tab['db'],"_");
843  if($pos === false){
844  $this->dbus->query("SELECT * FROM mysql.db WHERE User='".$user."' AND Host='".$this->dbus->Client."' AND Db='".$tab["db"]."';");
845  }else{
846  $dbname=str_replace('_','\_',$tab['db']);
847  $this->dbus->query("SELECT * FROM mysql.db WHERE User='".$user."' AND Host='".$this->dbus->Client."' AND Db='".$dbname."';");
848  }
849  if ($this->dbus->next_record()){
850  $r[]=array("db"=>$tab["db"], "select"=>$this->dbus->f("Select_priv"), "insert"=>$this->dbus->f("Insert_priv"), "update"=>$this->dbus->f("Update_priv"), "delete"=>$this->dbus->f("Delete_priv"), "create"=>$this->dbus->f("Create_priv"), "drop"=>$this->dbus->f("Drop_priv"), "references"=>$this->dbus->f("References_priv"), "index"=>$this->dbus->f("Index_priv"), "alter"=>$this->dbus->f("Alter_priv"), "create_tmp"=>$this->dbus->f("Create_tmp_table_priv"), "lock"=>$this->dbus->f("Lock_tables_priv"),
851  "create_view"=>$this->dbus->f("Create_view_priv"),
852  "show_view"=>$this->dbus->f("Show_view_priv"),
853  "create_routine"=>$this->dbus->f("Create_routine_priv"),
854  "alter_routine"=>$this->dbus->f("Alter_routine_priv"),
855  "execute"=>$this->dbus->f("Execute_priv"),
856  "event"=>$this->dbus->f("Event_priv"),
857  "trigger"=>$this->dbus->f("Trigger_priv")
858  );
859  }else{
860  $r[]=array("db"=>$tab['db'], "select"=>"N", "insert"=>"N", "update"=>"N", "delete"=>"N", "create"=>"N", "drop"=>"N", "references"=>"N", "index"=>"N", "alter"=>"N", "create_tmp"=>"N", "lock"=>"N","create_view"=>"N","show_view"=>"N","create_routine"=>"N","alter_routine"=>"N","execute"=>"N","event"=>"N","trigger"=>"N");
861 
862  }
863 
864  }
865  return $r;
866  }
867 
868  /* ------------------------------------------------------------ */
869  /**
870  * Set the access rights of user $user to database $dbn to be rights $rights
871  * @param $user the username to give rights to
872  * @param $dbn The database to give rights to
873  * @param $rights The rights as an array of MySQL keywords (insert, select ...)
874  * @return boolean TRUE if the rights has been applied or FALSE if an error occurred
875  *
876  **/
877  function set_user_rights($user,$dbn,$rights) {
878  global $mem,$err,$db;
879  $err->log("mysql","set_user_rights");
880 
881  $usern=addslashes($user);
882  $dbname=addslashes($dbn);
883  $dbname=str_replace('_','\_',$dbname);
884  // On génère les droits en fonction du tableau de droits
885  $strrights="";
886  for( $i=0 ; $i<count($rights) ; $i++ ) {
887  switch ($rights[$i]) {
888  case "select":
889  $strrights.="SELECT,";
890  break;
891  case "insert":
892  $strrights.="INSERT,";
893  break;
894  case "update":
895  $strrights.="UPDATE,";
896  break;
897  case "delete":
898  $strrights.="DELETE,";
899  break;
900  case "create":
901  $strrights.="CREATE,";
902  break;
903  case "drop":
904  $strrights.="DROP,";
905  break;
906  case "references":
907  $strrights.="REFERENCES,";
908  break;
909  case "index":
910  $strrights.="INDEX,";
911  break;
912  case "alter":
913  $strrights.="ALTER,";
914  break;
915  case "create_tmp":
916  $strrights.="CREATE TEMPORARY TABLES,";
917  break;
918  case "lock":
919  $strrights.="LOCK TABLES,";
920  break;
921  case "create_view":
922  $strrights.="CREATE VIEW,";
923  break;
924  case "show_view":
925  $strrights.="SHOW VIEW,";
926  break;
927  case "create_routine":
928  $strrights.="CREATE ROUTINE,";
929  break;
930  case "alter_routine":
931  $strrights.="ALTER ROUTINE,";
932  break;
933  case "execute":
934  $strrights.="EXECUTE,";
935  break;
936  case "event":
937  $strrights.="EVENT,";
938  break;
939  case "trigger":
940  $strrights.="TRIGGER,";
941  break;
942  }
943  }
944 
945  // We reset all user rights on this DB :
946  $this->dbus->query("SELECT * FROM mysql.db WHERE User = '$usern' AND Db = '$dbname';");
947  if($this->dbus->num_rows())
948  $this->dbus->query("REVOKE ALL PRIVILEGES ON `$dbname`.* FROM '$usern'@'".$this->dbus->Client."';");
949  if( $strrights ){
950  $strrights=substr($strrights,0,strlen($strrights)-1);
951  $this->grant($dbname,$usern,$strrights);
952  }
953  $this->dbus->query("FLUSH PRIVILEGES");
954  return TRUE;
955  }
956 
958  return Array('select','insert','update','delete','create','drop','references','index','alter','create_tmp','lock','create_view','show_view','create_routine','alter_routine','execute','event','trigger');
959 
960 
961  }
962 
963 
964  /* ----------------------------------------------------------------- */
965  /** Hook function called by the lxc class to set mysql_host and port
966  * parameters
967  * @param $name string name of the quota
968  * @return integer the number of service used or false if an error occured
969  * @access private
970  */
971  function hook_lxc_params($params) {
972  global $err;
973  $err->log("mysql","alternc_get_quota");
974  $p=array();
975  if (isset($this->dbus["Host"]) && $this->dbus["Host"]!="") {
976  $p["mysql_host"] = $this->dbus["Host"];
977  $p["mysql_port"] = 3306;
978  }
979  return $p;
980  }
981 
982 
983  /* ----------------------------------------------------------------- */
984  /** Hook function called by the quota class to compute user used quota
985  * Returns the used quota for the $name service for the current user.
986  * @param $name string name of the quota
987  * @return integer the number of service used or false if an error occured
988  * @access private
989  */
990  function hook_quota_get() {
991  global $err,$db,$cuid;
992  $err->log("mysql","alternc_get_quota");
993  $q=Array("name"=>"mysql", "description"=>_("MySQL Databases"), "used"=>0);
994  $c=$this->get_dblist();
995  if (is_array($c)) {
996  $q['used']=count($c);
997  }
998  return $q;
999  }
1000 
1001  /* ----------------------------------------------------------------- */
1002  /** Hook function called when a user is created.
1003  * AlternC's standard function that create a member
1004  * @access private
1005  */
1006  function alternc_add_member() {
1007  global $db,$err,$cuid,$mem;
1008  $err->log("mysql","alternc_add_member");
1009  //checking for the phpmyadmin user
1010  $db->query("SELECT name,password FROM dbusers WHERE uid=$cuid AND Type='ADMIN';");
1011  if ($db->num_rows()) {
1012  $myadm=$db->f("name");
1013  $password=$db->f("password");
1014  }else{
1015  $myadm=$cuid."_myadm";
1016  $password=create_pass(8);
1017  }
1018 
1019 
1020  $db->query("INSERT INTO dbusers (uid,name,password,enable) VALUES ('$cuid','$myadm','$password','ADMIN');");
1021 
1022  return true;
1023  }
1024 
1025 
1026 
1027 
1028  /* ----------------------------------------------------------------- */
1029  /** Hook function called when a user is deleted.
1030  * AlternC's standard function that delete a member
1031  * @access private
1032  */
1033  function alternc_del_member() {
1034  global $db,$err,$cuid;
1035  $err->log("mysql","alternc_del_member");
1036  $c=$this->get_dblist();
1037  if (is_array($c)) {
1038  for($i=0;$i<count($c);$i++) {
1039  $this->del_db($c[$i]["name"]);
1040  }
1041  }
1042  $d=$this->get_userslist(1);
1043  if (!empty($d)) {
1044  for($i=0;$i<count($d);$i++) {
1045  $this->del_user($d[$i]["name"],1);
1046  }
1047  }
1048  return true;
1049  }
1050 
1051 
1052  /* ----------------------------------------------------------------- */
1053  /** Hook function called when a user is logged out.
1054  * We just remove the cookie created in admin/sql_admin.php
1055  a @access private
1056  */
1057  function alternc_del_session() {
1058  $_SESSION['PMA_single_signon_user'] = '';
1059  $_SESSION['PMA_single_signon_password'] = '';
1060  $_SESSION['PMA_single_signon_host'] = '';
1061  }
1062 
1063 
1064  /* ----------------------------------------------------------------- */
1065  /**
1066  * Exporte all the mysql information of an account
1067  * @access private
1068  * EXPERIMENTAL 'sid' function ;)
1069  */
1070  function alternc_export_conf() {
1071  //TODO don't work with separated sql server for dbusers
1072  global $db,$err,$cuid;
1073  $err->log("mysql","export");
1074  $db->query("SELECT login, pass, db, bck_mode, bck_dir, bck_history, bck_gzip FROM db WHERE uid='$cuid';");
1075  if ($db->next_record()) {
1076  $str.=" <sql>\n";
1077  $str.=" <login>".$db->Record["login"]."</login>\n";
1078  $str.=" <pass>".$db->Record["pass"]."</pass>\n";
1079  do {
1080  $filename=$tmpdir."/mysql.".$db->Record["db"].".sql.gz";
1081  $str.=" <database>".($db->Record["db"])."</database>\n";
1082  $str.=" <password>".($db->Record["pass"])."</password>\n";
1083  if ($s["bck_mode"]!=0) {
1084  $str.=" <backup-mode>".($db->Record["bck_mode"])."</backup-mode>\n";
1085  $str.=" <backup-dir>".($db->Record["bck_dir"])."</backup-dir>\n";
1086  $str.=" <backup-history>".($db->Record["bck_history"])."</backup-history>\n";
1087  $str.=" <backup-gzip>".($db->Record["bck_gzip"])."</backup-gzip>\n";
1088  }
1089  } while ($db->next_record());
1090  $str.=" </sql>\n";
1091  }
1092  return $str;
1093  }
1094 
1095  /* ----------------------------------------------------------------- */
1096  /**
1097  * Exporte all the mysql databases a of give account to $dir directory
1098  * @access private
1099  * EXPERIMENTAL 'sid' function ;)
1100  */
1101  function alternc_export_data ($dir){
1102  global $db, $err, $cuid,$mem;
1103  $err->log("mysql","export_data");
1104  $db->query("SELECT db.login, db.pass, db.db, dbusers.name FROM db,dbusers WHERE db.uid='$cuid' AND dbusers.uid=db.uid;");
1105  $dir.="sql/";
1106  if(!is_dir($dir)){
1107  if(!mkdir($dir)){
1108  $err->raise('mysql',_("The directory could not be created"));
1109  }
1110  }
1111  // on exporte toutes les bases utilisateur.
1112  while($db->next_record()){
1113  $filename=$dir."mysql.".$db->Record["db"].".".date("H:i:s").".sql.gz";
1114  exec ("/usr/bin/mysqldump --defaults-file=/etc/alternc/my.cnf --add-drop-table --allow-keywords -Q -f -q -a -e ".escapeshellarg($db->Record["db"])." |/bin/gzip >".escapeshellarg($filename));
1115  }
1116  }
1117 
1118  /* ----------------------------------------------------------------- */
1119  /**
1120  * Return the size of each databases in a SQL Host given in parameter
1121  * @param $db_name the human name of the host
1122  * @param $db_host the host hosting the SQL databases
1123  * @param $db_login the login to access the SQL db
1124  * @param $db_password the password to access the SQL db
1125  * @param $db_client the client to access the SQL db
1126  * @return an array associating the name of the databases to their sizes : array(dbname=>size)
1127  */
1128  function get_dbus_size($db_name,$db_host,$db_login,$db_password,$db_client) {
1129  global $db,$err;
1130  $err->log("mysql","get_dbus_size",$db_host);
1131 
1132  # We create the object with empty parameters
1133  $this->dbus=new DB_users(true);
1134  # Modify the object with right parameters
1135  $this->dbus->HumanHostname = $db_name;
1136  $this->dbus->Host = $db_host;
1137  $this->dbus->User = $db_login;
1138  $this->dbus->Password = $db_password;
1139  $this->dbus->Client = $db_client;
1140 
1141  $this->dbus->query("show databases;");
1142  $res=array();
1143  $d=array();
1144  while($this->dbus->next_record()) {
1145  $dbname=$this->dbus->f("Database");
1146  $c=mysql_query("SHOW TABLE STATUS FROM $dbname;");
1147  $size=0;
1148  while ($d=mysql_fetch_array($c)) {
1149  $size+=$d["Data_length"]+$d["Index_length"];
1150  }
1151  $res["$dbname"]="$size";
1152  }
1153  return $res;
1154  }
1155 
1156 } /* Class m_mysql */
1157 
1158 ?>