So how would a DBA set up a system to expire the password of interactive MySQL users? Some kind folks have been reading the last few entries of this blog and asked me to flesh out how to go from concept to production and we will work this out together (feedback please!) over the next few postings.
The first step is to find out what information we have and then determine what information do not have. We have the account name, the password, and if the password is expired. But we do not know the interval for password expiration. We might want to keep track of when the last change was made, possibly keep a list of the last N passwords to check against to discourage reuse, and maybe have a black list of words not to use as passwords.
In the mysql.user table we find the account name, the password, and if the password is expired or not. But we need some new tables to hold the data we do not have yet. I have seen some customers customize their instances by adding this sort of data into their mysql database. Logically it is a great place to keep the information but you kill yourself keeping these new tables and fields updated at upgrade time. You never know what conflicts you will ran into in the future. So we will grab what we can from the mysql database and start another database.
CREATE DATABASE password_meta;
CREATE TABLE meta (password_lifespan SMALLINT UNSIGNED NOT NULL DEFAULT 90);
So we now have a table for meta data, created an entry for the life span of an password (password_lifespan), and set up a default value of 90. We can add other columns for information about any password work we need to store later.
Next we will need a list of users. More specifically a list of users who have passwords that have to be changed. Do not have application passwords included as your applications are most likely not going to know how to change a password (but is a good idea to change ALL passwords on a regular basis). So where we end up with a table that will have the user or account name that we will grab from mysql.user and will be a primary key. As we create this from scratch, we can simply copy this data from the user table by hand, if there are a few users, or utilize some sort of script. Once we get this into a production ready mode, we will need a way to make sure new interactive users get added to this new table via some sorted scripted event, stored procedure, or a TRIGGER to take the burden off the DBA to add new accounts to the new table(1).
So we have a list of users who have password that expire. Obviously we will need some sort of date that records when the password was set? Or do we calculate 90 days from the day the account was established? And yes, you could store both. My preference is to keep the last change and calculate the expiration date.
CREATE TABLE user (User CHAR(16) COLLATE utf8_bin NOT NULL DERFAULT ''.
PRIMARY KEY (User));
Do we need to keep track of old passwords? We will need a table that keeps track of the username, old password (hashed), and lets keep a date. Later we can check for the last N records of old passwords sorted by most recent date to cut down on password reuse
CREATE TABLE old_password (User char(16) COLLAGE utf8_bin NOT NULL,
Password char(11) CHARACTER SET Latin1 COLLATE latin1_bin NOT NULL,
PRIMARY KEY (User));
You probably noticed the collation information in the last two tables. These were copied from the mysql.user table on the instance being used. You probably should be storing data in UTF8 as a general principal. Someday all the old Latin1 qualifiers will have to make room for the richer character sets. And when Latin1 is no longer a standard, the above code will need to be changed to reflect these changes.
But what if you are using a password plug-in that stores more than 11 characters worth of data? Well, you will have to modify the old_password table to match your environment.
So we now have a very flimsy set of armatures to hang some data from and next time we will start adding some data and some rough code as our next step.
- The idea is to work smarter not harder and adding an extra step to your account creation process is usually not the way to work smarter.