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