2
## Emacs, this is -*- perl -*- mode? :-)
4
# Copyright (C) 2000, 2007 MySQL AB, 2009 Sun Microsystems, Inc.
6
# This program is free software; you can redistribute it and/or
7
# modify it under the terms of the GNU Library General Public
8
# License as published by the Free Software Foundation; version 2
11
# This program is distributed in the hope that it will be useful,
12
# but WITHOUT ANY WARRANTY; without even the implied warranty of
13
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14
# Library General Public License for more details.
16
# You should have received a copy of the GNU Library General Public
17
# License along with this library; if not, write to the Free
18
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
22
## Permission setter for MySQL
24
## mady by Luuk de Boer (luuk@wxs.nl) 1998.
25
## it's made under GPL ...:-))
28
############################################################################
31
## 1.0 first start of the program
32
## 1.1 some changes from monty and after that
33
## initial release in mysql 3.22.10 (nov 1998)
34
## 1.2 begin screen now in a loop + quit is using 0 instead of 9
35
## after ideas of Paul DuBois.
36
## 1.2a Add Grant, References, Index and Alter privilege handling (Monty)
37
## 1.3 Applied patch provided by Martin Mokrejs <mmokrejs@natur.cuni.cz>
38
## (General code cleanup, use the GRANT statement instead of updating
39
## the privilege tables directly, added option to revoke privileges)
40
## 1.4 Remove option 6 which attempted to erroneously grant global privileges
44
# empty ... suggestions ... mail them to me ...
52
use vars qw($dbh $sth $hostname $opt_user $opt_password $opt_help $opt_host
53
$opt_socket $opt_port $host $version);
58
$dbh=$host=$opt_user= $opt_password= $opt_help= $opt_host= $opt_socket= "";
61
read_my_cnf(); # Read options from ~/.my.cnf
63
GetOptions("user=s","password=s","help","host=s","socket=s","port=i");
65
usage() if ($opt_help); # the help function
69
$sqlhost = "localhost";
76
# ask for a password if no password is set already
77
if ($opt_password eq '')
80
print "Password for user $opt_user to connect to MySQL: ";
81
$opt_password = <STDIN>;
88
# make the connection to MySQL
89
$dbh= DBI->connect("DBI:mysql:mysql:host=$sqlhost:port=$opt_port:mysql_socket=$opt_socket",$opt_user,$opt_password, {PrintError => 0}) ||
90
die("Can't make a connection to the mysql server.\n The error: $DBI::errstr");
92
# the start of the program
97
# below all subroutines of the program
101
# the beginning of the program
103
sub q1 { # first question ...
108
print "## Welcome to the permission setter $version for MySQL.\n";
109
print "## made by Luuk de Boer\n";
112
print "What would you like to do:\n";
113
print " 1. Set password for an existing user.\n";
114
print " 2. Create a database + user privilege for that database\n";
115
print " and host combination (user can only do SELECT)\n";
116
print " 3. Create/append user privilege for an existing database\n";
117
print " and host combination (user can only do SELECT)\n";
118
print " 4. Create/append broader user privileges for an existing\n";
119
print " database and host combination\n";
120
print " (user can do SELECT,INSERT,UPDATE,DELETE)\n";
121
print " 5. Create/append quite extended user privileges for an\n";
122
print " existing database and host combination (user can do\n";
123
print " SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,\n";
124
print " LOCK TABLES,CREATE TEMPORARY TABLES)\n";
125
print " 6. Create/append full privileges for an existing database\n";
126
print " and host combination (user has FULL privilege)\n";
127
print " 7. Remove all privileges for for an existing database and\n";
128
print " host combination.\n";
129
print " (user will have all permission fields set to N)\n";
130
print " 0. exit this program\n";
131
print "\nMake your choice [1,2,3,4,5,6,7,0]: ";
135
if ($answer =~ /^[1234567]$/) {
138
} elsif ($answer =~ /^[234567]$/) {
141
print "Sorry, something went wrong. With such option number you should not get here.\n\n";
144
} elsif ($answer == 0) {
145
print "We hope we can help you next time \n\n";
148
print "Your answer was $answer\n";
149
print "and that's wrong .... Try again\n";
157
# set a password for a user
161
my ($user,$pass,$host) = "";
162
print "\n\nSetting a (new) password for a user.\n";
165
$pass = newpass($user);
166
$host = hosts($user);
170
print "That was it ... here is an overview of what you gave to me:\n";
171
print "The username : $user\n";
172
# print "The password : $pass\n";
173
print "The host : $host\n";
176
print "Are you pretty sure you would like to implement this [yes/no]: ";
181
print "Okay .. that was it then ... See ya\n\n";
186
print "Okay ... let's go then ...\n\n";
188
$user = $dbh->quote($user);
189
$host = $dbh->quote($host);
196
$pass = "PASSWORD(". $dbh->quote($pass) . ")";
198
my $sth = $dbh->prepare("update user set Password=$pass where User = $user and Host = $host") || die $dbh->errstr;
199
$sth->execute || die $dbh->errstr;
201
print "The password is set for user $user.\n\n";
206
# all things which will be added are done here
210
my ($answer,$good,$db,$user,$pass,$host,$priv);
219
$pass = newpass("$user");
224
print "That was it ... here is an overview of what you gave to me:\n";
225
print "The database name : $db\n";
226
print "The username : $user\n";
227
# print "The password : $pass\n";
228
print "The host(s) : $host\n";
231
print "Are you pretty sure you would like to implement this [yes/no]: ";
235
print "Okay .. that was it then ... See ya\n\n";
238
print "Okay ... let's go then ...\n\n";
242
# create the database
244
my $sth = $dbh->do("CREATE DATABASE $db") || $dbh->errstr;
246
print STDERR "What do you want? You wanted to create new database and add new user, right?\n";
247
die "But then specify databasename, please\n";
251
if ( ( !$todo ) or not ( $todo =~ m/^[2-7]$/ ) ) {
252
print STDERR "Sorry, select option $todo isn't known inside the program .. See ya\n";
256
my @hosts = split(/,/,$host);
258
die "username not specified: $user\n";
261
die "databasename is not specified nor *\n";
263
foreach $host (@hosts) {
264
# user privileges: SELECT
265
if (($todo == 2) || ($todo == 3)) {
266
$sth = $dbh->do("GRANT SELECT ON $db.* TO $user@\"$host\" IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
267
} elsif ($todo == 4) {
268
# user privileges: SELECT,INSERT,UPDATE,DELETE
269
$sth = $dbh->do("GRANT SELECT,INSERT,UPDATE,DELETE ON $db.* TO $user@\"$host\" IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
270
} elsif ($todo == 5) {
271
# user privileges: SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,CREATE TEMPORARY TABLES
272
$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;
273
} elsif ($todo == 6) {
275
$sth = $dbh->do("GRANT ALL ON $db.* TO \'$user\'\@\'$host\' IDENTIFIED BY \'$pass\'") || die $dbh->errstr;
276
} elsif ($todo == 7) {
277
# all privileges set to N
278
$sth = $dbh->do("REVOKE ALL ON $db.* FROM \'$user\'\@\'$host\'") || die $dbh->errstr;
281
$dbh->do("FLUSH PRIVILEGES") || print STDERR "Can't flush privileges\n";
282
print "Everything is inserted and mysql privileges have been reloaded.\n\n";
286
# ask for a new database name
289
my ($answer,$good,$db);
290
print "\n\nWhich database would you like to add: ";
296
my $sth = $dbh->prepare("SHOW DATABASES") || die $dbh->errstr;
297
$sth->execute || die $dbh->errstr;
298
while (my @r = $sth->fetchrow_array) {
299
if ($r[0] eq $answer) {
300
print "\n\nSorry, this database name is already in use; try something else: ";
305
print "You must type something ...\nTry again: ";
308
last if ($good == 0);
311
print "The new database $db will be created\n";
319
my ($answer,$good,$db);
320
print "\n\nWhich database from existing databases would you like to select: \n";
321
print "You can choose from: \n";
322
my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
323
$sth->execute || die $dbh->errstr;
324
while (my @r = $sth->fetchrow_array) {
327
print "Which database will it be (case sensitive). Type * for any: \n";
333
if ($answer eq "*") {
334
print "OK, the user entry will NOT be limited to any database";
337
my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
338
$sth->execute || die $dbh->errstr;
339
while (my @r = $sth->fetchrow_array) {
340
if ($r[0] eq $answer) {
347
print "Type either database name or * meaning any databasename. That means";
348
print " any of those above but also any which will be created in future!";
349
print " This option gives a user chance to operate on databse mysql, which";
350
print " contains privilege settings. That is really risky!\n";
356
print "You must select one from the list.\nTry again: ";
360
print "The database $db will be used.\n";
365
# ask for a new username
372
print "\nWhat username is to be created: ";
383
print "You must type something ...\nTry again: ";
388
print "Username = $user\n";
393
# ask for a user which is already in the user table
399
print "\nFor which user do you want to specify a password: ";
406
my $sth = $dbh->prepare("select User from user where User = '$answer'") || die $dbh->errstr;
407
$sth->execute || die $dbh->errstr;
408
my @r = $sth->fetchrow_array;
415
print "Sorry, user $answer isn't known in the user table.\nTry again: ";
421
print "You must type something ...\nTry again: ";
426
print "Username = $user\n";
431
# ask for a new password
436
my ($pass,$answer,$good,$yes);
438
print "Would you like to set a password for $user [y/n]: ";
444
print "What password do you want to specify for $user: ";
454
print "Type the password again: ";
455
my $second = <STDIN>;
459
if ($answer ne $second)
461
print "Passwords aren't the same; we begin from scratch again.\n";
463
print "Password please: ";
473
print "You must type something ...\nTry again: ";
478
# print "The password for $user is $pass.\n";
482
print "We won't set a password so the user doesn't have to use it\n";
493
my ($host,$answer,$good);
495
print "We now need to know from what host(s) the user will connect.\n";
496
print "Keep in mind that % means 'from any host' ...\n";
497
print "The host please: ";
505
print "Would you like to add another host [yes/no]: ";
510
print "Okay, give us the host please: ";
515
print "Okay we keep it with this ...\n";
520
print "You must type something ...\nTry again: ";
526
print "The following host(s) will be used: $host.\n";
531
# ask for a host which is already in the user table
536
my ($answer,$good,$host);
538
print "We now need to know which host for $user we have to change.\n";
539
print "Choose from the following hosts: \n";
540
$user = $dbh->quote($user);
541
my $sth = $dbh->prepare("select Host,User from user where User = $user") || die $dbh->errstr;
542
$sth->execute || die $dbh->errstr;
543
while (my @r = $sth->fetchrow_array)
547
print "The host please (case sensitive): ";
554
$sth = $dbh->prepare("select Host,User from user where Host = '$answer' and User = $user") || die $dbh->errstr;
555
$sth->execute || die $dbh->errstr;
556
my @r = $sth->fetchrow_array;
564
print "You have to select a host from the list ...\nTry again: ";
570
print "You have to type something ...\nTry again: ";
575
print "The following host will be used: $host.\n";
580
# a nice quit (first disconnect and then exit
589
# Read variables password, port and socket from .my.cnf under the client
595
open(TMP,$ENV{'HOME'} . "/.my.cnf") || return 1;
598
if (/^\[(client|perl)\]/i)
600
while ((defined($_=<TMP>)) && !/^\[\w+\]/)
603
if (/^host\s*=\s*(\S+)/i)
607
elsif (/^user\s*=\s*(\S+)/i)
611
elsif (/^password\s*=\s*(\S+)/i)
615
elsif (/^port\s*=\s*(\S+)/i)
619
elsif (/^socket\s*=\s*(\S+)/i)
635
----------------------------------------------------------------------
636
The permission setter for MySQL.
639
made by: Luuk de Boer <luuk\@wxs.nl>
640
----------------------------------------------------------------------
642
The permission setter is a little program which can help you add users
643
or databases or change passwords in MySQL. Keep in mind that we don't
644
check permissions which already been set in MySQL. So if you can't
645
connect to MySQL using the permission you just added, take a look at
646
the permissions which have already been set in MySQL.
648
The permission setter first reads your .my.cnf file in your Home
649
directory if it exists.
651
Options for the permission setter:
653
--help : print this help message and exit.
655
The options shown below are used for making the connection to the MySQL
656
server. Keep in mind that the permissions for the user specified via
657
these options must be sufficient to add users / create databases / set
660
--user : is the username to connect with.
661
--password : the password of the username.
662
--host : the host to connect to.
663
--socket : the socket to connect to.
664
--port : the port number of the host to connect to.
666
If you don't give a password and no password is set in your .my.cnf
667
file, then the permission setter will ask for a password.