Data seem sometimes to have their own life and will, and they refuse to behave as we wish.
Then, you need a firm hand to tame the wild data and turn them into quiet and obeying pets.

Sunday, January 08, 2006

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:

GRANTallon test.*to test1 identifiedby"test";# name of a databaseGRANTallon test.*to test2 identifiedby"test2";# name of a userGRANTallon test.*to test3 identifiedby"master";# a common wordGRANTallon test.*to test4 identifiedby"retsam";# a reversed common wordGRANTallon test.*to test5 identifiedby"marshmallow";# an uncommon word

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

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.

Randal,I know very well about placeholders, as you may see in my PerlMonks posts.Please, check the code.I am building a multiple record INSERT statement. 30,000 records * 3 columns. Would you use 90,000 placeholders or just 30,000 calls to $dbh->quote?