Craig Walls - 08/Mar/08 08:18 PM Attached patch for supporting customization of SQL through <jdbc-user-service> for user authentication, user authorization, and user group authorization (the three customizations offered through org.springframework.security.userdetails.jdbc.JdbcDaoImpl). What this patch *DOESN'T* do is to support customization of the SQL used in org.springframework.security.userdetails.jdbc.JdbcUserDetailsManager (insert, delete, and update SQL).

Luke Taylor - 04/Apr/08 03:09 PM - edited I think this is probably worth doing because jdbc-user-service won't be of much practical use unless people can customize the SQL. However, the amount of customization that will be possible is limited - loading extra columns won't be possible. There is of course nothing to prevent a user from actually using a JbdcDaoImpl bean directly or (more flexibly) a subclass of that which returns their own UserDetails object and putting the Id of that bean in their user-service-ref attribute. JbdcDaoImpl configuration is not very verbose so it's not a big deal to mix traditional bean config with namespace config here.

Luke Taylor - 13/Apr/08 03:53 PM I've added the three suggested attributes to match the query properties in JdbcDaoImpl.

Triqui Galletas - 15/Apr/08 11:22 AM Sorry, maybe I'm missing something here, but why not add userTable, userNameCol, userCredCol, userRoleTable, roleNameCol, and so on like the Realm component in the tomcat security configuration? (http://tomcat.apache.org/tomcat-5.5-doc/config/realm.html)

Craig Walls - 15/Apr/08 11:37 AM Because my database schema may not be so simple as to include the username, password, and enabled status in a single user table. For that matter, I may not even care to take advantage of the enabled field, but Spring Security still requires it. Being able to specify SQL gives me the ultimate flexibility so that I can write queries like "select student_id, password, true from students where student_id=?". In this case, I've effectively set the enabled flag to always be true for all users. Another reason to be able to specify SQL is that it is consistent with how JdbcDaoImpl already works. Since <jdbc-user-service> is just a configuration abstraction over JdbcDaoImpl, I argue that the configuration options should be consistent between the two.

Triqui Galletas - 17/Apr/08 12:07 PM Well, in fact what I would really like is the property rolePrefix to be exposed, cause even with this fix applied I still have to define my authentication provider like this: <authentication-provider user-service-ref="jdbcUserService"> <password-encoder hash="sha" /> </authentication-provider> <beans:bean id="jdbcUserService" class="org.springframework.security.userdetails.jdbc.JdbcDaoImpl"> <beans:property name="rolePrefix" value="ROLE_" /> <beans:property name="dataSource" ref="appDataSource" /> <beans:property name="usersByUsernameQuery" value="SELECT web_login, web_sha_password, 1 FROM password WHERE web_login = ?" /> <beans:property name="authoritiesByUsernameQuery" value="SELECT web_login, web_role FROM customer_role WHERE web_login = ?" /> </beans:bean> I don't think it will happen, though.