REVERT TO CONSOLE

Adding a database back end to Spring Security seems deceptively simple. And to be fair, there are several ways to do it. The way I had in mind looked like a shortcut.

In my application there is basically one table for all users. It contains the values I need for username, password, role, and the active flag that Spring Security supports. No, it wouldn’t be my first choice of table setup, but that’s what I have to work with.

I read through Chris Baker’s fine article in the Java DZONE and the way he did it was to override two values in the jdbc-user-service, which replaces the user-service authentication provider. He used:

<authentication-provider>
	<jdbc-user-service data-source-ref="dataSource"
	users-by-username-query="SELECT U.username, U.password, U.accountEnabled AS 'enabled' FROM User U where U.username=?"
	authorities-by-username-query="SELECT U.username, R.name as 'authority' FROM User U JOIN Authority A ON u.id = A.userId JOIN Role R ON R.id = A.roleId WHERE U.username=?"/>
</authentication-provider>

I wanted to do something similar, and initially banged out:

	<authentication-provider>
	<jdbc-user-service data-source-ref="baseDataSource"
	users-by-username-query="SELECT LOGONID, PASSWORD, ACTIVE FROM EMPLOYEE WHERE LOGONID=?"
	authorities-by-username-query="SELECT LOGONID, ROLE FROM EMPLOYEE WHERE LOGONID=?"/>
	</authentication-provider>

The result was that all employees were inactive for Spring Security. Why? Because the EMPLOYEE table’s ACTIVE column is a CHAR, and Spring Security expects a BIT or BOOLEAN. After tinkering around for a bit, I decided to modify the EMPLOYEE table to use a BOOLEAN instead. The actually posed a problem for DbUnit, surprise surprise. The workaround in Ant didn’t work for me, I had a weird classnotfound error that I couldn’t track back to ant, and just gave up after a while. I didn’t really want to change the column type anyway.

In the end, I played around with HSQL for a while and came up with

	<authentication-provider>
	<jdbc-user-service data-source-ref="baseDataSource"
	users-by-username-query="SELECT LOGONID, PASSWORD, CASE WHEN UCASE(ACTIVE)='T' THEN 1 ELSE 0 END FROM EMPLOYEE WHERE LOGONID=?"
	authorities-by-username-query="SELECT LOGONID, ROLE FROM EMPLOYEE WHERE LOGONID=?"/>
	</authentication-provider>

Disclaimer: There are other ways to do this that are identified in the Spring Security reference documentation. The documentation is very good, my only wish would be that they provide the default schema, and provide a description of the default roles.