« back to all changes in this revision
Viewing changes to scripts/mysql_setpermission.sh
- browse files at revision 28.1.31
- compare with another revision
- compare with revision 577
- download diff
- download tarball
- view history from revision 28.1.31
- Committer: Monty Taylor
- Date: 2008-07-02 06:53:53 UTC
- mto: (28.2.1 packaging) (77.1.30 codestyle)
- mto: This revision was merged to the branch mainline in revision 51.
- Revision ID: monty@inaugust.com-20080702065353-2cc96bu1gpdrbkks
Deleted tons of pointless garbage from scripts.
- files removed:
- scripts/comp_sql.c
- files modified:
- Makefile.am
added
removed
scripts/mysql_setpermission.sh
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
}
Loggerhead is a web-based interface for Breezy
Version: 2.0.1