spring security基于数据库表进行认证

我们从研究org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.class的源码开始

public class JdbcDaoImpl extends JdbcDaoSupport
        implements UserDetailsService, MessageSourceAware {
    //默认的用户查询sql
    public static final String DEF_USERS_BY_USERNAME_QUERY = "select username,password,enabled "
            + "from users " + "where username = ?";
　　//默认的权限查询sql
    public static final String DEF_AUTHORITIES_BY_USERNAME_QUERY = "select username,authority "
            + "from authorities " + "where username = ?";
　　//默认的权限组查询sql
    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";

    protected MessageSourceAccessor messages = SpringSecurityMessageSource.getAccessor();

    private String authoritiesByUsernameQuery;
    private String groupAuthoritiesByUsernameQuery;
    private String usersByUsernameQuery;
　　//角色前缀默认为""
    private String rolePrefix = "";
    private boolean usernameBasedPrimaryKey = true;
    private boolean enableAuthorities = true;
　　//权限组默认未设置
    private boolean enableGroups;


　　//构造时填充sql
    public JdbcDaoImpl() {
        this.usersByUsernameQuery = DEF_USERS_BY_USERNAME_QUERY;
        this.authoritiesByUsernameQuery = DEF_AUTHORITIES_BY_USERNAME_QUERY;
        this.groupAuthoritiesByUsernameQuery = DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY;
    }

  
    protected MessageSourceAccessor getMessages() {
        return this.messages;
    }

 
    protected void addCustomAuthorities(String username,
            List<GrantedAuthority> authorities) {
    }

    public String getUsersByUsernameQuery() {
        return this.usersByUsernameQuery;
    }

    @Override
    protected void initDao() throws ApplicationContextException {
        Assert.isTrue(this.enableAuthorities || this.enableGroups,
                "Use of either authorities or groups must be enabled");
    }

　　//加载用户方法，实现了UserDetailsService接口
    @Override
    public UserDetails loadUserByUsername(String username)
            throws UsernameNotFoundException {
        List<UserDetails> users = loadUsersByUsername(username);//调用方法加载用户

        if (users.size() == 0) {
            this.logger.debug("Query returned no results for user '" + username + "'");

            throw new UsernameNotFoundException(
                    this.messages.getMessage("JdbcDaoImpl.notFound",
                            new Object[] { username }, "Username {0} not found"));
        }

        UserDetails user = users.get(0); //get(0)说明如果数据库中有多个相同name的user，那么以第一个为准

        Set<GrantedAuthority> dbAuthsSet = new HashSet<GrantedAuthority>();

        if (this.enableAuthorities) {
　　　　　　　//加载权限，如果下面的权限组执行了这里的权限将被覆盖，因为最终会被存入dbAuthsSet 这个set集合中
            dbAuthsSet.addAll(loadUserAuthorities(user.getUsername()));
        }

        if (this.enableGroups) {
　　　　　　　　//加载权限组
            dbAuthsSet.addAll(loadGroupAuthorities(user.getUsername()));
        }

        List<GrantedAuthority> dbAuths = new ArrayList<GrantedAuthority>(dbAuthsSet);
　　　　
        addCustomAuthorities(user.getUsername(), dbAuths);

        if (dbAuths.size() == 0) {
            this.logger.debug("User '" + username
                    + "' has no authorities and will be treated as 'not found'");

            throw new UsernameNotFoundException(this.messages.getMessage(
                    "JdbcDaoImpl.noAuthority", new Object[] { username },
                    "User {0} has no GrantedAuthority"));
        }
　　　　//创建用户
        return createUserDetails(username, user, dbAuths);
    }

    protected List<UserDetails> loadUsersByUsername(String username) {
　　　　//spring jdbc去查询user
        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);//注意：你的sql查询结果顺序
                        String password = rs.getString(2);//这里和下面都是
                        boolean enabled = rs.getBoolean(3);
                        return new User(username, password, enabled, true, true, true,
                                AuthorityUtils.NO_AUTHORITIES);//除了前三个我们可以操纵，后面的状态值都是固定开启的
                    }

                });
    }

 　　//注意事项和上面一样
    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);
                    }
                });
    }

 　　//同上
    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);
                    }
                });
    }

    
    protected UserDetails createUserDetails(String username,
            UserDetails userFromUserQuery, List<GrantedAuthority> combinedAuthorities) {
        String returnUsername = userFromUserQuery.getUsername();

        if (!this.usernameBasedPrimaryKey) {
            returnUsername = username;
        }

        return new User(returnUsername, userFromUserQuery.getPassword(),
                userFromUserQuery.isEnabled(), true, true, true, combinedAuthorities);
    }

   
    public void setAuthoritiesByUsernameQuery(String queryString) {
        this.authoritiesByUsernameQuery = queryString;
    }

    protected String getAuthoritiesByUsernameQuery() {
        return this.authoritiesByUsernameQuery;
    }

    
    public void setGroupAuthoritiesByUsernameQuery(String queryString) {
        this.groupAuthoritiesByUsernameQuery = queryString;
    }

   
    public void setRolePrefix(String rolePrefix) {
        this.rolePrefix = rolePrefix;
    }

    protected String getRolePrefix() {
        return this.rolePrefix;
    }

    
    public void setUsernameBasedPrimaryKey(boolean usernameBasedPrimaryKey) {
        this.usernameBasedPrimaryKey = usernameBasedPrimaryKey;
    }

    protected boolean isUsernameBasedPrimaryKey() {
        return this.usernameBasedPrimaryKey;
    }

  
    public void setUsersByUsernameQuery(String usersByUsernameQueryString) {
        this.usersByUsernameQuery = usersByUsernameQueryString;
    }

    protected boolean getEnableAuthorities() {
        return this.enableAuthorities;
    }

   
    public void setEnableAuthorities(boolean enableAuthorities) {
        this.enableAuthorities = enableAuthorities;
    }

    protected boolean getEnableGroups() {
        return this.enableGroups;
    }

  
    public void setEnableGroups(boolean enableGroups) {
        this.enableGroups = enableGroups;
    }

    @Override
    public void setMessageSource(MessageSource messageSource) {
        Assert.notNull(messageSource, "messageSource cannot be null");
        this.messages = new MessageSourceAccessor(messageSource);
    }
}

主要几点在我注释的那些地方，可以看出这种方式是很不灵活的一种方式，但足够满足大多数小项目了。

根据这个内置的实现我们的数据表应该使用5张表来满足它，users，authorities，groups以及两张关联表。

基本结构如下：引用http://www.cnblogs.com/tyb1222/p/4155670.html

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50621
Source Host           : localhost:3306
Source Database       : security

Target Server Type    : MYSQL
Target Server Version : 50621
File Encoding         : 65001

Date: 2014-12-10 15:49:04
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for authorities
-- ----------------------------
DROP TABLE IF EXISTS `authorities`;
CREATE TABLE `authorities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `authority` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for groups
-- ----------------------------
DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `groupName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for group_authorities
-- ----------------------------
DROP TABLE IF EXISTS `group_authorities`;
CREATE TABLE `group_authorities` (
  `group_Id` int(11) NOT NULL AUTO_INCREMENT,
  `authority` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`group_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for group_members
-- ----------------------------
DROP TABLE IF EXISTS `group_members`;
CREATE TABLE `group_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(20) DEFAULT NULL,
  `group_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `userName` varchar(20) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `enabled` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

配置文件如下：

@Configuration
@EnableWebSecurity//启用web安全功能
public class SecurityConfig extends WebSecurityConfigurerAdapter{
    @Autowired
    private DataSource dataSource;
    @Override
    protected void configure(AuthenticationManagerBuilder auth)
            throws Exception {
        //基于数据库表进行认证，当调用groupAuthoritiesByUsername这个方法时enableGroups将被设置为true

　　　　 auth.jdbcAuthentication().dataSource(dataSource).usersByUsernameQuery("select username,password,enabled from users where username=?").groupAuthoritiesByUsername("select g.id, g.groupname, 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");

　　　　//其实只有auth.jdbcAuthentication().dataSource(dataSource)这一句就已经可以了，因为我们写的sql与它内部的sql是一样的。当需要时才去手写它，比如当需要调整查询条件或查询结果时

} }

还有一点是我们数据库表里一定要有用户并且关联至少一个权限，不然认证不会通过的。

这是基于数据库表进行认证最简单的一种方式，并且限制较多，只能处理用户和权限(不能处理角色，虽然可以开启权限组但是没什么用，我们只能判断用户是否拥有权限却不能判断用户是否属于某一组)，如果你的项目权限比较复杂那么推荐你去扩展UserDetailsService实现自定义加载数据。

经过这几天研究shiro和springsecurity之后感觉它俩最大的不同就是springsecurity中的角色和权限的概念完全是同一个东西。而shiro中则比较清晰，用户，角色，权限。用户只能去通过角色去间接的绑定权限，而不能直接去与权限绑定。

以上纯属个人见解。