[org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl]类中定义了三条sql语句和三个相关的方法：
public static final String DEF_USERS_BY_USERNAME_QUERY = "select username,password,enabled "
+ "from users " + "where username = ?";
public static final String DEF_AUTHORITIES_BY_USERNAME_QUERY = "select username,authority "
+ "from authorities " + "where username = ?";
public static final String DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY = "select g.id, g.group_name, ga.authority "
+ "from groups g, group_members gm, group_authorities ga "
+ "where gm.username = ? " + "and g.id = ga.group_id "
+ "and g.id = gm.group_id";
上边的三条SQL语句，分别在下面的三个方法中使用：
/**
* Executes the SQL <tt>usersByUsernameQuery</tt> and returns a list of UserDetails
* objects. There should normally only be one matching user.
*/
protected List<UserDetails> loadUsersByUsername(String username) {
return getJdbcTemplate().query(this.usersByUsernameQuery,
new String[] { username }, new RowMapper<UserDetails>() {
@Override
public UserDetails mapRow(ResultSet rs, int rowNum)
throws SQLException {
String username = rs.getString(1);
String password = rs.getString(2);
boolean enabled = rs.getBoolean(3);
return new User(username, password, enabled, true, true, true,
AuthorityUtils.NO_AUTHORITIES);
}
});
}
/**
* Loads authorities by executing the SQL from <tt>authoritiesByUsernameQuery</tt>.
*
* @return a list of GrantedAuthority objects for the user
*/
protected List<GrantedAuthority> loadUserAuthorities(String username) {
return getJdbcTemplate().query(this.authoritiesByUsernameQuery,
new String[] { username }, new RowMapper<GrantedAuthority>() {
@Override
public GrantedAuthority mapRow(ResultSet rs, int rowNum)
throws SQLException {
String roleName = JdbcDaoImpl.this.rolePrefix + rs.getString(2);
return new SimpleGrantedAuthority(roleName);
}
});
}
/**
* Loads authorities by executing the SQL from
* <tt>groupAuthoritiesByUsernameQuery</tt>.
*
* @return a list of GrantedAuthority objects for the user
*/
protected List<GrantedAuthority> loadGroupAuthorities(String username) {
return getJdbcTemplate().query(this.groupAuthoritiesByUsernameQuery,
new String[] { username }, new RowMapper<GrantedAuthority>() {
@Override
public GrantedAuthority mapRow(ResultSet rs, int rowNum)
throws SQLException {
String roleName = getRolePrefix() + rs.getString(3);
return new SimpleGrantedAuthority(roleName);
}
});
}
分别用来查询用户的详细资料，根据用户名查询用户的权限信息，根据用户名查询用户的分组的所有的权限信息；上边的SQL语句是基于Spring Security的默认的database schema设计的：
create table users(
username varchar_ignorecase(50) not null primary key,
password varchar_ignorecase(50) not null,
enabled boolean not null
);
create table authorities (
username varchar_ignorecase(50) not null,
authority varchar_ignorecase(50) not null,
constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);
create table groups (
id bigint generated by default as identity(start with 0) primary key,
group_name varchar_ignorecase(50) not null
);
create table group_authorities (
group_id bigint not null,
authority varchar(50) not null,
constraint fk_group_authorities_group foreign key(group_id) references groups(id)
);
create table group_members (
id bigint generated by default as identity(start with 0) primary key,
username varchar(50) not null,
group_id bigint not null,
constraint fk_group_members_group foreign key(group_id) references groups(id)
);
由上边的方法的具体实现可以看出，这三条查询语句需要符合一定的规则：
- DEF_USERS_BY_USERNAME_QUERY查询结果的顺序分别是用户名、密码和是否启用；
- DEF_AUTHORITIES_BY_USERNAME_QUERY要保证查询结果的第二个字段是权限字符串
- DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY要保证查询结构的第三个字段是权限字符串
AuthenticationManagerBuilder为我们提供了重新设置这三个字符串的方法：
private static final String DEF_USERS_BY_USERNAME_QUERY = "select username,password,enabled from sys_user where username = ?";
private static final String DEF_AUTHORITIES_BY_USERNAME_QUERY = "select g.role_id, gat.authority_name "
+ "from sys_role g, sys_user_role gm, sys_role_authority ga ,sys_authority gat,sys_user u" + " where "
+ "u.username =?" + " and " + "gm.user_id = u.user_id" + " and " + "g.role_id = ga.role_id" + " and "
+ "g.role_id = gm.role_id" + " and " + "ga.authority_id = gat.authority_id";
private static final String DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY = "select g.role_id, g.role_name, gat.authority_name "
+ "from sys_role g, sys_user_role gm, sys_role_authority ga ,sys_authority gat,sys_user u" + " where "
+ "u.username =?" + " and " + "gm.user_id = u.user_id" + " and " + "g.role_id = ga.role_id" + " and "
+ "g.role_id = gm.role_id" + " and " + "ga.authority_id = gat.authority_id";
@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication().dataSource(dataSource).usersByUsernameQuery(DEF_USERS_BY_USERNAME_QUERY)
.authoritiesByUsernameQuery(DEF_AUTHORITIES_BY_USERNAME_QUERY)
.groupAuthoritiesByUsername(DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY).rolePrefix("");
}
这样就完成了自定义数据库表与Spring Security的整合。下载GitHub源码，可以直接运行测试一下。
