In a previous post, I noted that the new new password verification plugin in MySQL 5.6 provides a basis for implementing a more comprehensive password policy. Most notably, password policies include requirements around password strength, duration, and reuse. While the password validation plugin focuses on password strength policy components, there are ways to roll your own processes in support of password expiration and reuse policy components. Unlike the password verification plugin, the tools I will describe below don’t hook directly into account maintenance commands.
You can download the full .SQL file (in a .ZIP package) here, and I’ll walk through the various tables, procedures and events used to implement a password policy which checks password expiration and reuse below.
The mysql.user system table houses the user information, including the password hash – but it doesn’t include information that is necessary for tracking password expiration or any history that could be used to ensure adequate password rotation. To implement this, we need to create a password history table:
CREATE TABLE mysql.user_account_history ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, host CHAR(60) COLLATE utf8_bin NOT NULL DEFAULT '', user CHAR(16) COLLATE utf8_bin NOT NULL DEFAULT '', password CHAR(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY (host, user, created_date DESC) );
I’m using the mysql system database for this. You might decide this isn’t where you want to store this information, but wherever you put it, make sure it has very restricted access.
The mysql.user table has a primary key on user, host – so we know that every row in mysql.user will have a unique combination of user and host values. That’s not true for our history table, though, because we want to insert multiple rows per user account so that we can track the history of password changes. So we create a primary key column “id” to uniquely identify each history entry. The plan is to add a row for each observed password change.
We’ll create a second table to track the policy violations found:
CREATE TABLE mysql.user_password_policy_violation ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, history_id BIGINT UNSIGNED NOT NULL, host CHAR(60) COLLATE utf8_bin NOT NULL DEFAULT '', user CHAR(16) COLLATE utf8_bin NOT NULL DEFAULT '', violation_type INT NOT NULL, violation_message VARCHAR(255), violation_reported_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY (history_id, violation_type) );
Like the user_account_history table, we’re creating a primary key named “id”. This isn’t mapped to the “id” column of the user_account_history table, though – that’s found in the “history_id” column. Because any password change may violate multiple policy rules, we need a unique primary key for the violations found.
The “violation_type” column will contain numeric codes that help differentiate between the policy aspects violated, so that one violation does not preclude checking other policy aspects. The “violation_message” column is the more useful, plain-text explanation for the violation found.
Any rows in this table represent violations of the password policy. Unlike the password verification plugin in 5.6, these additional checks cannot be done at the time the password is changed – we’re only auditing after the fact, and reporting on violations. One might be tempted to implement these policies as triggers on the mysql.user table with a goal of enforcing the policy as password changes are executed, but alas, you cannot create triggers on system tables:
mysql> CREATE TRIGGER mysql.t_fail -> BEFORE UPDATE ON mysql.user -> FOR EACH ROW SET @a=1; ERROR 1465 (HY000): Triggers can not be created on system tables
To identify changes, track password history and record violations, we have a collection of stored procedures. The starting point is the check_password_policy procedure:
CREATE DEFINER = root@localhost
PROCEDURE mysql.check_password_policy()
BEGIN
DECLARE pwd_expiration_days INT DEFAULT 90;
DECLARE pwd_reuse_threshold INT DEFAULT 10;
DECLARE done, new_id INT DEFAULT FALSE;
DECLARE l_user, l_host, l_password VARCHAR(100);
DECLARE cur CURSOR FOR
SELECT user, host, password
FROM mysql.user
WHERE plugin = ''
OR plugin = 'mysql_native_password';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO l_user, l_host, l_password;
IF done THEN
LEAVE read_loop;
END IF;
SET new_id = FALSE;
CALL mysql.record_password_history( l_user, l_host, l_password, new_id);
IF new_id THEN
IF l_password = '' THEN
INSERT INTO mysql.user_password_policy_violation
(history_id, user, host, violation_type, violation_message)
VALUES
(new_id, l_user, l_host, 1, CONCAT(l_user, '@', l_host, ' has no password.'));
ELSE
CALL mysql.check_password_reuse( new_id, l_user, l_host, l_password, pwd_reuse_threshold );
END IF;
ELSE
CALL mysql.check_password_expiration( l_user, l_host, l_password, pwd_expiration_days );
END IF;
END LOOP;
CLOSE cur;
END
This stored procedure loops through the accounts in mysql.user that use the current password authentication. If you have users that still have pre-4.1 format passwords that you need to audit, you should include rows where plugin = ‘mysql_old_password’. The procedure ignores accounts configured to use other authentication methods, such as PAM or Windows native authentication.
As it loops through the user list from mysql.user, it calls a second stored procedure – record_password_history – to check whether the current user, host, password combination is recorded in the history table, and if not, insert it. Here’s what that stored procedure looks like:
CREATE DEFINER = root@localhost PROCEDURE mysql.record_password_history( IN l_user VARCHAR(60), IN l_host VARCHAR(60), IN l_pass VARCHAR(41), INOUT new_id INT) BEGIN DECLARE done, found INT DEFAULT FALSE; DECLARE l_password VARCHAR(41); DECLARE cur CURSOR FOR SELECT password FROM mysql.user_account_history WHERE user = l_user AND host = l_host ORDER BY created_date DESC LIMIT 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO l_password; IF done THEN LEAVE read_loop; END IF; IF l_password = l_pass THEN SET found = TRUE; END IF; END LOOP; CLOSE cur; IF NOT found THEN INSERT INTO mysql.user_account_history (user, host, password) VALUES (l_user, l_host, l_pass); SET new_id = LAST_INSERT_ID(); END IF; END
This is pretty simple – it’s checking the most recent entry in user_account_history to see whether the password hash matches the current password hash found in mysql.proc (and passed into the procedure as an argument). If it matches, nothing happens – but if it doesn’t, we insert a new row and return the “id” column value in the new_id INOUT parameter.
Back to the original stored procedure, here’s what happens after the call to record_password_history:
CALL mysql.record_password_history( l_user, l_host, l_password, new_id);
IF new_id THEN
IF l_password = '' THEN
INSERT INTO mysql.user_password_policy_violation
(history_id, user, host, violation_type, violation_message)
VALUES
(new_id, l_user, l_host, 1, CONCAT(l_user, '@', l_host, ' has no password.'));
The first password policy checked is that you have to have a password. If you don’t, the violation is recorded. This is simple enough that it’s in the main procedure itself, rather than a call to a separate stored procedure. If there is a password for a newly-recorded password history row, though, we want to check the hash against earlier history:
ELSE CALL mysql.check_password_reuse( new_id, l_user, l_host, l_password, pwd_reuse_threshold );
Note that this is only called when a password change (or new user account) is observed, and there’s a non-blank password. Here’s that stored procedure:
CREATE DEFINER = root@localhost
PROCEDURE mysql.check_password_reuse(
IN new_id BIGINT UNSIGNED,
IN l_user VARCHAR(60),
IN l_host VARCHAR(60),
IN l_pass VARCHAR(41),
IN reuse_check_threshold INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE l_password VARCHAR(41);
DECLARE l_date DATE;
DECLARE first_record INT DEFAULT TRUE;
DECLARE cur CURSOR FOR
SELECT password, DATE(created_date)
FROM mysql.user_account_history
WHERE user = l_user
AND host = l_host
ORDER BY created_date DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO l_password, l_date;
IF done THEN
LEAVE read_loop;
END IF;
IF reuse_check_threshold > 0 THEN
SET reuse_check_threshold = reuse_check_threshold - 1;
END IF;
IF (NOT first_record) AND l_password = l_pass THEN
SET done = TRUE;
INSERT INTO mysql.user_password_policy_violation
(history_id, user, host, violation_type, violation_message)
VALUES
(new_id, l_user, l_host, 3, CONCAT(l_user, '@', l_host, ' reused password from ', l_date));
END IF;
SET first_record = FALSE;
IF reuse_check_threshold = 0 THEN
SET done = TRUE;
END IF;
END LOOP;
CLOSE cur;
END
The query in the cursor iterates through the password history in reverse order. It explicitly ignores the first entry, because that entry will reflect the current password and match. We limit the password history check to a maximum defined by the reuse_check_threshold IN parameter. You can adjust this behavior by changing the following statement in the main check_password_policy procedure:
DECLARE pwd_reuse_threshold INT DEFAULT 10;
Pretty straight-forward stuff.
Back to the main procedure, we exit the IF condition for when a new row is found, and enter the ELSE:
IF new_id THEN ... ELSE CALL mysql.check_password_expiration( l_user, l_host, l_password, pwd_expiration_days ); END IF;
We don’t bother checking the password expiration if we just recorded a new password history row, which is why this is found in the ELSE clause. The stored procedure called also takes a configuration option in pwd_expiration_days, which is declared earlier in the main procedure and controls how often a password needs to be changed. It’s important to note that this is based entirely on the history data captured above – a user account may have had the same password for 5 years, but it will only start “counting” once the history data is recorded. Here’s the stored procedure which does the work:
CREATE DEFINER = root@localhost
PROCEDURE mysql.check_password_expiration(
IN l_user VARCHAR(60),
IN l_host VARCHAR(60),
IN l_pass VARCHAR(41),
IN days_threshold INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE l_password VARCHAR(41);
DECLARE l_days INT;
DECLARE new_id BIGINT UNSIGNED;
DECLARE reminder_days_snooze INT DEFAULT 10;
DECLARE cur CURSOR FOR
SELECT h.id, h.password, DATEDIFF(CURRENT_DATE(), DATE(h.created_date))
FROM mysql.user_account_history h
LEFT JOIN mysql.user_password_policy_violation v ON
(h.id = v.history_id AND v.violation_type = 2
AND v.violation_reported_date > NOW() - INTERVAL reminder_days_snooze DAY)
WHERE h.user = l_user
AND h.host = l_host
AND v.id IS NULL
ORDER BY h.created_date DESC LIMIT 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO new_id, l_password, l_days;
IF done THEN
LEAVE read_loop;
END IF;
IF l_password = l_pass AND l_days >= days_threshold THEN
SET done = TRUE;
INSERT INTO mysql.user_password_policy_violation
(history_id, user, host, violation_type, violation_message)
VALUES
(new_id, l_user, l_host, 2, CONCAT(l_user, '@', l_host, ' has not changed password in ', l_days, ' days.'));
END IF;
END LOOP;
CLOSE cur;
END
You may also notice that the SELECT statement has a LEFT JOIN to the user_password_policy_violation table – this allows us to report expired passwords only as frequently as we like (it may be annoying to get notices that password has expired every day, or hour, or whatever):
DECLARE cur CURSOR FOR SELECT h.id, h.password, DATEDIFF(CURRENT_DATE(), DATE(h.created_date)) FROM mysql.user_account_history h LEFT JOIN mysql.user_password_policy_violation v ON (h.id = v.history_id AND v.violation_type = 2 AND v.violation_reported_date > NOW() - INTERVAL reminder_days_snooze DAY) WHERE h.user = l_user AND h.host = l_host AND v.id IS NULL ORDER BY h.created_date DESC LIMIT 1;
We want only the most recent password, and we only want it when the snooze period on any previous violation reports has expired. We compare both the password hash and the number of days since the password change was recorded, and if they meet our criteria, the violation is recorded:
IF l_password = l_pass AND l_days >= days_threshold THEN SET done = TRUE; INSERT INTO mysql.user_password_policy_violation (history_id, user, host, violation_type, violation_message) VALUES (new_id, l_user, l_host, 2, CONCAT(l_user, '@', l_host, ' has not changed password in ', l_days, ' days.')); END IF;
That’s pretty much it for the stored procedures. Using an event, you can schedule this to happen automatically:
CREATE DEFINER = root@localhost EVENT mysql.password_policy_check_event ON SCHEDULE EVERY 1 DAY DO CALL mysql.check_password_policy();
So, let’s see how it works:
mysql> CALL mysql.check_password_policy(); Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM mysql.user_password_policy_violation\G *************************** 1. row *************************** id: 1 history_id: 1 host: localhost user: root violation_type: 1 violation_message: root@localhost has no password. violation_reported_date: 2012-09-19 11:49:57 *************************** 2. row *************************** id: 2 history_id: 2 host: 127.0.0.1 user: root violation_type: 1 violation_message: root@127.0.0.1 has no password. violation_reported_date: 2012-09-19 11:49:57 *************************** 3. row *************************** id: 3 history_id: 3 host: ::1 user: root violation_type: 1 violation_message: root@::1 has no password. violation_reported_date: 2012-09-19 11:49:57 *************************** 4. row *************************** id: 4 history_id: 4 host: localhost user: violation_type: 1 violation_message: @localhost has no password. violation_reported_date: 2012-09-19 11:49:57 4 rows in set (0.00 sec) mysql> SELECT * FROM mysql.user_account_history\G *************************** 1. row *************************** id: 1 host: localhost user: root password: created_date: 2012-09-19 11:49:57 *************************** 2. row *************************** id: 2 host: 127.0.0.1 user: root password: created_date: 2012-09-19 11:49:57 *************************** 3. row *************************** id: 3 host: ::1 user: root password: created_date: 2012-09-19 11:49:57 *************************** 4. row *************************** id: 4 host: localhost user: password: created_date: 2012-09-19 11:49:57 *************************** 5. row *************************** id: 5 host: localhost user: test password: *C150BFA211A222FD7BF10BDABD06D77507780E1B created_date: 2012-09-19 11:49:57 5 rows in set (0.00 sec)
So, it quickly identified the default accounts which have no password. Check. Let’s check to see what happens when we manipulate the user_account_history table to make it appear that a password was last changed 100 days ago:
mysql> UPDATE mysql.user_account_history -> SET created_date = NOW() - INTERVAL 100 DAY -> WHERE user = '' AND host = 'localhost'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> CALL mysql.check_password_policy(); Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM mysql.user_password_policy_violation\G *************************** 1. row *************************** ... *************************** 5. row *************************** id: 5 history_id: 4 host: localhost user: violation_type: 2 violation_message: @localhost has not changed password in 100 days. violation_reported_date: 2012-09-19 11:54:06 5 rows in set (0.00 sec)
Great. Now let’s see what happens when you reuse a password:
mysql> CREATE USER test@localhost IDENTIFIED BY '1234pWd!';
Query OK, 0 rows affected (0.00 sec)
mysql> CALL mysql.check_password_policy();
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR test@localhost = PASSWORD('2132pWd!');
Query OK, 0 rows affected (0.00 sec)
mysql> CALL mysql.check_password_policy();
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR test@localhost = PASSWORD('1234pWd!');
Query OK, 0 rows affected (0.00 sec)
mysql> CALL mysql.check_password_policy();
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM mysql.user_password_policy_violation\G
*************************** 1. row ***************************
...
*************************** 6. row ***************************
id: 6
history_id: 7
host: localhost
user: test
violation_type: 3
violation_message: test@localhost reused password from 2012-09-19
violation_reported_date: 2012-09-19 11:55:50
6 rows in set (0.00 sec)
Looks good.
So, we now have a functioning tool to report password policy violations where passwords are reused or not changed frequently enough. Coupled with the password strength plugin in MySQL 5.6, this can help manage compliance with password policies.
There are additional steps that can be taken, though. For example, once these bugs are fixed, the procedure can be altered to do more than just report policy non-compliance – it could force the user to change their password at next login, using the new ALTER USER … PASSWORD EXPIRE functionality in MySQL 5.6. In a follow-up blog, I’ll also show how we can create custom data collections and rules for MySQL Enterprise Monitor based on the policy violation information produced here.
