~drizzle-trunk/drizzle/development : contents of scripts/mysql

~drizzle-trunk/drizzle/development : (revision 1)

#!@PERL@
## Emacs, this is -*- perl -*- mode? :-)
##
##        Permission setter for MySQL
##
##        mady by Luuk de Boer (luuk@wxs.nl) 1998.
##        it's made under GPL ...:-))
##
##
############################################################################
## History
##
## 1.0 first start of the program
## 1.1 some changes from monty and after that
##     initial release in mysql 3.22.10 (nov 1998)
## 1.2 begin screen now in a loop + quit is using 0 instead of 9
##     after ideas of Paul DuBois.
## 1.2a Add Grant, References, Index and Alter privilege handling (Monty)
## 1.3 Applied patch provided by Martin Mokrejs <mmokrejs@natur.cuni.cz>
##     (General code cleanup, use the GRANT statement instead of updating
##     the privilege tables directly, added option to revoke privileges)
## 1.4 Remove option 6 which attempted to erroneously grant global privileges

#### TODO
#
# empty ... suggestions ... mail them to me ...


$version="1.4";

use DBI;
use Getopt::Long;
use strict;
use vars qw($dbh $sth $hostname $opt_user $opt_password $opt_help $opt_host
	    $opt_socket $opt_port $host $version);

my $sqlhost = "";
my $user = "";

$dbh=$host=$opt_user= $opt_password= $opt_help= $opt_host= $opt_socket= "";
$opt_port=0;

read_my_cnf();		# Read options from ~/.my.cnf

GetOptions("user=s","password=s","help","host=s","socket=s","port=i");

usage() if ($opt_help); # the help function

if ($opt_host eq '')
{
  $sqlhost = "localhost";
}
else
{
  $sqlhost = $opt_host;
}

# ask for a password if no password is set already
if ($opt_password eq '')
{
  system "stty -echo";
  print "Password for user $opt_user to connect to MySQL: ";
  $opt_password = <STDIN>;
  chomp($opt_password);
  system "stty echo";
  print "\n";
}


# make the connection to MySQL
$dbh= DBI->connect("DBI:mysql:mysql:host=$sqlhost:port=$opt_port:mysql_socket=$opt_socket",$opt_user,$opt_password, {PrintError => 0}) ||
  die("Can't make a connection to the mysql server.\n The error: $DBI::errstr");

# the start of the program
&q1();
exit(0); # the end...

#####
# below all subroutines of the program
#####

###
# the beginning of the program
###
sub q1 { # first question ...
  my ($answer,$end);
  while (! $end) {
    print "#"x70;
    print "\n";
    print "## Welcome to the permission setter $version for MySQL.\n";
    print "## made by Luuk de Boer\n";
    print "#"x70;
    print "\n";
    print "What would you like to do:\n";
    print "  1. Set password for an existing user.\n";
    print "  2. Create a database + user privilege for that database\n";
		print "     and host combination (user can only do SELECT)\n";
    print "  3. Create/append user privilege for an existing database\n";
		print "     and host combination (user can only do SELECT)\n";
    print "  4. Create/append broader user privileges for an existing\n";
		print "     database and host combination\n";
		print "     (user can do SELECT,INSERT,UPDATE,DELETE)\n";
    print "  5. Create/append quite extended user privileges for an\n";
		print "     existing database and host combination (user can do\n";
		print "     SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,\n";
		print "     LOCK TABLES,CREATE TEMPORARY TABLES)\n";
    print "  6. Create/append full privileges for an existing database\n";
		print "     and host combination (user has FULL privilege)\n";
    print "  7. Remove all privileges for for an existing database and\n";
		print "     host combination.\n";
    print "     (user will have all permission fields set to N)\n";
    print "  0. exit this program\n";
    print "\nMake your choice [1,2,3,4,5,6,7,0]: ";
    while (<STDIN>) {
      $answer = $_;
      chomp($answer);
      if ($answer =~ /^[1234567]$/) {
        if ($answer == 1) {
	   setpwd();
        } elsif ($answer =~ /^[234567]$/) {
	   addall($answer);
	} else {
          print "Sorry, something went wrong. With such option number you should not get here.\n\n";
          $end = 1;
        }
      } elsif ($answer == 0) {
        print "We hope we can help you next time \n\n";
	$end = 1;
      } else {
        print "Your answer was $answer\n";
        print "and that's wrong .... Try again\n";
      }
      last;
    }
  }
}

###
# set a password for a user
###
sub setpwd
{
  my ($user,$pass,$host) = "";
  print "\n\nSetting a (new) password for a user.\n";

  $user = user();
  $pass = newpass($user);
  $host = hosts($user);

  print "#"x70;
  print "\n\n";
  print "That was it ... here is an overview of what you gave to me:\n";
  print "The username 		: $user\n";
#  print "The password		: $pass\n";
  print "The host		: $host\n";
  print "#"x70;
  print "\n\n";
  print "Are you pretty sure you would like to implement this [yes/no]: ";
  my $no = <STDIN>;
  chomp($no);
  if ($no =~ /n/i)
  {
    print "Okay .. that was it then ... See ya\n\n";
    return(0);
  }
  else
  {
    print "Okay ... let's go then ...\n\n";
  }
  $user = $dbh->quote($user);
  $host = $dbh->quote($host);
  if ($pass eq '')
  {
    $pass = "''";
  }
  else
  {
    $pass = "PASSWORD(". $dbh->quote($pass) . ")";
  }
  my $sth = $dbh->prepare("update user set Password=$pass where User = $user and Host = $host") || die $dbh->errstr;
  $sth->execute || die $dbh->errstr;
  $sth->finish;
  print "The password is set for user $user.\n\n";

}

###
# all things which will be added are done here
###
sub addall {
  my ($todo) = @_;
  my ($answer,$good,$db,$user,$pass,$host,$priv);

  if ($todo == 2) {
    $db = newdatabase();
  } else {
    $db = database();
  }

  $user = newuser();
  $pass = newpass("$user");
  $host = newhosts();

  print "#"x70;
  print "\n\n";
  print "That was it ... here is an overview of what you gave to me:\n";
  print "The database name	: $db\n";
  print "The username 		: $user\n";
#  print "The password		: $pass\n";
  print "The host(s)		: $host\n";
  print "#"x70;
  print "\n\n";
  print "Are you pretty sure you would like to implement this [yes/no]: ";
  my $no = <STDIN>;
  chomp($no);
  if ($no =~ /n/i) {
    print "Okay .. that was it then ... See ya\n\n";
    return(0);
  } else {
    print "Okay ... let's go then ...\n\n";
  }

  if ($todo == 2) {
    # create the database
    if ($db) {
    my $sth = $dbh->do("CREATE DATABASE $db") || $dbh->errstr;
    } else {
      print STDERR "What do you want? You wanted to create new database and add new user, right?\n";
      die "But then specify databasename, please\n";
  }
  }

  if ( ( !$todo ) or not ( $todo =~ m/^[2-7]$/ ) ) {
    print STDERR "Sorry, select option $todo isn't known inside the program .. See ya\n";
    quit();
  }

  my @hosts = split(/,/,$host);
  if (!$user) {
     die "username not specified: $user\n";
  }
  if (!$db) {
     die "databasename is not specified nor *\n";
  }
  foreach $host (@hosts) {
    # user privileges: SELECT
    if (($todo == 2) || ($todo == 3)) {
      $sth = $dbh->do("GRANT SELECT ON $db.* TO $user@\"$host\" IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
    } elsif ($todo == 4) {
      # user privileges: SELECT,INSERT,UPDATE,DELETE
      $sth = $dbh->do("GRANT SELECT,INSERT,UPDATE,DELETE ON $db.* TO $user@\"$host\" IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
    } elsif ($todo == 5) {
      # user privileges: SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,CREATE TEMPORARY TABLES
      $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;
    } elsif ($todo == 6) {
       # all privileges
       $sth = $dbh->do("GRANT ALL ON $db.* TO \'$user\'\@\'$host\' IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
    } elsif ($todo == 7) {
       # all privileges set to N
       $sth = $dbh->do("REVOKE ALL ON *.* FROM \'$user\'\@\'$host\'") || die $dbh->errstr;
    }
    }
  $dbh->do("FLUSH PRIVILEGES") || print STDERR "Can't flush privileges\n";
  print "Everything is inserted and mysql privileges have been reloaded.\n\n";
}

###
# ask for a new database name
###
sub newdatabase {
  my ($answer,$good,$db);
  print "\n\nWhich database would you like to add: ";
  while (<STDIN>) {
    $answer = $_;
    $good = 0;
    chomp($answer);
    if ($answer) {
      my $sth = $dbh->prepare("SHOW DATABASES") || die $dbh->errstr;
      $sth->execute || die $dbh->errstr;
      while (my @r = $sth->fetchrow_array) {
        if ($r[0] eq $answer) {
          print "\n\nSorry, this database name is already in use; try something else: ";
          $good = 1;
        }
      }
    } else {
      print "You must type something ...\nTry again: ";
      next;
    }
    last if ($good == 0);
  }
  $db = $answer;
  print "The new database $db will be created\n";
  return($db);
}

###
# select a database
###
sub database {
  my ($answer,$good,$db);
  print "\n\nWhich database from existing databases would you like to select: \n";
  print "You can choose from: \n";
  my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
  $sth->execute || die $dbh->errstr;
  while (my @r = $sth->fetchrow_array) {
    print "  - $r[0] \n";
  }
  print "Which database will it be (case sensitive). Type * for any: \n";
  while (<STDIN>) {
    $answer = $_;
    $good = 0;
    chomp($answer);
    if ($answer) {
      if ($answer eq "*") {
        print "OK, the user entry will NOT be limited to any database";
        return("*");
      }
      my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
      $sth->execute || die $dbh->errstr;
      while (my @r = $sth->fetchrow_array) {
        if ($r[0] eq $answer) {
          $good = 1;
          $db = $r[0];
          last;
        }
      }
    } else {
        print "Type either database name or * meaning any databasename. That means";
        print " any of those above but also any which will be created in future!";
        print " This option gives a user chance to operate on databse mysql, which";
        print " contains privilege settings. That is really risky!\n";
      next;
    }
    if ($good == 1) {
      last;
    } else {
      print "You must select one from the list.\nTry again: ";
      next;
    }
  }
  print "The database $db will be used.\n";
  return($db);
}

###
# ask for a new username
###
sub newuser
{
  my $user = "";
  my $answer = "";

  print "\nWhat username is to be created: ";
  while(<STDIN>)
  {
    $answer = $_;
    chomp($answer);
    if ($answer)
    {
      $user = $answer;
    }
    else
    {
      print "You must type something ...\nTry again: ";
      next;
    }
    last;
  }
  print "Username = $user\n";
  return($user);
}

###
# ask for a user which is already in the user table
###
sub user
{
  my ($answer,$user);

  print "\nFor which user do you want to specify a password: ";
  while(<STDIN>)
  {
    $answer = $_;
    chomp($answer);
    if ($answer)
    {
      my $sth = $dbh->prepare("select User from user where User = '$answer'") || die $dbh->errstr;
      $sth->execute || die $dbh->errstr;
      my @r = $sth->fetchrow_array;
      if ($r[0])
      {
        $user = $r[0];
      }
      else
      {
       print "Sorry, user $answer isn't known in the user table.\nTry again: ";
       next;
     }
    }
    else
    {
      print "You must type something ...\nTry again: ";
      next;
    }
    last;
  }
  print "Username = $user\n";
  return($user);
}

###
# ask for a new password
###
sub newpass
{
  my ($user) = @_;
  my ($pass,$answer,$good,$yes);

  print "Would you like to set a password for $user [y/n]: ";
  $yes = <STDIN>;
  chomp($yes);
  if ($yes =~ /y/)
  {
    system "stty -echo";
    print "What password do you want to specify for $user: ";
    while(<STDIN>)
    {
      $answer = $_;
      chomp($answer);
      system "stty echo";
      print "\n";
      if ($answer)
      {
        system "stty -echo";
        print "Type the password again: ";
        my $second = <STDIN>;
        chomp($second);
        system "stty echo";
        print "\n";
        if ($answer ne $second)
        {
          print "Passwords aren't the same; we begin from scratch again.\n";
          system "stty -echo";
          print "Password please: ";
          next;
        }
        else
        {
          $pass = $answer;
        }
      }
      else
      {
        print "You must type something ...\nTry again: ";
        next;
      }
      last;
    }
#    print "The password for $user is $pass.\n";
  }
  else
  {
    print "We won't set a password so the user doesn't have to use it\n";
    $pass = "";
  }
  return($pass);
}

###
# ask for new hosts
###
sub newhosts
{
  my ($host,$answer,$good);

  print "We now need to know from what host(s) the user will connect.\n";
  print "Keep in mind that % means 'from any host' ...\n";
  print "The host please: ";
  while(<STDIN>)
  {
    $answer = $_;
    chomp($answer);
    if ($answer)
    {
      $host .= ",$answer";
      print "Would you like to add another host [yes/no]: ";
      my $yes = <STDIN>;
      chomp($yes);
      if ($yes =~ /y/i)
      {
        print "Okay, give us the host please: ";
        next;
      }
      else
      {
        print "Okay we keep it with this ...\n";
      }
    }
    else
    {
      print "You must type something ...\nTry again: ";
      next;
    }
    last;
  }
  $host =~ s/^,//;
  print "The following host(s) will be used: $host.\n";
  return($host);
}

###
# ask for a host which is already in the user table
###
sub hosts
{
  my ($user) = @_;
  my ($answer,$good,$host);

  print "We now need to know which host for $user we have to change.\n";
  print "Choose from the following hosts: \n";
  $user = $dbh->quote($user);
  my $sth = $dbh->prepare("select Host,User from user where User = $user") || die $dbh->errstr;
  $sth->execute || die $dbh->errstr;
  while (my @r = $sth->fetchrow_array)
  {
    print "  - $r[0] \n";
  }
  print "The host please (case sensitive): ";
  while(<STDIN>)
  {
    $answer = $_;
    chomp($answer);
    if ($answer)
    {
      $sth = $dbh->prepare("select Host,User from user where Host = '$answer' and User = $user") || die $dbh->errstr;
      $sth->execute || die $dbh->errstr;
      my @r = $sth->fetchrow_array;
      if ($r[0])
      {
        $host = $answer;
        last;
      }
      else
      {
        print "You have to select a host from the list ...\nTry again: ";
        next;
      }
    }
    else
    {
      print "You have to type something ...\nTry again: ";
      next;
    }
    last;
  }
  print "The following host will be used: $host.\n";
  return($host);
}

###
# a nice quit (first disconnect and then exit
###
sub quit
{
  $dbh->disconnect;
  exit(0);
}

###
# Read variables password, port and socket from .my.cnf under the client
# or perl groups
###

sub read_my_cnf
{
  open(TMP,$ENV{'HOME'} . "/.my.cnf") || return 1;
  while (<TMP>)
  {
    if (/^\[(client|perl)\]/i)
    {
      while ((defined($_=<TMP>)) && !/^\[\w+\]/)
      {
	print $_;
	if (/^host\s*=\s*(\S+)/i)
	{
	  $opt_host = $1;
	}
	elsif (/^user\s*=\s*(\S+)/i)
	{
	  $opt_user = $1;
	}
	elsif (/^password\s*=\s*(\S+)/i)
	{
	  $opt_password = $1;
	}
	elsif (/^port\s*=\s*(\S+)/i)
	{
	  $opt_port = $1;
	}
	elsif (/^socket\s*=\s*(\S+)/i)
	{
	  $opt_socket = $1;
	}
      }
    }
  }
  close(TMP);
}

###
# the help text
###
sub usage
{
  print <<EOL;
----------------------------------------------------------------------
                 The permission setter for MySQL.
                      version: $version

                 made by: Luuk de Boer <luuk\@wxs.nl>
----------------------------------------------------------------------

The permission setter is a little program which can help you add users
or databases or change passwords in MySQL. Keep in mind that we don't
check permissions which already been set in MySQL. So if you can't
connect to MySQL using the permission you just added, take a look at
the permissions which have already been set in MySQL.

The permission setter first reads your .my.cnf file in your Home
directory if it exists.

Options for the permission setter:

--help		: print this help message and exit.

The options shown below are used for making the connection to the MySQL
server. Keep in mind that the permissions for the user specified via
these options must be sufficient to add users / create databases / set
passwords.

--user		: is the username to connect with.
--password	: the password of the username.
--host		: the host to connect to.
--socket	: the socket to connect to.
--port		: the port number of the host to connect to.

If you don't give a password and no password is set in your .my.cnf
file, then the permission setter will ask for a password.


EOL
exit(0);
}

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