~drizzle-trunk/drizzle/development

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
}