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