Spring Security 2.0.4 Authentication SQL (UsersByUsernameQuery override)

[evan_goff]

There are several postings with questions regarding how to override the default DEF_USERS_BY_USERNAME_QUERY and related authentication SQL, which are in the class org.springframework.security.userdetails.jdbc.Jdbc DaoImpl along with the associated MappingSqlQuery objects (which are private in JdbcDaoImpl.) There are concerns about excessive duplication of code in the JdbcDaoImpl class, specifically avoiding the need to duplicate the load...() methods -- there must be a better way of doing this.

What is the recommended way to override the default authentication SQL in Spring Security 2.0.4?

[Luke Taylor]

From the Javadoc for JdbcDaoImpl:

"If you are using an existing schema you will have to set the queries usersByUsernameQuery and authoritiesByUsernameQuery to match your database setup"

These allow you to set the SQL for your schema.

[evan_goff]

Thanks for your reply, Luke -- seems like overriding the db queries should be clean and simple, but it's kludgy to have to recreate the loadUserByUsername() method from JdbcDaoImpl... this approach has been discussed in other discussion threads, but is not very clean.

What is the best way of "using an existing schema" with Spring Security to facilitate authentication, while minimizing redundant code? How can the authentication SQL statement be extracted from the code and put into the applicationContext or another external location?

Thanks for your assistance!

[Luke Taylor]

I'm sorry, but I don't really understand the question. You can inject the SQL queries as strings using the properties I just mentioned. The SQL must return result set data that matches the default schema queries:

http://static.springframework.org/sp...oImpl.html#106

If your requirements are substantially different then you should implement UserDetailsService directly.

[evan_goff]

Injecting the authentication SQL sounds like it should be simple using Spring Security, but how can I do it cleanly?

In Spring Security the MappingSqlQuery objects (UsersByUsernameMapping, AuthoritiesByUsernameMapping, etc.) are now constructed with default SQL and are private within org.springframework.security.userdetails.jdbc.Jdbc DaoImpl.

In previous version of ACEGI, I implemented my own MappingSqlQuery objects in the class that extended JdbcDaoImpl, which was a fairly clean means of providing specific SQL as required by my db schema.

How can I do that now with Spring Security (without duplicating lots of code from JdbcDaoImpl or recreating UserDetails unnecessarily)?

[Luke Taylor]

What do you mean by "cleanly"? Why is there a problem with using the properties I've pointed out to you? e.g.

setUsersByUsernameQuery("select username,password,enabled from users where username = ?");

[evan_goff]

The problem is that once you have customized the SQL for authentication, you do not have access to an appropriate corresponding MappingSqlQuery object to be used to create the UserDetails object you need.

Specifically, the UsersByUsernameMapping is a private class extending MappingSqlQuery within the JdbcDaoImpl class and is shown here:

/**
     * Query object to look up a user.
     */
    private class UsersByUsernameMapping extends MappingSqlQuery {
        protected UsersByUsernameMapping(DataSource ds) {
            super(ds, usersByUsernameQuery);
            declareParameter(new SqlParameter(Types.VARCHAR));
            compile();
        }

        protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
            String username = rs.getString(1);
            String password = rs.getString(2);
            boolean enabled = rs.getBoolean(3);
            UserDetails user = new User(username, password, enabled, true, true, true,
                    new GrantedAuthority[] {new GrantedAuthorityImpl("HOLDER")});

            return user;
        }
    }

Note that the fields being extracted from the ResultSet in the mapRow() method above are not going to match the fields in the customized SQL to reflect a different db schema.

How can I set the MappingSqlQuery object that correctly maps the ResultSet returned from the customized authentication SQL? Overriding the following method from JdbcDaoImpl might work:

/** 
     * Executes the <tt>usersByUsernameQuery</tt> and returns a list of UserDetails objects (there should normally 
     * only be one matching user). 
     */
    protected List loadUsersByUsername(String username) {
        return usersByUsernameMapping.execute(username);
    }

...but this is not really what I would consider a clean solution -- I'd rather have the option of injecting my own MappingSqlQuery object and let the framework execute the appropriate method as required by the authentication process.

What do you suggest?

[Luke Taylor]

The intention was to refactor the query objects and replace them with direct use of JdbcTemplate which is simpler (and which I've now done - thanks for the reminder :-) ). I don't really see how adding custom query objects is any cleaner than overriding a method which calls them. The former forces a particular implementation approach (use of MappingSqlQuery) on the implementation whereas a method only specifies the contract. You can override it to use a MappingSqlQuery if you choose but you don't have to.

There's also a method for the creation of the final UserDetails, so you use that as customization point if you want.