Blocking user accounts

A long time missing feature in MySQL is temporarily blocking accounts: denying a user to log in, without affecting any other of her privileges.

There is no such privilege as ‘LOGIN’ in the grants table, as the ability to log in is the most basic one MySQL allows. This basic privilege is called USAGE.

I’ll present a hack around this, one which oak-block-account implements. Before presenting the hack, lets lay down some requirements:

• A user can be blocked from logging in to MySQL.
• Such a blocked user can later be ‘released’, re-enabling him to log in.
• It should be possible to determine if a certain user is currently blocked or not.

A first attempt to answer the above requirements is to change the account’s password. As a naive example, we can set an account’s password to ‘aaaaaaaaa’. But let’s consider the following:

• Will the user be unable to find, by some algorithm or by brute force, the new password?
• How can we revert the new password to the original one?

Time to look at how MySQL stores passwords, then.

We begin by distinguishing old_passwords (variable old_passwords=1) from new passwords.

• Old passwords are 16 characters long. These are hexadecimal characters.
• New passwords are 41 characters long: a leading ‘*’ followed by 40 hexadecimal characters.

Blocking with ‘new passwords’

To disable an account using a ‘new password’, the trick is simply to reverse the password in the mysql.user table. So if my password was ‘123456’ (strong one, isn’t it?), then mysql.user will have:

mysql> select PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+

To encode, we do:

SET PASSWORD FOR 'some_user'@'some_host' = '9DA2AC2DE76CD7ADD8654EE50192347BE7384BB6*'

Let’s consider the implication of what we just did:

• The new password is valid, as far as MySQL is concerned. No questions asked.
• The user cannot log in with his old password.
• Nor can the user log in with any other password, since the PASSWORD() function will never return a password ending with ‘*’.
• It is easy to see that the user is ‘blocked’: his password ends with ‘*’.
• It is easy to restore the original password: we simply reverse the text and call SET PASSWORD again.

Blocking with ‘old passwords’

This part really assumes you’re using MySQL 4.1 or above. If you’re one of those ‘few’ lucky people, but are unfortunately using old_passwords, here’s the deal:

Reversing an old password won’t do, since:

• The reverse may still consist of an encoding for some password
• It’s impossible to tell if a user is blocked or not.

MySQL will only allow 16 or 41 character long passwords (anyway that’s my finding). So to encode a 16 characters long password, we pad it with 25 (= 41-16) ‘~’ characters. Thus, the encoded password ‘abcdef0123456789’ turns to ‘~~~~~~~~~~~~~~~~~~~~~~~~~abcdef0123456789’. Again, note the following:

• The new password is accepted by MySQL as valid.
• The user cannot log in with his old password.
• Nor can the user log in with any other password, since the PASSWORD() function will never return a password starting with ‘~’.
• It is easy to see that the user is ‘blocked’: his password starts with 25 ‘~’ characters.
• It is easy to restore the original password: we simply remove the leading ‘~’ characters and call SET PASSWORD again.

oak-block-account

No need to work all this by hand. oak-block-account is a utility which does exactly that. It can block, release, and even kill accounts. It will automatically detect if the password is ‘old’ or ‘new’, or if the account is already blocked or not.

Other possibilities

RENAME USER is another trick which could be used: we could take a user’s login (e.g. ‘webuser’) and rename it to an unknown value, like ‘webuser__1q98d4f’. While it serves the same purpose – of blocking the user, it has a disadvantage: if by luck or hack the new login is discovered, it could still be used to access the database. The change of password solution ensures there is no user/password combination which will work on the blocked account.

Other options may involve removing the account from mysql.user, to put it elsewhere, from where to restore the row when the time comes. I prefer a solution which works on the mysql schema itself.