continued from Do-it-yourself password management

Requiring a minimum length to the password

To control the length of the key we can add a constraint on the table’s key field.  In MS-Access this is done with the ‘validation rule’ field property, accessible in table design view.  To ensure all keys are length of 10 or greater enter “Len([key])>10” into the validation rule. In the “Validation Text” property enter “Sorry, the key length has to greater than 10 characters long.” into the field property. Then if you enter a key like “password” you should get a pop up message box telling you the length is incorrect.

Adding complexity rules

What about preventing certain characters, for example if your  applications will not accept certain characters. You can further modify the constraint. For example if you wanted to prevent adding a single quote character you can use the vba instr function. Add the following to the Validation Rule field property:

Len([key])>=10 And InStr([key],”‘”)=0

and also change the validation text to something like “Sorry, the key length has to be greater than 10 characters long or it contains a disallowed character.”

Another example: To prevent the user from adding a greater than character, do this: And InStr([key],”>”)=0

Setting a default created date

Also, we want to make sure the date created field defaults to today’s date and time. To do that in the default value of the DateCreated field property enter “Now()”. That function will add the current date and time into the field.

Indexes enforce password key and name uniqueness

Also we want to test out our unique indexes that were created in the prior post. If you try to enter a duplicate key or duplicate keyname value, you should get this response from ms-access:

Seeing this message is good because it tells us that the unique indexes will prevent two bad things from happening in the password database: 1. duplicate keys, and 2. duplicate keynames. Both conditions should not exist in this table.

One interesting thing about this table design is the fact it can keep calculate the password age as well as preventing us from using the same password again. This abilities help us to manage the passwords according to best practices: keeping us notified when a password gets too old, and preventing us from reusing a password.

However this design only allows us to keep track of a single name-password which is very simple. So if you a password entered for “APP1” you can only enter a single row. This is good if you only want to know what the current password is.

why I like ‘autonumber’ fields

However if you wanted to allow the database to keep record of the current and prior passwords, you could change the index from unique to a non-unique index.  If you did this, and the auto number is set to increment, you can still find the most recent password is the record with the highest id number (because that field is auto-numbered). You could capture the latest password by a sql group by query on the max of the id and group by the keyname.

It is worth noting that the passwords would be unique across all passwords in the database table. This is a very good business rule. It prevents the user from using the same password across many “applications” or uses.

Overall, with just a few minutes of effort we can get build an ms-access based password manager.