Skip to content

Commit 20fbcff

Browse files
author
Joerg Bruehe
committed
Fix for bug# 12794345:
Ensure a random password is set for the root user The approach is that the script "mysql_install_db" (Perl variant) supports a new option "--random-passwords". If this option is set, the script will (after setting the predefined accounts in the table "mysql.user") run commands from "mysql_security_commands.sql" (new file). These commands will set a random password for the root accounts and set the "password expired" flag, and they will remove the anonymous accounts.
1 parent 0febb96 commit 20fbcff

3 files changed

Lines changed: 238 additions & 19 deletions

File tree

‎scripts/CMakeLists.txt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,6 +76,7 @@ INSTALL(FILES
7676
${CMAKE_CURRENT_SOURCE_DIR}/mysql_system_tables_data.sql
7777
${CMAKE_CURRENT_SOURCE_DIR}/fill_help_tables.sql
7878
${CMAKE_CURRENT_SOURCE_DIR}/mysql_test_data_timezone.sql
79+
${CMAKE_CURRENT_SOURCE_DIR}/mysql_security_commands.sql
7980
${FIX_PRIVILEGES_SQL}
8081
DESTINATION ${INSTALL_MYSQLSHAREDIR} COMPONENT Server
8182
)

‎scripts/mysql_install_db.pl.in‎

Lines changed: 201 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,7 @@
3434
#
3535
##############################################################################
3636

37+
use Fcntl;
3738
use File::Basename;
3839
use Getopt::Long;
3940
use Sys::Hostname;
@@ -70,6 +71,9 @@ Usage: $0 [OPTIONS]
7071
--help Display this help and exit.
7172
--ldata=path The path to the MySQL data directory. Same as --datadir.
7273
--no-defaults Don't read default options from any option file.
74+
--random-passwords Create and set a random password for all root accounts
75+
and set the "password expired" flag,
76+
remove the anonymous accounts.
7377
--rpm For internal use. This option is used by RPM files
7478
during the MySQL installation process.
7579
--skip-name-resolve Use IP addresses rather than hostnames when creating
@@ -132,8 +136,9 @@ sub parse_arguments
132136
"verbose",
133137
"rpm",
134138
"help",
139+
"random-passwords",
135140

136-
# These options will also be pased to mysqld.
141+
# These options will also be pased to mysqld.
137142
"defaults-file=s",
138143
"defaults-extra-file=s",
139144
"no-defaults",
@@ -147,7 +152,7 @@ sub parse_arguments
147152
# the install package. See top-level 'dist-hook' make target.
148153
#
149154
# --windows is a deprecated alias
150-
"cross-bootstrap|windows", # FIXME undocumented, even needed?
155+
"cross-bootstrap|windows",
151156
) or usage();
152157

153158
usage() if $opt->{help};
@@ -249,6 +254,121 @@ sub quote_options {
249254
return join(" ", @cmd);
250255
}
251256

257+
##############################################################################
258+
#
259+
# Simple escape mechanism (\-escape any ' and \), suitable for two contexts:
260+
# - single-quoted SQL strings
261+
# - single-quoted option values on the right hand side of = in my.cnf
262+
#
263+
##############################################################################
264+
265+
# (Function and comment copied from 'mysql_secure_installation')
266+
267+
# These two contexts don't handle escapes identically. SQL strings allow
268+
# quoting any character (\C => C, for any C), but my.cnf parsing allows
269+
# quoting only \, ' or ". For example, password='a\b' quotes a 3-character
270+
# string in my.cnf, but a 2-character string in SQL.
271+
#
272+
# This simple escape works correctly in both places.
273+
274+
# FIXME: What about double quote in password? Not handled here - not needed?
275+
276+
sub basic_single_escape {
277+
my ($str) = @_;
278+
# Inside a character class, \ is not special; this escapes both \ and '
279+
$str =~ s/([\'])/\\$1/g;
280+
return $str;
281+
}
282+
283+
##############################################################################
284+
#
285+
# Handle the files with confidential contents
286+
#
287+
##############################################################################
288+
289+
my $secret_file; # full path name of the confidential file
290+
my $escaped_password; # the password, with special characters escaped
291+
292+
sub ensure_secret_file {
293+
$secret_file = $ENV{HOME} . "/.mysql_secret";
294+
295+
# Create safe files to avoid leaking info to other users
296+
# Loop may be extended if we need more ...
297+
foreach my $file ( $secret_file ) {
298+
next if -f $file; # Already exists
299+
local *FILE;
300+
sysopen(FILE, $file, O_CREAT, 0600)
301+
or die "ERROR: can't create $file: $!";
302+
close FILE;
303+
}
304+
}
305+
306+
##############################################################################
307+
#
308+
# Append an arbitrary number of lines to an existing file
309+
#
310+
##############################################################################
311+
312+
sub append_file {
313+
my $file = shift;
314+
-f $file or die "ERROR: file is missing \"$file\": $!";
315+
open(FILE, ">>$file") or die "ERROR: can't append to file \"$file\": $!";
316+
foreach my $line ( @_ ) {
317+
print FILE $line, "\n"; # Add EOL char
318+
}
319+
close FILE;
320+
}
321+
322+
##############################################################################
323+
#
324+
# Inform the user about the generated random password
325+
#
326+
##############################################################################
327+
328+
sub tell_root_password {
329+
my $now = localtime(); # scalar context = printable string
330+
331+
# Now, we need to tell the user the new root password.
332+
# We use "append_file" to protect the user in case they are doing multiple
333+
# installations intermixed with backups and restores.
334+
# While this would be really bad practice, it still might happen.
335+
# As long as this file is not destroyed, the time stamps may rescue them.
336+
# Having the comment and the password on the same line makes it easier
337+
# to automatically extract the password (automated testing!), and the final
338+
# empty line is for better redability.
339+
append_file($secret_file,
340+
"# The random password set for the root user at $now (local time): " .
341+
$escaped_password,
342+
"");
343+
print "A random root password has been set. You will find it in '$secret_file'.\n";
344+
}
345+
346+
##############################################################################
347+
#
348+
# Generate a random password
349+
#
350+
##############################################################################
351+
352+
sub generate_random_password {
353+
# Short term:
354+
# On (at least) Linux and Solaris, a "random" device is available, use it:
355+
# cat /dev/urandom | tr -dc "[:alnum:]" | fold -w 8 | head -1
356+
# Note: There is no guarantee the results will pass a validation checker
357+
# as resulted from WL#2739
358+
# http://wl.no.oracle.com/worklo/?tid=2739
359+
# http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html
360+
#
361+
# Long term:
362+
# Password generated via Perl module "String::MkPasswd" available at
363+
# http://search.cpan.org/~cgrau/String-MkPasswd/bin/mkpasswd.pl
364+
# Using it got approved recently.
365+
#
366+
my $password = `cat /dev/urandom | tr -dc "[:alnum:]" | fold -w 8 | head -1`;
367+
chomp ($password);
368+
return $password;
369+
}
370+
371+
252372
##############################################################################
253373
#
254374
# Ok, let's go. We first need to parse arguments which are required by
@@ -312,6 +432,31 @@ $opt = {}; # Reset the arguments FIXME ?
312432
parse_arguments($opt, @default_options);
313433
parse_arguments($opt, 'PICK-ARGS-FROM-ARGV', @ARGV);
314434

435+
# ----------------------------------------------------------------------
436+
# Create a random password for root, if requested and implemented
437+
# ----------------------------------------------------------------------
438+
439+
if ( $opt->{'random-passwords'} ) {
440+
# Add other non-working OS like this: $^O =~ m/^(solaris|linux|freebsd|darwin)$/
441+
# Issue 1: random password creation
442+
# Issue 2: confidential file
443+
if ( $^O =~ m/^(MSWin32)$/ ) {
444+
print "Random password not yet implemented for $^O - option will be ignored\n";
445+
delete $opt->{'random-passwords'};
446+
} else {
447+
ensure_secret_file();
448+
my $password = generate_random_password();
449+
if ( $password ) {
450+
# "true" means "string is non-empty"
451+
$escaped_password = basic_single_escape($password);
452+
} else {
453+
# Whatever the reason (missing "/dev/urandom"), an empty password is bad
454+
print "Could not generate a random password - not setting one\n";
455+
delete $opt->{'random-passwords'};
456+
}
457+
}
458+
}
459+
315460
# ----------------------------------------------------------------------
316461
# Configure paths to support files
317462
# ----------------------------------------------------------------------
@@ -368,8 +513,9 @@ if ( $opt->{srcdir} )
368513
my $fill_help_tables = "$pkgdatadir/fill_help_tables.sql";
369514
my $create_system_tables = "$pkgdatadir/mysql_system_tables.sql";
370515
my $fill_system_tables = "$pkgdatadir/mysql_system_tables_data.sql";
516+
my $security_commands = "$pkgdatadir/mysql_security_commands.sql";
371517

372-
foreach my $f ( $fill_help_tables,$create_system_tables,$fill_system_tables )
518+
foreach my $f ( $fill_help_tables, $create_system_tables, $fill_system_tables, $security_commands )
373519
{
374520
-f $f or cannot_find_file($f);
375521
}
@@ -483,6 +629,21 @@ if ( open(PIPE, "| $mysqld_install_cmd_line") )
483629

484630
print PIPE $_;
485631
}
632+
633+
if ( $opt->{'random-passwords'} )
634+
{
635+
open(SQL3, $security_commands)
636+
or error($opt,"can't open $security_commands for reading: $!");
637+
while ( <SQL3> )
638+
{
639+
# using the implicit variable $_ !
640+
s/ABC123xyz/$escaped_password/e ; # Replace placeholder by random password
641+
print PIPE $_;
642+
}
643+
close SQL3;
644+
tell_root_password();
645+
}
646+
486647
close PIPE;
487648
close SQL;
488649
close SQL2;
@@ -525,22 +686,43 @@ if ( open(PIPE, "| $mysqld_install_cmd_line") )
525686
# set a password after installing the data files on the real host system.
526687
# At this point, there is no end user, so it does not make sense to print
527688
# this reminder.
528-
report($opt,
529-
"PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !",
530-
"To do so, start the server, then issue the following commands:",
531-
"",
532-
" $bindir/mysqladmin -u root password 'new-password'",
533-
" $bindir/mysqladmin -u root -h $hostname password 'new-password'",
534-
"",
535-
"Alternatively you can run:",
536-
"",
537-
" $bindir/mysql_secure_installation",
538-
"",
539-
"which will also give you the option of removing the test",
540-
"databases and anonymous user created by default. This is",
541-
"strongly recommended for production servers.",
542-
"",
543-
"See the manual for more instructions.");
689+
if ( $opt->{'random-passwords'} ) {
690+
report($opt,
691+
"A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !",
692+
"You will find that password in '$secret_file'.",
693+
"",
694+
"You must change that password on your first connect,",
695+
"no other statement but 'SET PASSWORD' will be accepted.",
696+
"See the manual for the semantics of the 'password expired' flag.",
697+
"",
698+
"Also, the account for the anonymous user has been removed.",
699+
"",
700+
"In addition, you can run:",
701+
"",
702+
" $bindir/mysql_secure_installation",
703+
"",
704+
"which will also give you the option of removing the test database.",
705+
"This is strongly recommended for production servers.",
706+
"",
707+
"See the manual for more instructions.");
708+
} else {
709+
report($opt,
710+
"PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !",
711+
"To do so, start the server, then issue the following commands:",
712+
"",
713+
" $bindir/mysqladmin -u root password 'new-password'",
714+
" $bindir/mysqladmin -u root -h $hostname password 'new-password'",
715+
"",
716+
"Alternatively you can run:",
717+
"",
718+
" $bindir/mysql_secure_installation",
719+
"",
720+
"which will also give you the option of removing the test",
721+
"databases and anonymous user created by default. This is",
722+
"strongly recommended for production servers.",
723+
"",
724+
"See the manual for more instructions.");
725+
}
544726

545727
if ( !$opt->{rpm} )
546728
{

‎scripts/mysql_security_commands.sql‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
-- Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
2+
--
3+
-- This program is free software; you can redistribute it and/or modify
4+
-- it under the terms of the GNU General Public License as published by
5+
-- the Free Software Foundation; version 2 of the License.
6+
--
7+
-- This program is distributed in the hope that it will be useful,
8+
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
9+
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10+
-- GNU General Public License for more details.
11+
--
12+
-- You should have received a copy of the GNU General Public License
13+
-- along with this program; if not, write to the Free Software
14+
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
15+
16+
# This set of commands will modify the predefined accounts of a MySQL installation
17+
# to increase security.
18+
19+
# 1) Set passwords for the root account.
20+
# Note that the password 'ABC123xyz' will be replaced by a random string
21+
# when these commands are transferred to the server.
22+
SET @@old_passwords=1;
23+
UPDATE mysql.user SET Password=PASSWORD('ABC123xyz') WHERE User='root' and plugin='mysql_old_password';
24+
SET @@old_passwords=0;
25+
UPDATE mysql.user SET Password=PASSWORD('ABC123xyz') WHERE User='root' and plugin in ('', 'mysql_native_password');
26+
SET @@old_passwords=2;
27+
UPDATE mysql.user SET authentication_string=PASSWORD('ABC123xyz') WHERE User='root' and plugin='sha256_password';
28+
29+
# 2) Drop the anonymous account.
30+
DELETE FROM mysql.user WHERE User='';
31+
32+
# 3) Force the root user to change the password on first connect.
33+
UPDATE mysql.user SET Password_expired='Y' WHERE User='root';
34+
35+
# In case this file is sent to a running server.
36+
FLUSH PRIVILEGES;

0 commit comments

Comments
 (0)