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中则比较清晰，用户，角色，权限。用户只能去通过角色去间接的绑定权限，而不能直接去与权限绑定。

以上纯属个人见解。

巴特西

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

最新文章

热门文章