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

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基于数据库表进行认证

标签：server   get   关联表   顺序   oauth   access   注意事项   apt   throw