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