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

Tomcat SQLRealm

I was working on a J2EE project, deployed on Tomcat. We were using the tomcat realms for authentication and authorization. We ran in to a problem : We needed to pass an additional parameter from the login page to the receiving controller (Spring MVC). After sifting through innumerable tomcat mailing lists ( this , this and this).

Yet another problem was that our tables that had users and roles were linked via another table called groups. And Tomcat does not have a realm implementation to execute arbitrary SQL queries.

It is straightforward to write a realm implementation in tomcat. Extend org.apache.catalina.realm.RealmBase, and implement getPrincipal(), and getName(). And I wrote one - SQLRealm. It does not solve the first problem of passing additional parameters, but solves the second one. Currently it does the following:

• Run aribtrary SQL query to extract password.
• Run aribtrary SQL query to extract roles.
• Accept multiple bind parameters for either query (The params are extracted from the username, each value separated by a configurable delimiter character).
• Uses plain JDBC.

The future enhancements will be to :

• Make the additional parameters available in the Principal object.
• Flexibility to use datasources.
  

I will be hosting the jar and the source, with a usage document in a couple of days.

The Interview

Last friday, my wife got an interview call from a large multinational software services company in bangalore.My wife has just passed out of engineering.The scheduled time was 8:30 AM. So we go there at 8: 25.We, along with two hundred other aspirants are made to wait "outside" the campus for about an hour. So much for the schedule.Finally, my wife gets a chance to go in. She (along with others) climbs up four stories,(No..you cannot take the elevator) .

An hour later, she takes an hour long test. By now, it is 12:00PM. The recruitment team tells that the result will be announced within an hour, and people will have to wait. "Can I get Coffee?" - "No";"I haven't had my breakfast.can I go out and eat something?" - "No you cannot leave the premises";"I do not want the job,can I go now?"-"No you cannot, until the entire process is completed"! What!? am I under house arrest or something? Apparently yes. People are huddled on the terrace, in the scorcthing sun for FOUR hours. Yes, that is right. Four hours in the sun, without food.

There was a barking recruitment guy, who apparently scowled "I too have been without food from morning.why are you complaining?". I have not heard a more ridiculous comment than this in ages.Once the results are announced, the next rounds are hurried through and it is 7:30PM when she completes all rounds and awaiting results.Again,"The results will be announced soon". And it took almost three hours to tell the result. Repeated requests for going out for food fell on deaf ears.

She got an offer eventually, at 10:15 PM. After 14 hours of standing in the sun, waiting, waiting and waiting and hearing scowls. The moment she was out of the campus, my wife decided not to accept the offer. While I was waiting for her, I talked to one of the employees there, and found out that their answer-sheet-correcting -machines got messed up. All of them? At the same time? Wonderful. The employee was understanding,patient and said that they screwed up.But that does not take away so many hours of irritation,does it?

Oh, by the way, did I tell you the name of the company? It was LogicaCMG.