Spring Security之自定义数据库表

[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)
);

由上边的方法的具体实现可以看出，这三条查询语句需要符合一定的规则：

1. DEF_USERS_BY_USERNAME_QUERY查询结果的顺序分别是用户名、密码和是否启用；
2. DEF_AUTHORITIES_BY_USERNAME_QUERY要保证查询结果的第二个字段是权限字符串
3. 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源码，可以直接运行测试一下。