SQLRealm 0.1 available

For the impatient, it is a custom realm for Tomcat (tested on 5.5.x) ,which supports arbitrart SQL queries to extract passwords and roles. Here is the jar file and here are the sources (eclipse project files included).

SQLRealm can be useful in scenarios where the default JDBCRealm or DataSourceRealms prove to be inadequate. For example, if you wanted usernames to be unique only within a department, and so have to pass the department id to uniquely identify the user, SQLRealm can help.

To use SQLRealm:

• Drop the jar in to server/lib of your tomcat installation
• Add an entry similar to what you see below in your conf\server.conf file in the relevant context:

 <Realm   className="org.motn.misc.catalina.realm.SQLRealm"
     driverName="org.postgresql.Driver"
     connectionURL="jdbc:postgresql://localhost/db"
     connectionName="user"
     connectionPassword="pwd"
     passwordSQL="SELECT PASSWORD FROM USERS WHERE USERNAME={0} and DEPTID={1}" <!-- these params are bound with tokens from the username field j_username -->
     passwordAlias="pwd"  <!-- Optional -->
     rolesSQL="SELECT NAME, 'Roles' FROM ROLES,GROUP_OF_ROLES,USERS WHERE USERNAME={0} AND USERS.GROUP_ID=GROUP_OF_ROLES.ID AND ROLES.ID=GROUP_OF_ROLES.ROLE_ID"
     roleAlias="role_name" <!-- Optional -->
     delimiter=":"
     name="MyRealm"
/>

You can see the traditional bind parameters "?" being replaced by {0}, {1} etc. These are the positions of tokens in the j_username field value. For example, if you wanted to pass username and deptid to the SQL query, your j_username field would have the value "testuser:abcdept". The tokens in the "extended username" are delimited by ":" by default. You can override it, of course.
You would probably use a little javascript in your login pages to create the extended username field. For example, select a value from a department dropdown box, and append it to the username in j_username field.

A complete example WAR file will be posted soon