Blog

# Using mod_auth_mysql as a single sign-on tool

I recently migrated our Subversion repositories and trac sites from one server to another. This gave me the chance to reorganize our layout and authentication mechanism. I chose to replace our existing .htaccess files with mod_auth_mysql because it fit two key criteria:

• Its easy to administer – Logging into the server and running htpasswd every time I need to add a user or change a password is a PITA
• Most of the applications we use offer MySQL authentication functions, so I can grant access to several things at once.

The docs on how to set it up were fairly clear, with one exception: I would have liked to have seen an example of how to use multiple tables for group membership. After a little more Googling I ran across such an example, taken from Common database normalization techniques suggest that users and passwords go into one table, groups in another, and a third table should be used to join the two by IDs. This is what I was after, and thats what Jiras integration doc showed me. It was also confirmation that I had chosen an appropriate central authentication mechanism.

Eric was nice enough to whip up a Rails CRUD utility to manage the tables (which will be posted soon) and from there it was a simple matter to instruct Apache to use those tables whenever it needed authentication. By the way, a great way to do that is with a separate file such as “mysql-auth.inc” and just use the Apache Include directive to include that file wherever you need authentication.