release/src/router/mysql/scripts/mysql_setpermission.sh

   1 #!/usr/bin/perl
   2 ## Emacs, this is -*- perl -*- mode? :-)
   3
   4 # Copyright (c) 2000, 2007 MySQL AB, 2009 Sun Microsystems, Inc.
   5 # Use is subject to license terms.
   6 #
   7 # This program is free software; you can redistribute it and/or
   8 # modify it under the terms of the GNU Library General Public
   9 # License as published by the Free Software Foundation; version 2
  10 # of the License.
  11 #
  12 # This program is distributed in the hope that it will be useful,
  13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15 # Library General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU Library General Public
  18 # License along with this library; if not, write to the Free
  19 # Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
  20 # MA 02110-1301, USA
  21
  22 ##
  23 ##        Permission setter for MySQL
  24 ##
  25 ##        mady by Luuk de Boer (luuk@wxs.nl) 1998.
  26 ##        it's made under GPL ...:-))
  27 ##
  28 ##
  29 ############################################################################
  30 ## History
  31 ##
  32 ## 1.0 first start of the program
  33 ## 1.1 some changes from monty and after that
  34 ##     initial release in mysql 3.22.10 (nov 1998)
  35 ## 1.2 begin screen now in a loop + quit is using 0 instead of 9
  36 ##     after ideas of Paul DuBois.
  37 ## 1.2a Add Grant, References, Index and Alter privilege handling (Monty)
  38 ## 1.3 Applied patch provided by Martin Mokrejs <mmokrejs@natur.cuni.cz>
  39 ##     (General code cleanup, use the GRANT statement instead of updating
  40 ##     the privilege tables directly, added option to revoke privileges)
  41 ## 1.4 Remove option 6 which attempted to erroneously grant global privileges
  42
  43 #### TODO
  44 #
  45 # empty ... suggestions ... mail them to me ...
  46
  47
  48 $version="1.4";
  49
  50 use DBI;
  51 use Getopt::Long;
  52 use strict;
  53 use vars qw($dbh $sth $hostname $opt_user $opt_password $opt_help $opt_host
  54             $opt_socket $opt_port $host $version);
  55
  56 my $sqlhost = "";
  57 my $user = "";
  58
  59 $dbh=$host=$opt_user= $opt_password= $opt_help= $opt_host= $opt_socket= "";
  60 $opt_port=0;
  61
  62 read_my_cnf();          # Read options from ~/.my.cnf
  63
  64 GetOptions("user=s","password=s","help","host=s","socket=s","port=i");
  65
  66 usage() if ($opt_help); # the help function
  67
  68 if ($opt_host eq '')
  69 {
  70   $sqlhost = "localhost";
  71 }
  72 else
  73 {
  74   $sqlhost = $opt_host;
  75 }
  76
  77 # ask for a password if no password is set already
  78 if ($opt_password eq '')
  79 {
  80   system "stty -echo";
  81   print "Password for user $opt_user to connect to MySQL: ";
  82   $opt_password = <STDIN>;
  83   chomp($opt_password);
  84   system "stty echo";
  85   print "\n";
  86 }
  87
  88
  89 # make the connection to MySQL
  90 $dbh= DBI->connect("DBI:mysql:mysql:host=$sqlhost:port=$opt_port:mysql_socket=$opt_socket",$opt_user,$opt_password, {PrintError => 0}) ||
  91   die("Can't make a connection to the mysql server.\n The error: $DBI::errstr");
  92
  93 # the start of the program
  94 &q1();
  95 exit(0); # the end...
  96
  97 #####
  98 # below all subroutines of the program
  99 #####
 100
 101 ###
 102 # the beginning of the program
 103 ###
 104 sub q1 { # first question ...
 105   my ($answer,$end);
 106   while (! $end) {
 107     print "#"x70;
 108     print "\n";
 109     print "## Welcome to the permission setter $version for MySQL.\n";
 110     print "## made by Luuk de Boer\n";
 111     print "#"x70;
 112     print "\n";
 113     print "What would you like to do:\n";
 114     print "  1. Set password for an existing user.\n";
 115     print "  2. Create a database + user privilege for that database\n";
 116                 print "     and host combination (user can only do SELECT)\n";
 117     print "  3. Create/append user privilege for an existing database\n";
 118                 print "     and host combination (user can only do SELECT)\n";
 119     print "  4. Create/append broader user privileges for an existing\n";
 120                 print "     database and host combination\n";
 121                 print "     (user can do SELECT,INSERT,UPDATE,DELETE)\n";
 122     print "  5. Create/append quite extended user privileges for an\n";
 123                 print "     existing database and host combination (user can do\n";
 124                 print "     SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,\n";
 125                 print "     LOCK TABLES,CREATE TEMPORARY TABLES)\n";
 126     print "  6. Create/append full privileges for an existing database\n";
 127                 print "     and host combination (user has FULL privilege)\n";
 128     print "  7. Remove all privileges for for an existing database and\n";
 129                 print "     host combination.\n";
 130     print "     (user will have all permission fields set to N)\n";
 131     print "  0. exit this program\n";
 132     print "\nMake your choice [1,2,3,4,5,6,7,0]: ";
 133     while (<STDIN>) {
 134       $answer = $_;
 135       chomp($answer);
 136       if ($answer =~ /^[1234567]$/) {
 137         if ($answer == 1) {
 138            setpwd();
 139         } elsif ($answer =~ /^[234567]$/) {
 140            addall($answer);
 141         } else {
 142           print "Sorry, something went wrong. With such option number you should not get here.\n\n";
 143           $end = 1;
 144         }
 145       } elsif ($answer == 0) {
 146         print "We hope we can help you next time \n\n";
 147         $end = 1;
 148       } else {
 149         print "Your answer was $answer\n";
 150         print "and that's wrong .... Try again\n";
 151       }
 152       last;
 153     }
 154   }
 155 }
 156
 157 ###
 158 # set a password for a user
 159 ###
 160 sub setpwd
 161 {
 162   my ($user,$pass,$host) = "";
 163   print "\n\nSetting a (new) password for a user.\n";
 164
 165   $user = user();
 166   $pass = newpass($user);
 167   $host = hosts($user);
 168
 169   print "#"x70;
 170   print "\n\n";
 171   print "That was it ... here is an overview of what you gave to me:\n";
 172   print "The username           : $user\n";
 173 #  print "The password          : $pass\n";
 174   print "The host               : $host\n";
 175   print "#"x70;
 176   print "\n\n";
 177   print "Are you pretty sure you would like to implement this [yes/no]: ";
 178   my $no = <STDIN>;
 179   chomp($no);
 180   if ($no =~ /n/i)
 181   {
 182     print "Okay .. that was it then ... See ya\n\n";
 183     return(0);
 184   }
 185   else
 186   {
 187     print "Okay ... let's go then ...\n\n";
 188   }
 189   $user = $dbh->quote($user);
 190   $host = $dbh->quote($host);
 191   if ($pass eq '')
 192   {
 193     $pass = "''";
 194   }
 195   else
 196   {
 197     $pass = "PASSWORD(". $dbh->quote($pass) . ")";
 198   }
 199   my $sth = $dbh->prepare("update user set Password=$pass where User = $user and Host = $host") || die $dbh->errstr;
 200   $sth->execute || die $dbh->errstr;
 201   $sth->finish;
 202   print "The password is set for user $user.\n\n";
 203
 204 }
 205
 206 ###
 207 # all things which will be added are done here
 208 ###
 209 sub addall {
 210   my ($todo) = @_;
 211   my ($answer,$good,$db,$user,$pass,$host,$priv);
 212
 213   if ($todo == 2) {
 214     $db = newdatabase();
 215   } else {
 216     $db = database();
 217   }
 218
 219   $user = newuser();
 220   $pass = newpass("$user");
 221   $host = newhosts();
 222
 223   print "#"x70;
 224   print "\n\n";
 225   print "That was it ... here is an overview of what you gave to me:\n";
 226   print "The database name      : $db\n";
 227   print "The username           : $user\n";
 228 #  print "The password          : $pass\n";
 229   print "The host(s)            : $host\n";
 230   print "#"x70;
 231   print "\n\n";
 232   print "Are you pretty sure you would like to implement this [yes/no]: ";
 233   my $no = <STDIN>;
 234   chomp($no);
 235   if ($no =~ /n/i) {
 236     print "Okay .. that was it then ... See ya\n\n";
 237     return(0);
 238   } else {
 239     print "Okay ... let's go then ...\n\n";
 240   }
 241
 242   if ($todo == 2) {
 243     # create the database
 244     if ($db) {
 245     my $sth = $dbh->do("CREATE DATABASE $db") || $dbh->errstr;
 246     } else {
 247       print STDERR "What do you want? You wanted to create new database and add new user, right?\n";
 248       die "But then specify databasename, please\n";
 249   }
 250   }
 251
 252   if ( ( !$todo ) or not ( $todo =~ m/^[2-7]$/ ) ) {
 253     print STDERR "Sorry, select option $todo isn't known inside the program .. See ya\n";
 254     quit();
 255   }
 256
 257   my @hosts = split(/,/,$host);
 258   if (!$user) {
 259      die "username not specified: $user\n";
 260   }
 261   if (!$db) {
 262      die "databasename is not specified nor *\n";
 263   }
 264   foreach $host (@hosts) {
 265     # user privileges: SELECT
 266     if (($todo == 2) || ($todo == 3)) {
 267       $sth = $dbh->do("GRANT SELECT ON $db.* TO $user@\"$host\" IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
 268     } elsif ($todo == 4) {
 269       # user privileges: SELECT,INSERT,UPDATE,DELETE
 270       $sth = $dbh->do("GRANT SELECT,INSERT,UPDATE,DELETE ON $db.* TO $user@\"$host\" IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
 271     } elsif ($todo == 5) {
 272       # user privileges: SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,CREATE TEMPORARY TABLES
 273       $sth = $dbh->do("GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,CREATE TEMPORARY TABLES ON $db.* TO $user@\"$host\" IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
 274     } elsif ($todo == 6) {
 275        # all privileges
 276        $sth = $dbh->do("GRANT ALL ON $db.* TO \'$user\'\@\'$host\' IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
 277     } elsif ($todo == 7) {
 278        # all privileges set to N
 279        $sth = $dbh->do("REVOKE ALL ON $db.* FROM \'$user\'\@\'$host\'") || die $dbh->errstr;
 280     }
 281     }
 282   $dbh->do("FLUSH PRIVILEGES") || print STDERR "Can't flush privileges\n";
 283   print "Everything is inserted and mysql privileges have been reloaded.\n\n";
 284 }
 285
 286 ###
 287 # ask for a new database name
 288 ###
 289 sub newdatabase {
 290   my ($answer,$good,$db);
 291   print "\n\nWhich database would you like to add: ";
 292   while (<STDIN>) {
 293     $answer = $_;
 294     $good = 0;
 295     chomp($answer);
 296     if ($answer) {
 297       my $sth = $dbh->prepare("SHOW DATABASES") || die $dbh->errstr;
 298       $sth->execute || die $dbh->errstr;
 299       while (my @r = $sth->fetchrow_array) {
 300         if ($r[0] eq $answer) {
 301           print "\n\nSorry, this database name is already in use; try something else: ";
 302           $good = 1;
 303         }
 304       }
 305     } else {
 306       print "You must type something ...\nTry again: ";
 307       next;
 308     }
 309     last if ($good == 0);
 310   }
 311   $db = $answer;
 312   print "The new database $db will be created\n";
 313   return($db);
 314 }
 315
 316 ###
 317 # select a database
 318 ###
 319 sub database {
 320   my ($answer,$good,$db);
 321   print "\n\nWhich database from existing databases would you like to select: \n";
 322   print "You can choose from: \n";
 323   my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
 324   $sth->execute || die $dbh->errstr;
 325   while (my @r = $sth->fetchrow_array) {
 326     print "  - $r[0] \n";
 327   }
 328   print "Which database will it be (case sensitive). Type * for any: \n";
 329   while (<STDIN>) {
 330     $answer = $_;
 331     $good = 0;
 332     chomp($answer);
 333     if ($answer) {
 334       if ($answer eq "*") {
 335         print "OK, the user entry will NOT be limited to any database";
 336         return("*");
 337       }
 338       my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
 339       $sth->execute || die $dbh->errstr;
 340       while (my @r = $sth->fetchrow_array) {
 341         if ($r[0] eq $answer) {
 342           $good = 1;
 343           $db = $r[0];
 344           last;
 345         }
 346       }
 347     } else {
 348         print "Type either database name or * meaning any databasename. That means";
 349         print " any of those above but also any which will be created in future!";
 350         print " This option gives a user chance to operate on databse mysql, which";
 351         print " contains privilege settings. That is really risky!\n";
 352       next;
 353     }
 354     if ($good == 1) {
 355       last;
 356     } else {
 357       print "You must select one from the list.\nTry again: ";
 358       next;
 359     }
 360   }
 361   print "The database $db will be used.\n";
 362   return($db);
 363 }
 364
 365 ###
 366 # ask for a new username
 367 ###
 368 sub newuser
 369 {
 370   my $user = "";
 371   my $answer = "";
 372
 373   print "\nWhat username is to be created: ";
 374   while(<STDIN>)
 375   {
 376     $answer = $_;
 377     chomp($answer);
 378     if ($answer)
 379     {
 380       $user = $answer;
 381     }
 382     else
 383     {
 384       print "You must type something ...\nTry again: ";
 385       next;
 386     }
 387     last;
 388   }
 389   print "Username = $user\n";
 390   return($user);
 391 }
 392
 393 ###
 394 # ask for a user which is already in the user table
 395 ###
 396 sub user
 397 {
 398   my ($answer,$user);
 399
 400   print "\nFor which user do you want to specify a password: ";
 401   while(<STDIN>)
 402   {
 403     $answer = $_;
 404     chomp($answer);
 405     if ($answer)
 406     {
 407       my $sth = $dbh->prepare("select User from user where User = '$answer'") || die $dbh->errstr;
 408       $sth->execute || die $dbh->errstr;
 409       my @r = $sth->fetchrow_array;
 410       if ($r[0])
 411       {
 412         $user = $r[0];
 413       }
 414       else
 415       {
 416        print "Sorry, user $answer isn't known in the user table.\nTry again: ";
 417        next;
 418      }
 419     }
 420     else
 421     {
 422       print "You must type something ...\nTry again: ";
 423       next;
 424     }
 425     last;
 426   }
 427   print "Username = $user\n";
 428   return($user);
 429 }
 430
 431 ###
 432 # ask for a new password
 433 ###
 434 sub newpass
 435 {
 436   my ($user) = @_;
 437   my ($pass,$answer,$good,$yes);
 438
 439   print "Would you like to set a password for $user [y/n]: ";
 440   $yes = <STDIN>;
 441   chomp($yes);
 442   if ($yes =~ /y/)
 443   {
 444     system "stty -echo";
 445     print "What password do you want to specify for $user: ";
 446     while(<STDIN>)
 447     {
 448       $answer = $_;
 449       chomp($answer);
 450       system "stty echo";
 451       print "\n";
 452       if ($answer)
 453       {
 454         system "stty -echo";
 455         print "Type the password again: ";
 456         my $second = <STDIN>;
 457         chomp($second);
 458         system "stty echo";
 459         print "\n";
 460         if ($answer ne $second)
 461         {
 462           print "Passwords aren't the same; we begin from scratch again.\n";
 463           system "stty -echo";
 464           print "Password please: ";
 465           next;
 466         }
 467         else
 468         {
 469           $pass = $answer;
 470         }
 471       }
 472       else
 473       {
 474         print "You must type something ...\nTry again: ";
 475         next;
 476       }
 477       last;
 478     }
 479 #    print "The password for $user is $pass.\n";
 480   }
 481   else
 482   {
 483     print "We won't set a password so the user doesn't have to use it\n";
 484     $pass = "";
 485   }
 486   return($pass);
 487 }
 488
 489 ###
 490 # ask for new hosts
 491 ###
 492 sub newhosts
 493 {
 494   my ($host,$answer,$good);
 495
 496   print "We now need to know from what host(s) the user will connect.\n";
 497   print "Keep in mind that % means 'from any host' ...\n";
 498   print "The host please: ";
 499   while(<STDIN>)
 500   {
 501     $answer = $_;
 502     chomp($answer);
 503     if ($answer)
 504     {
 505       $host .= ",$answer";
 506       print "Would you like to add another host [yes/no]: ";
 507       my $yes = <STDIN>;
 508       chomp($yes);
 509       if ($yes =~ /y/i)
 510       {
 511         print "Okay, give us the host please: ";
 512         next;
 513       }
 514       else
 515       {
 516         print "Okay we keep it with this ...\n";
 517       }
 518     }
 519     else
 520     {
 521       print "You must type something ...\nTry again: ";
 522       next;
 523     }
 524     last;
 525   }
 526   $host =~ s/^,//;
 527   print "The following host(s) will be used: $host.\n";
 528   return($host);
 529 }
 530
 531 ###
 532 # ask for a host which is already in the user table
 533 ###
 534 sub hosts
 535 {
 536   my ($user) = @_;
 537   my ($answer,$good,$host);
 538
 539   print "We now need to know which host for $user we have to change.\n";
 540   print "Choose from the following hosts: \n";
 541   $user = $dbh->quote($user);
 542   my $sth = $dbh->prepare("select Host,User from user where User = $user") || die $dbh->errstr;
 543   $sth->execute || die $dbh->errstr;
 544   while (my @r = $sth->fetchrow_array)
 545   {
 546     print "  - $r[0] \n";
 547   }
 548   print "The host please (case sensitive): ";
 549   while(<STDIN>)
 550   {
 551     $answer = $_;
 552     chomp($answer);
 553     if ($answer)
 554     {
 555       $sth = $dbh->prepare("select Host,User from user where Host = '$answer' and User = $user") || die $dbh->errstr;
 556       $sth->execute || die $dbh->errstr;
 557       my @r = $sth->fetchrow_array;
 558       if ($r[0])
 559       {
 560         $host = $answer;
 561         last;
 562       }
 563       else
 564       {
 565         print "You have to select a host from the list ...\nTry again: ";
 566         next;
 567       }
 568     }
 569     else
 570     {
 571       print "You have to type something ...\nTry again: ";
 572       next;
 573     }
 574     last;
 575   }
 576   print "The following host will be used: $host.\n";
 577   return($host);
 578 }
 579
 580 ###
 581 # a nice quit (first disconnect and then exit
 582 ###
 583 sub quit
 584 {
 585   $dbh->disconnect;
 586   exit(0);
 587 }
 588
 589 ###
 590 # Read variables password, port and socket from .my.cnf under the client
 591 # or perl groups
 592 ###
 593
 594 sub read_my_cnf
 595 {
 596   open(TMP,$ENV{'HOME'} . "/.my.cnf") || return 1;
 597   while (<TMP>)
 598   {
 599     if (/^\[(client|perl)\]/i)
 600     {
 601       while ((defined($_=<TMP>)) && !/^\[\w+\]/)
 602       {
 603         print $_;
 604         if (/^host\s*=\s*(\S+)/i)
 605         {
 606           $opt_host = $1;
 607         }
 608         elsif (/^user\s*=\s*(\S+)/i)
 609         {
 610           $opt_user = $1;
 611         }
 612         elsif (/^password\s*=\s*(\S+)/i)
 613         {
 614           $opt_password = $1;
 615         }
 616         elsif (/^port\s*=\s*(\S+)/i)
 617         {
 618           $opt_port = $1;
 619         }
 620         elsif (/^socket\s*=\s*(\S+)/i)
 621         {
 622           $opt_socket = $1;
 623         }
 624       }
 625     }
 626   }
 627   close(TMP);
 628 }
 629
 630 ###
 631 # the help text
 632 ###
 633 sub usage
 634 {
 635   print <<EOL;
 636 ----------------------------------------------------------------------
 637                  The permission setter for MySQL.
 638                       version: $version
 639
 640                  made by: Luuk de Boer <luuk\@wxs.nl>
 641 ----------------------------------------------------------------------
 642
 643 The permission setter is a little program which can help you add users
 644 or databases or change passwords in MySQL. Keep in mind that we don't
 645 check permissions which already been set in MySQL. So if you can't
 646 connect to MySQL using the permission you just added, take a look at
 647 the permissions which have already been set in MySQL.
 648
 649 The permission setter first reads your .my.cnf file in your Home
 650 directory if it exists.
 651
 652 Options for the permission setter:
 653
 654 --help          : print this help message and exit.
 655
 656 The options shown below are used for making the connection to the MySQL
 657 server. Keep in mind that the permissions for the user specified via
 658 these options must be sufficient to add users / create databases / set
 659 passwords.
 660
 661 --user          : is the username to connect with.
 662 --password      : the password of the username.
 663 --host          : the host to connect to.
 664 --socket        : the socket to connect to.
 665 --port          : the port number of the host to connect to.
 666
 667 If you don't give a password and no password is set in your .my.cnf
 668 file, then the permission setter will ask for a password.
 669
 670
 671 EOL
 672 exit(0);
 673 }