2
## Emacs, this is -*- perl -*- mode? :-)
4
# Copyright (c) 2000, 2007 MySQL AB, 2009 Sun Microsystems, Inc.
5
# Use is subject to license terms.
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
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.
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,
23
## Permission setter for MySQL
25
## mady by Luuk de Boer (luuk@wxs.nl) 1998.
26
## it's made under GPL ...:-))
29
############################################################################
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
45
# empty ... suggestions ... mail them to me ...
53
use vars qw($dbh $sth $hostname $opt_user $opt_password $opt_help $opt_host
54
$opt_socket $opt_port $host $version);
59
$dbh=$host=$opt_user= $opt_password= $opt_help= $opt_host= $opt_socket= "";
62
read_my_cnf(); # Read options from ~/.my.cnf
64
GetOptions("user=s","password=s","help","host=s","socket=s","port=i");
66
usage() if ($opt_help); # the help function
70
$sqlhost = "localhost";
77
# ask for a password if no password is set already
78
if ($opt_password eq '')
81
print "Password for user $opt_user to connect to MySQL: ";
82
$opt_password = <STDIN>;
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");
93
# the start of the program
98
# below all subroutines of the program
102
# the beginning of the program
104
sub q1 { # first question ...
109
print "## Welcome to the permission setter $version for MySQL.\n";
110
print "## made by Luuk de Boer\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]: ";
136
if ($answer =~ /^[1234567]$/) {
139
} elsif ($answer =~ /^[234567]$/) {
142
print "Sorry, something went wrong. With such option number you should not get here.\n\n";
145
} elsif ($answer == 0) {
146
print "We hope we can help you next time \n\n";
149
print "Your answer was $answer\n";
150
print "and that's wrong .... Try again\n";
158
# set a password for a user
162
my ($user,$pass,$host) = "";
163
print "\n\nSetting a (new) password for a user.\n";
166
$pass = newpass($user);
167
$host = hosts($user);
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";
177
print "Are you pretty sure you would like to implement this [yes/no]: ";
182
print "Okay .. that was it then ... See ya\n\n";
187
print "Okay ... let's go then ...\n\n";
189
$user = $dbh->quote($user);
190
$host = $dbh->quote($host);
197
$pass = "PASSWORD(". $dbh->quote($pass) . ")";
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;
202
print "The password is set for user $user.\n\n";
207
# all things which will be added are done here
211
my ($answer,$good,$db,$user,$pass,$host,$priv);
220
$pass = newpass("$user");
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";
232
print "Are you pretty sure you would like to implement this [yes/no]: ";
236
print "Okay .. that was it then ... See ya\n\n";
239
print "Okay ... let's go then ...\n\n";
243
# create the database
245
my $sth = $dbh->do("CREATE DATABASE $db") || $dbh->errstr;
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";
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";
257
my @hosts = split(/,/,$host);
259
die "username not specified: $user\n";
262
die "databasename is not specified nor *\n";
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) {
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;
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";
287
# ask for a new database name
290
my ($answer,$good,$db);
291
print "\n\nWhich database would you like to add: ";
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: ";
306
print "You must type something ...\nTry again: ";
309
last if ($good == 0);
312
print "The new database $db will be created\n";
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) {
328
print "Which database will it be (case sensitive). Type * for any: \n";
334
if ($answer eq "*") {
335
print "OK, the user entry will NOT be limited to any database";
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) {
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";
357
print "You must select one from the list.\nTry again: ";
361
print "The database $db will be used.\n";
366
# ask for a new username
373
print "\nWhat username is to be created: ";
384
print "You must type something ...\nTry again: ";
389
print "Username = $user\n";
394
# ask for a user which is already in the user table
400
print "\nFor which user do you want to specify a password: ";
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;
416
print "Sorry, user $answer isn't known in the user table.\nTry again: ";
422
print "You must type something ...\nTry again: ";
427
print "Username = $user\n";
432
# ask for a new password
437
my ($pass,$answer,$good,$yes);
439
print "Would you like to set a password for $user [y/n]: ";
445
print "What password do you want to specify for $user: ";
455
print "Type the password again: ";
456
my $second = <STDIN>;
460
if ($answer ne $second)
462
print "Passwords aren't the same; we begin from scratch again.\n";
464
print "Password please: ";
474
print "You must type something ...\nTry again: ";
479
# print "The password for $user is $pass.\n";
483
print "We won't set a password so the user doesn't have to use it\n";
494
my ($host,$answer,$good);
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: ";
506
print "Would you like to add another host [yes/no]: ";
511
print "Okay, give us the host please: ";
516
print "Okay we keep it with this ...\n";
521
print "You must type something ...\nTry again: ";
527
print "The following host(s) will be used: $host.\n";
532
# ask for a host which is already in the user table
537
my ($answer,$good,$host);
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)
548
print "The host please (case sensitive): ";
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;
565
print "You have to select a host from the list ...\nTry again: ";
571
print "You have to type something ...\nTry again: ";
576
print "The following host will be used: $host.\n";
581
# a nice quit (first disconnect and then exit
590
# Read variables password, port and socket from .my.cnf under the client
596
open(TMP,$ENV{'HOME'} . "/.my.cnf") || return 1;
599
if (/^\[(client|perl)\]/i)
601
while ((defined($_=<TMP>)) && !/^\[\w+\]/)
604
if (/^host\s*=\s*(\S+)/i)
608
elsif (/^user\s*=\s*(\S+)/i)
612
elsif (/^password\s*=\s*(\S+)/i)
616
elsif (/^port\s*=\s*(\S+)/i)
620
elsif (/^socket\s*=\s*(\S+)/i)
636
----------------------------------------------------------------------
637
The permission setter for MySQL.
640
made by: Luuk de Boer <luuk\@wxs.nl>
641
----------------------------------------------------------------------
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.
649
The permission setter first reads your .my.cnf file in your Home
650
directory if it exists.
652
Options for the permission setter:
654
--help : print this help message and exit.
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
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.
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.