Locking USER Accounts in MySQL

Locking User Accounts in MySQL

For SOX Compliance when an employee, contractor, or vendor leaves your company their account needs to be locked, expired, or removed.  Some of these people may return, and their Account may have many complicated permissions.  In these cases, it would be better to “Lock” their Account while they are no longer doing business with your company. 

However, MySQL does not have the ability to “Lock” or “Expire” a User Account.   The following is a simple procedure to “Lock” and “Unlock” a MySQL Account.

Typically, the one-way encrypted hash value of a users password is stored in a password field  in an user account table.  MySQL supports three builtin one-way hashed functions, PASSWORD(), MD5() and SHA1().  It is recommended not to directly use the PASSWORD() function. Reason, is that prior to PHP 4.1 version, the password function produced a 16 byte value, and afterwards a 41 byte value consisting of a '*' followed by the SHA1 40 byte hashed value. Thus, the password function is not compatible between the two.

To avoid incompatibilities, it is recommended to directly use the SHA1() builtin hash function to produce the same 40 byte hashed value, and add a leading ‘*’ . Note, your password field must be at least 41 characters in length.

Alternatively, one can use the MD5() builtin hash function to produce a 32 byte hashed value. Additionally, both SHA1 and MD5 are builtin PHP functions, so the passwords can be hashed prior to submission to the database, which further reduces exposure to the plaintext value of the password.

When a user logs into an account, the password they enter is passed through the same one-way encrypted hash function, and the resulting hashed value is then compared for a match to the hashed value stored in the password field.

To lock a password in such a way that it is still recoverable, remove the leading ‘*’ in the stored hash value, which will cause subsequent attempts to match the hashed value of an entered password to fail. The password can be later unlocked by adding back the ‘*’.

To Lock the account

mysql>use mysql

mysql> select User,Password from user where User = 'username' and Host = 'localhost';

+---------+-----------+-------------------------------------------+

| User    | Password                                  |

+---------+-------------------------------------------+

| username | *D1856C4478388E9C1D82C215C573310AD7BF5BEA |

+---------+-------------------------------------------+

mysql>update user set Password = ‘D1856C4478388E9C1D82C215C573310AD7BF5BEA’ where User = ‘username’ and Host = ‘localhost’;

mysql>flush privileges;

To restore the Account, do the following:

mysql>use mysql

mysql> select User,Password from user where User = 'username' and Host = 'localhost';

+---------+-----------+-------------------------------------------+

| User    | Password                                  |

+---------+-------------------------------------------+

| username | D1856C4478388E9C1D82C215C573310AD7BF5BEA |

+---------+-------------------------------------------+

mysql>update user set Password = ‘*D1856C4478388E9C1D82C215C573310AD7BF5BEA’ where User = ‘username’ and Host = ‘localhost’;

mysql>flush privileges;

Locating Locked Accounts

Locked accounts can be located by searching the password field for values not starting with the asterisk character ‘*’ used to unlock the account.

mysql>use mysql

mysql> select User,Password from user where User NOT LIKE '*%' and Host = 'localhost';

---------+-----------+-------------------------------------------+

| User    | Password                                  |

+---------+-------------------------------------------+

| username | D1856C4478388E9C1D82C215C573310AD7BF5BEA |

+---------+-------------------------------------------+

These procedures will save the DBA’s time and ensure the returning User to have the same privileges they had previously.