Using P4toDB to check user password strength.

Feb 24, 2010

P4toDB exposes some interesting data that was not previously available. Two such tidbits are the strength of user passwords and the expiration date of logins. This post will show some simple queries to extract the information from the `user` table using the MySQL command-line.

<EDIT> As a point of clarification, the password strength data as discussed here is only available via a Perforce server super-user account. See Access Levels Required by Perforce Commands for some more context on the different permission levels. P4toDB requires a super-user account on the Perforce server, but stores the replicated data on the SQL server, so be mindful of the data you replicate and the users who have access to it via SQL permissions.</EDIT>

The source Perforce server instance in this example has four users: Alice with no password, Bob and Zoe with weak passwords and Carlos with a strong one. Zoe is in a group with a non-default login expiry time. Note that you can enforce strong passwords by having your server at security level 2.

First I'll talk about password strength. Here's a query to list the users without a password set:

mysql> SELECT `user`, `email` FROM `user` WHERE `password` = '' ;
+---------+------------------+
| user    | email            |
+---------+------------------+
| Alice   | Alice@hostname   |
+---------+------------------+

Now one for those with weak passwords:

mysql> SELECT `user`, `email` FROM `user` WHERE `password` <> '' AND `strength` = 0;
+---------+----------------+
| user    | email          |
+---------+----------------+
| Bob     | Bob@hostname   |
| Zoe     | Zoe@hostname   |
+---------+----------------+

A strong password is defined as having:

... at least eight characters long, and at least two of the following are true:
- contains uppercase letters.
- contains lowercase letters.
- contains non-alphabetic characters.

And, can be queried as such:

mysql> SELECT `user`, `email` FROM `user` WHERE `strength` = 1;
+--------+-----------------+
| user   | email           |
+--------+-----------------+
| Carlos | Carlos@hostname |
+--------+-----------------+

Perhaps finding out what your user-base has for password strength will be useful to you. Also, if you take the `user` data along with a technique like I illustrated in versioning 'p4 counters', you could implement a password hash history or aging mechanism. Typically people use External Authentication Triggers for that task, though.

And now to continue on with the second part of this post - when a user logs in, they're sent a time-limited ticket granting access to the server. The expiry date for the ticket is also stored in the database under the `user` table, so we'll follow on with the same data as used above.

Today is the 17th at ~4PM. The following query shows who is logged in. The 1969 dates are an epoch date of zero, meaning no login. The default login time is 12 hours, as we can see in Bob's login. Zoe's is set for 86,400 seconds, or 24 hours.

mysql> SELECT `user`, `enddate` FROM `user` ORDER BY `enddate` DESC;
+---------+---------------------+
| user    | enddate             |
+---------+---------------------+
| Zoe     | 2010-02-18 16:08:56 |
| Bob     | 2010-02-18 04:09:03 |
| Alice   | 1969-12-31 16:00:00 |
| Carlos  | 1969-12-31 16:00:00 |
+---------+---------------------+

The reason Zoe has a longer ticket life is because she's in group that changes the default value. That variable is stored in the `timeout` column from the `group` table. Note that if a user is a member of multiple groups, that there are precedence rules that come into play.

mysql> SELECT * FROM `group`;
+------+-----------+------+------+------+------+---------+
| user | group     | type | maxr | maxs | maxl | timeout |
+------+-----------+------+------+------+------+---------+
| Zoe  | Zoe_group |    1 |    0 |    0 |    0 |   86400 |
+------+-----------+------+------+------+------+---------+

To show active logins, just filter-out anything in the past. If the SQL server's clock doesn't match the Perforce server's, the answer will be off.

mysql> SELECT `user`, `enddate` FROM `user` WHERE `enddate` > NOW() ORDER BY `enddate` DESC;
+---------+---------------------+
| user    | enddate             |
+---------+---------------------+
| Zoe     | 2010-02-18 16:08:56 |
| Bob     | 2010-02-18 04:09:03 |
+---------+---------------------+

There are lots of general-purpose references for SQL queries out there, but here's a nice one that I just came across. The examples gave me some good ideas. One was to create a new table that would contain periodic samplings of `user`'s `enddate` column for logged-in users. Using this data you could perform a moving average of the length of current logins.

jgibson's picture
About the author:
Jason Gibson is a Software Engineer at Perforce Software.
See all posts by