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源码，可以直接运行测试一下。