Checking MySQL weak passwords

Do you have a policy of strong passwords?
Do you want to enforce it? If yes, then you may want a quick way of checking if your users are abiding to the rules.
You can either force the users to change passwords through a smart application, to prevent them from using weak passwords, or, if you are assessing an existing environment, you can check the existing passwords against a dictionary, to see if there are some imeediately exploitable passwords among your server accounts.

The mechanics of passwords checking

Checking passwords against a dictionary is a costly business. It can be made more affordable if we manage to put all the items to check in the database itself, so that we can use the DBMS force to do our job.
What we have to do is to collect a large list of words that we would not like our users to set as their password. Immediate candidates are user names, both from the operating system and the database, user full names, and of course words from as many dictionaries we believe our users could make advantage of.

WARNING Password checking is a breech of privacy. If you need to use the techniques explained in this post, be sure to:

• Inform the users about a policy of strict passwords;
• Inform them that you will run sanity checks from time to time;
• Make them sign a form to acknowledge that they know and understand the policy;
  
• If you are not the boss, get the management approval (in written!);
• Have the company lawyer review and approve the policy wording and the forms.
  

Failure to do so may result in unpleasant situations, ranging from slight embarrassment to criminal charges, depending on the company you work with, your country regulation, and your ill luck.

The table we need to create will have a word, which is what we would not want to be used as a password. A password, which is the word encoded with MySQL password function, and a reversed_password, which is the word spelled backwards encoded with password. If you have time and patience, you may add more variations (such as all lowercase, all uppercase).
Using such table, finding the weak passwords is quite easy. To have something to find, let's create some dumb users:

GRANT all on test.* to test1 identified by "test";        # name of a database
GRANT all on test.* to test2 identified by "test2";       # name of a user
GRANT all on test.* to test3 identified by "master";      # a common word
GRANT all on test.* to test4 identified by "retsam";      # a reversed common word
GRANT all on test.* to test5 identified by "marshmallow"; # an uncommon word

Now let's get them:

select
 user, word as 'chosen password', word as 'original word', 'direct' as 'password type'
from
 passwords
 inner join mysql.user on password=pwd
union
select
 user, reverse(word),word,'reverse' as 'password type'
from
 passwords
 inner join mysql.user on password=reverse_pwd
;
+-----------+-----------------+---------------+---------------+
| user      | chosen password | original word | password type |
+-----------+-----------------+---------------+---------------+
| test5     | marshmallow     | marshmallow   | direct        |
| test3     | master          | master        | direct        |
| test4     | retsam          | master        | reverse       |
| test1     | test            | test          | direct        |
| test2     | test2           | test2         | direct        |
+-----------+-----------------+---------------+---------------+

Here we are. All the offending users came to light, with a simple (and very efficient) query.

Creating the passwords table

The following perl script will create the passwords table and populate it, using a dictionary of English words and the other above mentioned elements.

#!/usr/bin/perl 
use strict;
use warnings;
use Data::Dumper;
use DBI;

my $words_file      = '/usr/share/dict/words';
my $groups_file     = '/etc/group';
my $users_file      = '/etc/passwd';
my $DEBUG           = $ENV{DEBUG};
my $deferred_keys   = $ENV{DEFERRED_KEYS}   || 1;
my $max_records     = $ENV{MAX_RECORDS}     || 30_000;
my $base_query      = qq{INSERT IGNORE INTO passwords VALUES };
my $query           = $base_query;
my $record_count    = 0;
my $total_recs      = 0;
my $database        = 'test';
my $host            = 'localhost';
my $conf_file       = "$ENV{HOME}/.my.cnf";
my $table_struct    = qq{
create table passwords (
word         char(50) collate utf8_bin NOT NULL,
pwd          char(41) collate utf8_bin NOT NULL,
reverse_pwd  char(41) collate utf8_bin NOT NULL,
PRIMARY KEY (word),
KEY         (pwd),
KEY         (reverse_pwd)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
};

my $dbh=DBI->connect("dbi:mysql:$database;host=$host"
   . ";mysql_read_default_file=$conf_file",
       undef,
       undef,
       {RaiseError => 1})
or die "Can't connect: $DBI::errstr\n";

$dbh->do(qq{drop table if exists passwords});
$dbh->do($table_struct);
$dbh->do(qq{ALTER TABLE passwords DISABLE KEYS}) if $deferred_keys;

# get the database users
my $users       = $dbh->selectall_arrayref(qq{select user from mysql.user});

# get the database names
my $databases   = $dbh->selectall_arrayref(qq{show databases});

# get the table names
my $tables      = $dbh->selectall_arrayref(qq{
select distinct table_name from information_schema.tables});

# add all table, users, database names to the word list
for ((@{$users}, @{$databases}, @{$tables})) {
process_query( $_->[0] );
}

# get the OS group names
open my $IN, q{<}, $groups_file
or die "unable to open $groups_file\n";
process_query((split(q{:}, $_))[0]) while <$IN>;
close $IN;

# get the OS user names
open $IN, q{<}, $users_file
or die "unable to open $users_file\n";
while (<$IN>) {
my ($user_id, $full_name) = (split(q{:}, $_))[0,4];
process_query($user_id);
process_query($full_name) if $full_name;
}
close $IN;

# get the words from a dictionary
# repeat this step for every other
# dictionary file you may want to include
open $IN, q{<}, $words_file
or die "unable to open $words_file\n";
process_query( $_ ) while <$IN>;
close $IN;

# finish off leftovers
if ($record_count) {
$dbh->do($query);
$total_recs += $record_count;
print "$record_count ($total_recs)\n" if $DEBUG;
}

$dbh->do(qq{ALTER TABLE passwords ENABLE KEYS}) if $deferred_keys;

sub process_query {
my ($word) = @_;
chomp $word;
if ($record_count) {
$query .= q{,};
}
my $quoted_word = $dbh->quote($word);
$query .= q{(}
  .  $quoted_word
  .  q{, password(}
  .  $quoted_word
  .  q{), password(reverse(}
  .  $quoted_word
  .  q{)))};
if (++$record_count >= $max_records)    {
$dbh->do($query);
$total_recs += $record_count;
print "$record_count ($total_recs)\n" if $DEBUG;
$record_count = 0;
$query = $base_query;
}
}

Some efficiency issues

Why do we need a Perl script to achieve such goal? Everything could be done with a few one-liners, and this is actually what I tried in the beginning.
Of course you can use some one-liners, but you will end up inserting one record at the time, which becomes unbearably slow when you pass the fifty thousand record mark.
As an example, to insert five hundred thousand records, this script takes about one minute, because it makes multiple insert statements of 30,000 records each, while inserting records one by one took about 17 minutes.
One more problem may arise if you are not careful about the column types. Your "password" and "reversed_password" columns must have exactly the same type, character set and collation of the "password" column in mysql.user table. If you don't make them match exactly, MySQL will refuse to use any index from that table when joining them. The query I showed before the script runs in less than one second. If you use a different character set, it will require a table scan, and it can take as much as one minute for a table of one million words.