Spring Security 3.0数据库动态实现权限控制
第一次接触Spring Security，在网上找了很多资料，有简单示例的，就是把用户名及权限信息配置在XML里面，但这种不太适合一般的项目！有些资料是翻译源代码讲解各个过滤器的作用以及其工作原理！由于项目时间紧所以求成心切的我看这些资料的时候总是很匆忙的跳步来看，到最后反而稀里糊涂，脑子里更乱，真心的希望有个狄仁杰中的元芳能在身边，这样我还能听取下他的意见："元芳，你怎么看！"这两天静下来把之前看的那些详细看了看，最后感觉这玩意儿也不是那么那么让人抓狂！
开发环境 SpringIDE 3.0、Spring Security 3.0.2、myibatis、Maven、MySql
最前面的spring配置以及Maven管理架包、myibatis的配置就不说了
首先：
1、需要将所需要的JAR包在pom中配置
2、在web.xml中添加spring security的过滤链
<filter><filter-name>springSecurityFilterChain</filter-name><filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class></filter><filter-mapping><filter-name>springSecurityFilterChain</filter-name><url-pattern>/*</url-pattern></filter-mapping>
<?xml version="1.0" encoding="UTF-8"?><b:beans xmlns="http://www.springframework.org/schema/security" xmlns:b="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-3.0.xsd"><!-- <global-method-security pre-post-annotations="enabled" /> --><!-- access-denied-page配置访问失败页面 --><http auto-config="true" access-denied-page="/accessDenied.htm"><!-- 不要过滤图片等静态资源，其中**代表可以跨越目录，*不可以跨越目录。 --> <intercept-url pattern="/**/*.jpg" filters="none" /> <intercept-url pattern="/**/*.png" filters="none" /> <intercept-url pattern="/**/*.gif" filters="none" /> <intercept-url pattern="/**/*.css" filters="none" /> <intercept-url pattern="/**/*.js" filters="none" /> <intercept-url pattern="/login.htm*" filters="none"/> <!-- <intercept-url pattern="/index.htm" access="ROLE_USER,ROLE_ADMIN"/> <intercept-url pattern="/header.htm" access="ROLE_USER,ROLE_ADMIN"/> <intercept-url pattern="/left.htm" access="ROLE_USER,ROLE_ADMIN"/> --> <intercept-url pattern="/**" access="ROLE_USER"/><!-- 配置登录页面 --><form-login login-page="/login.htm"default-target-url="/" authentication-failure-url="/login.htm?error=true"login-processing-url="/springSecurityLogin"/><!--"记住我"功能，采用持久化策略(将用户的登录信息存放在数据库表中) --><!-- <remember-me/> --><!-- 用户退出的跳转页面 --><logout logout-success-url="/login.htm" invalidate-session="true" logout-url="/logout" /><!-- 会话管理，设置最多登录异常，error-if-maximum-exceeded = false为第二次登录就会使前一个登录失效 --> <session-management invalid-session-url="/error/errorPage.jsp"><concurrency-control max-sessions="1" error-if-maximum-exceeded="false" /></session-management><!--添加自定义的过滤器 放在FILTER_SECURITY_INTERCEPTOR之前有效 --><custom-filter ref="customFilterSecurityInterceptor" before="FILTER_SECURITY_INTERCEPTOR" /></http><!-- 配置认证管理器 --><authentication-manager alias="authenticationManager"><authentication-provider user-service-ref="customUserDetailsService"> <password-encoder hash="md5"/><!-- <jdbc-user-service data-source-ref="dataSource"/> --></authentication-provider></authentication-manager></b:beans>
package com.doone.wisdom.security.impl;import java.util.ArrayList;import java.util.Collection;import java.util.List;import javax.annotation.Resource;import org.springframework.security.core.GrantedAuthority;import org.springframework.security.core.authority.GrantedAuthorityImpl;import org.springframework.security.core.userdetails.User;import org.springframework.security.core.userdetails.UserDetails;import org.springframework.security.core.userdetails.UsernameNotFoundException;import org.springframework.stereotype.Service;import com.doone.wisdom.dao.security.UserDao;import com.doone.wisdom.entity.security.RoleEntity;import com.doone.wisdom.entity.security.UserEntity;import com.doone.wisdom.security.service.CustomUserDetailsService;/** * TODO(这里用一句话描述这个类的职责). * @ClassName: CustomUserDetailsServiceImpl * @author Johnny_L_Q */@Service("customUserDetailsService")public class CustomUserDetailsServiceImpl implements CustomUserDetailsService { /** * @Fields serialVersionUID : TODO（用一句话描述这个变量表示什么）. */ private static final long serialVersionUID = 1L; @Resource private UserDao userDao; /* * 根据用户名判断是否存在 * <p>Title: loadUserByUsername</p> * <p>Description: </p> * @param arg0 * @return * @throws UsernameNotFoundException * @see org.springframework.security.core.userdetails.UserDetailsService#loadUserByUsername(java.lang.String) */ @Override public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException { UserEntity user = userDao.getUser(username); if (null == user) { throw new UsernameNotFoundException("用户" + username + "不存在"); } Collection<GrantedAuthority> auths = new ArrayList<GrantedAuthority>(); List<String> list = userDao.getAuthoritiesByUsername(username); for (int i = 0; i < list.size(); i++) { auths.add(new GrantedAuthorityImpl(list.get(i))); System.out.println("loadUserByUsername : " + list.get(i)); } //因为UserEntity实现了UserDetails，所以也可以直接返回user return new User(username, user.getPassword(), true, true, true, true, auths); }}package com.doone.wisdom.security.impl;import java.util.Collection;import java.util.Iterator;import org.springframework.security.access.AccessDeniedException;import org.springframework.security.access.ConfigAttribute;import org.springframework.security.access.SecurityConfig;import org.springframework.security.authentication.InsufficientAuthenticationException;import org.springframework.security.core.Authentication;import org.springframework.security.core.GrantedAuthority;import org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl;import org.springframework.security.provisioning.JdbcUserDetailsManager;import org.springframework.stereotype.Service;import com.doone.wisdom.security.service.CustomAccessDecisionManager;/** * * AccessdecisionManager在Spring security中是很重要的。 * * 在验证部分简略提过了，所有的Authentication实现需要保存在一个GrantedAuthority对象数组中。 这就是赋予给主体的权限。 * GrantedAuthority对象通过AuthenticationManager 保存到 * Authentication对象里，然后从AccessDecisionManager读出来，进行授权判断。 * * Spring Security提供了一些拦截器，来控制对安全对象的访问权限，例如方法调用或web请求。 * 一个是否允许执行调用的预调用决定，是由AccessDecisionManager实现的。 这个 AccessDecisionManager * 被AbstractSecurityInterceptor调用， 它用来作最终访问控制的决定。 * 这个AccessDecisionManager接口包含三个方法： * * void decide(Authentication authentication, Object secureObject, * List<ConfigAttributeDefinition> config) throws AccessDeniedException; boolean * supports(ConfigAttribute attribute); boolean supports(Class clazz); * * 从第一个方法可以看出来，AccessDecisionManager使用方法参数传递所有信息，这好像在认证评估时进行决定。 * 特别是，在真实的安全方法期望调用的时候，传递安全Object启用那些参数。 比如，让我们假设安全对象是一个MethodInvocation。 * 很容易为任何Customer参数查询MethodInvocation， * 然后在AccessDecisionManager里实现一些有序的安全逻辑，来确认主体是否允许在那个客户上操作。 * 如果访问被拒绝，实现将抛出一个AccessDeniedException异常。 * * 这个 supports(ConfigAttribute) 方法在启动的时候被 * AbstractSecurityInterceptor调用，来决定AccessDecisionManager * 是否可以执行传递ConfigAttribute。 supports(Class)方法被安全拦截器实现调用， * 包含安全拦截器将显示的AccessDecisionManager支持安全对象的类型。 * TODO(这里用一句话描述这个类的职责). * @ClassName: CustomAccessDecisionManagerImpl * @author Johnny_L_Q */@Service("customAccessDecisionManager")public class CustomAccessDecisionManagerImpl implements CustomAccessDecisionManager { /* * <p>Title: decide</p> * <p>Description: </p> * @param arg0 * @param arg1 * @param arg2 * @throws AccessDeniedException * @throws InsufficientAuthenticationException * @see org.springframework.security.access.AccessDecisionManager * #decide(org.springframework.security.core.Authentication, java.lang.Object, java.util.Collection) */ @Override public void decide(Authentication authentication, Object object, Collection<ConfigAttribute> configAttributes) throws AccessDeniedException, InsufficientAuthenticationException { if (null == configAttributes) { return; } Iterator<ConfigAttribute> cons = configAttributes.iterator(); while(cons.hasNext()){ ConfigAttribute ca = cons.next(); String needRole = ((SecurityConfig) ca).getAttribute(); //gra 为用户所被赋予的权限，needRole为访问相应的资源应具有的权限 for (GrantedAuthority gra : authentication.getAuthorities()) { if (needRole.trim().equals(gra.getAuthority().trim())) { return; } } } throw new AccessDeniedException("Access Denied"); } /* * <p>Title: supports</p> * <p>Description: </p> * @param arg0 * @return * @see org.springframework.security.access.AccessDecisionManager#supports(org.springframework.security.access.ConfigAttribute) */ @Override public boolean supports(ConfigAttribute arg0) { // TODO Auto-generated method stub return true; } /* * <p>Title: supports</p> * <p>Description: </p> * @param arg0 * @return * @see org.springframework.security.access.AccessDecisionManager#supports(java.lang.Class) */ @Override public boolean supports(Class<?> arg0) { // TODO Auto-generated method stub return true; }}package com.doone.wisdom.security.impl;import java.io.IOException;import javax.annotation.Resource;import javax.servlet.FilterChain;import javax.servlet.FilterConfig;import javax.servlet.ServletException;import javax.servlet.ServletRequest;import javax.servlet.ServletResponse;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.security.access.AccessDecisionManager;import org.springframework.security.access.SecurityMetadataSource;import org.springframework.security.access.intercept.AbstractSecurityInterceptor;import org.springframework.security.access.intercept.InterceptorStatusToken;import org.springframework.security.authentication.AuthenticationManager;import org.springframework.security.web.FilterInvocation;import org.springframework.security.web.access.intercept.FilterInvocationSecurityMetadataSource;import org.springframework.stereotype.Service;import com.doone.wisdom.security.service.CustomFilterSecurityInterceptor;/** * 该过滤器的主要作用就是通过spring著名的IoC生成securityMetadataSource。 * securityMetadataSource相当于本包中自定义的CostomInvocationSecurityMetadataSourceService。 * 该CostomInvocationSecurityMetadataSourceService的作用提从数据库提取权限和资源，装配到HashMap中， * 供Spring Security使用，用于权限校验。 * * TODO(这里用一句话描述这个类的职责). * @ClassName: CustomFilterSecurityInterceptorImpl * @author Johnny_L_Q */@Service("customFilterSecurityInterceptor")public class CustomFilterSecurityInterceptorImpl extends AbstractSecurityInterceptor implements CustomFilterSecurityInterceptor { @Resource @Qualifier("customInvocationSecurityMetadataSource") private FilterInvocationSecurityMetadataSource securityMetadataSource; @Resource @Qualifier("customAccessDecisionManager") @Override public void setAccessDecisionManager( AccessDecisionManager accessDecisionManager) { // TODO Auto-generated method stub super.setAccessDecisionManager(accessDecisionManager); }/* @Resource @Qualifier("customAccessDecisionManager") public AccessDecisionManager accessDecisionManager;*/ /* @Resource @Qualifier("authenticationManager") public AuthenticationManager authenticationManager;*/ @Resource @Qualifier("authenticationManager") @Override public void setAuthenticationManager(AuthenticationManager newManager) { super.setAuthenticationManager(newManager); } /* * <p>Title: doFilter</p> * <p>Description: </p> * @param arg0 * @param arg1 * @param arg2 * @throws IOException * @throws ServletException * @see javax.servlet.Filter#doFilter(javax.servlet.ServletRequest, javax.servlet.ServletResponse, javax.servlet.FilterChain) */ @Override public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException { FilterInvocation fi = new FilterInvocation(request, response, chain); infoke(fi); } /** * TODO(这里用一句话描述这个方法的作用). * @param fi * @throws ServletException * @throws IOException */ private void infoke(FilterInvocation fi) throws IOException, ServletException { InterceptorStatusToken token = super.beforeInvocation(fi); try { fi.getChain().doFilter(fi.getRequest(), fi.getResponse()); } finally { super.afterInvocation(token, null); } } /* * <p>Title: init</p> * <p>Description: </p> * @param arg0 * @throws ServletException * @see javax.servlet.Filter#init(javax.servlet.FilterConfig) */ @Override public void init(FilterConfig arg0) throws ServletException { // TODO Auto-generated method stub } /* * <p>Title: getSecureObjectClass</p> * <p>Description: </p> * @return * @see org.springframework.security.access.intercept.AbstractSecurityInterceptor#getSecureObjectClass() */ @Override public Class<?> getSecureObjectClass() { // TODO Auto-generated method stub return FilterInvocation.class; } /* * <p>Title: obtainSecurityMetadataSource</p> * <p>Description: </p> * @return * @see org.springframework.security.access.intercept.AbstractSecurityInterceptor#obtainSecurityMetadataSource() */ @Override public SecurityMetadataSource obtainSecurityMetadataSource() { // TODO Auto-generated method stub return this.securityMetadataSource; } /* * <p>Title: destroy</p> * <p>Description: </p> * @see javax.servlet.Filter#destroy() */ @Override public void destroy() { // TODO Auto-generated method stub } public FilterInvocationSecurityMetadataSource getSecurityMetadataSource() { return securityMetadataSource; } public void setSecurityMetadataSource( FilterInvocationSecurityMetadataSource securityMetadataSource) { this.securityMetadataSource = securityMetadataSource; }}package com.doone.wisdom.security.impl;import java.util.ArrayList;import java.util.Collection;import java.util.HashMap;import java.util.Iterator;import java.util.List;import javax.annotation.PostConstruct;import javax.annotation.Resource;import org.springframework.security.access.ConfigAttribute;import org.springframework.security.access.SecurityConfig;import org.springframework.security.web.FilterInvocation;import org.springframework.security.web.util.AntUrlPathMatcher;import org.springframework.security.web.util.UrlMatcher;import org.springframework.stereotype.Service;import com.doone.wisdom.entity.security.AuthorityEntity;import com.doone.wisdom.entity.security.RoleEntity;import com.doone.wisdom.security.service.CustomInvocationSecurityMetadataSource;import com.doone.wisdom.service.iface.AuthorityService;import com.doone.wisdom.service.iface.ResourceService;/** * TODO(这里用一句话描述这个类的职责). * @ClassName: CustomInvocationSecurityMetadataSourceImpl * @author Johnny_L_Q */@Service("customInvocationSecurityMetadataSource")public class CustomInvocationSecurityMetadataSourceImpl implements CustomInvocationSecurityMetadataSource { @Resource private ResourceService resourceService; @Resource private AuthorityService authorityService; private UrlMatcher urlMatcher = new AntUrlPathMatcher(); //private AntPathRequestMatcher pathMatcher; private HashMap<String, Collection<ConfigAttribute>> resourceMap = null; /** * * 自定义方法，这个类放入到Spring容器后， * 指定init为初始化方法，从数据库中读取资源 * TODO(这里用一句话描述这个方法的作用). */ @PostConstruct public void init() { loadResourceDefine(); } /** * * TODO(程序启动的时候就加载所有资源信息). */ private void loadResourceDefine() { // 在Web服务器启动时，提取系统中的所有权限。 //sql = "select authority_name from pub_authorities"; List<AuthorityEntity> query = authorityService.getAllAuthoritys(); /**//* * 应当是资源为key， 权限为value。 资源通常为url， 权限就是那些以ROLE_为前缀的角色。 一个资源可以由多个权限来访问。 * sparta */ resourceMap = new HashMap<String, Collection<ConfigAttribute>>(); for (AuthorityEntity auth : query) { String authName = auth.getAuthorityName(); ConfigAttribute ca = new SecurityConfig(auth.getAuthorityName()); List<String> resources = resourceService.getResourcesByAuthName(authName); for (String str : resources) { //String authName = auth2.getAuthorityName(); String url = str; /**//* * 判断资源文件和权限的对应关系，如果已经存在相关的资源url，则要通过该url为key提取出权限集合，将权限增加到权限集合中。 * sparta */ if (resourceMap.containsKey(url)) { Collection<ConfigAttribute> value = resourceMap.get(url); value.add(ca); resourceMap.put(url, value); } else { Collection<ConfigAttribute> atts = new ArrayList<ConfigAttribute>(); atts.add(ca); resourceMap.put(url, atts); } } } } /** * TODO(自定义方法，将List<Role>集合转换为框架需要的Collection<ConfigAttribute>集合). * @param roles 角色集合 * @return list 封装好的Collection集合 */ private Collection<ConfigAttribute> listToCollection(List<RoleEntity> roles) { List<ConfigAttribute> list = new ArrayList<ConfigAttribute>(); for (RoleEntity role : roles) { list.add(new SecurityConfig(role.getRoleName())); } return list; } /* * <p>Title: getAllConfigAttributes</p> * <p>Description: </p> * @return * @see org.springframework.security.access.SecurityMetadataSource#getAllConfigAttributes() */ @Override public Collection<ConfigAttribute> getAllConfigAttributes() { return null; } /* * <p>Title: getAttributes</p> * <p>Description: </p> * @param arg0 * @return * @throws IllegalArgumentException * @see org.springframework.security.access.SecurityMetadataSource#getAttributes(java.lang.Object) */ @Override public Collection<ConfigAttribute> getAttributes(Object object) throws IllegalArgumentException { //object 是一个URL ,为用户请求URL String url = ((FilterInvocation)object).getRequestUrl(); if("/".equals(url)){ return null; } int firstQuestionMarkIndex = url.indexOf("."); //判断请求是否带有参数 如果有参数就去掉后面的后缀和参数(/index.do --> /index) if(firstQuestionMarkIndex != -1){ url = url.substring(0,firstQuestionMarkIndex); } Iterator<String> ite = resourceMap.keySet().iterator(); //取到请求的URL后与上面取出来的资源做比较 while (ite.hasNext()) { String resURL = ite.next(); if(urlMatcher.pathMatchesUrl(url, resURL)){ return resourceMap.get(resURL); } } return null; } /* * <p>Title: supports</p> * <p>Description: </p> * @param arg0 * @return * @see org.springframework.security.access.SecurityMetadataSource#supports(java.lang.Class) */ @Override public boolean supports(Class<?> arg0) { // TODO Auto-generated method stub return true; }}/*MySQL Data TransferSource Host: localhostSource Database: wisdomTarget Host: localhostTarget Database: wisdomDate: 2012/10/19 9:29:52*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for pub_authorities-- ----------------------------CREATE TABLE `pub_authorities` ( `authority_id` varchar(32) COLLATE utf8_bin NOT NULL, `authority_name` varchar(40) COLLATE utf8_bin NOT NULL, `authority_desc` varchar(100) COLLATE utf8_bin DEFAULT NULL, `enabled` int(10) NOT NULL, `issys` int(10) NOT NULL, PRIMARY KEY (`authority_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ------------------------------ Records -- ----------------------------INSERT INTO `pub_authorities` VALUES ('1', 'ROLE_ADMIN', '首页广告条管理', '1', '0');INSERT INTO `pub_authorities` VALUES ('2', 'ROLE_USER', '首页', '1', '0');/*MySQL Data TransferSource Host: localhostSource Database: wisdomTarget Host: localhostTarget Database: wisdomDate: 2012/10/19 9:30:25*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for pub_resources-- ----------------------------CREATE TABLE `pub_resources` ( `resource_id` varchar(32) COLLATE utf8_bin NOT NULL, `resource_name` varchar(100) COLLATE utf8_bin NOT NULL, `resource_type` varchar(40) COLLATE utf8_bin NOT NULL, `priority` int(10) NOT NULL, `resource_string` varchar(200) COLLATE utf8_bin NOT NULL, `resource_desc` varchar(100) COLLATE utf8_bin DEFAULT NULL, `enabled` int(10) NOT NULL, `issys` int(10) NOT NULL, PRIMARY KEY (`resource_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ------------------------------ Records -- ----------------------------INSERT INTO `pub_resources` VALUES ('1', '首页广告条管理', 'action', '0', '/funcPages/adManager', '首页广告条管理', '1', '0');INSERT INTO `pub_resources` VALUES ('2', '首页', 'action', '0', '/index', '首页', '1', '0');/*MySQL Data TransferSource Host: localhostSource Database: wisdomTarget Host: localhostTarget Database: wisdomDate: 2012/10/19 9:30:17*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for pub_authorities_resources-- ----------------------------CREATE TABLE `pub_authorities_resources` ( `id` varchar(32) COLLATE utf8_bin NOT NULL, `authority_id` varchar(32) COLLATE utf8_bin NOT NULL, `resource_id` varchar(32) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), KEY `fk_res_aut` (`authority_id`), KEY `fk_res` (`resource_id`), CONSTRAINT `fk_res` FOREIGN KEY (`resource_id`) REFERENCES `pub_resources` (`resource_id`), CONSTRAINT `fk_res_aut` FOREIGN KEY (`authority_id`) REFERENCES `pub_authorities` (`authority_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ------------------------------ Records -- ----------------------------INSERT INTO `pub_authorities_resources` VALUES ('1', '1', '1');INSERT INTO `pub_authorities_resources` VALUES ('2', '2', '2');/*MySQL Data TransferSource Host: localhostSource Database: wisdomTarget Host: localhostTarget Database: wisdomDate: 2012/10/19 9:30:30*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for pub_roles-- ----------------------------CREATE TABLE `pub_roles` ( `role_id` varchar(32) COLLATE utf8_bin NOT NULL, `role_name` varchar(100) COLLATE utf8_bin NOT NULL, `role_desc` varchar(100) COLLATE utf8_bin DEFAULT NULL, `enabled` int(10) NOT NULL, `issys` int(10) NOT NULL COMMENT '角色表', PRIMARY KEY (`role_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ------------------------------ Records -- ----------------------------INSERT INTO `pub_roles` VALUES ('1', 'ROLE_ADMIN', '系统登录', '1', '0');INSERT INTO `pub_roles` VALUES ('2', 'ROLE_USER', '普通用户', '1', '0');/*MySQL Data TransferSource Host: localhostSource Database: wisdomTarget Host: localhostTarget Database: wisdomDate: 2012/10/19 9:30:36*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for pub_roles_authorities-- ----------------------------CREATE TABLE `pub_roles_authorities` ( `id` varchar(32) COLLATE utf8_bin NOT NULL, `role_id` varchar(32) COLLATE utf8_bin NOT NULL, `authority_id` varchar(32) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), KEY `fk_aut_role` (`role_id`), KEY `fk_aut` (`authority_id`), CONSTRAINT `fk_aut` FOREIGN KEY (`authority_id`) REFERENCES `pub_authorities` (`authority_id`), CONSTRAINT `fk_aut_role` FOREIGN KEY (`role_id`) REFERENCES `pub_roles` (`role_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ------------------------------ Records -- ----------------------------INSERT INTO `pub_roles_authorities` VALUES ('1', '1', '1');INSERT INTO `pub_roles_authorities` VALUES ('2', '1', '2');INSERT INTO `pub_roles_authorities` VALUES ('3', '2', '2');/*MySQL Data TransferSource Host: localhostSource Database: wisdomTarget Host: localhostTarget Database: wisdomDate: 2012/10/19 9:30:42*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for pub_users-- ----------------------------CREATE TABLE `pub_users` ( `user_id` varchar(32) COLLATE utf8_bin NOT NULL, `user_account` varchar(30) COLLATE utf8_bin NOT NULL, `user_name` varchar(40) COLLATE utf8_bin NOT NULL, `user_password` varchar(100) COLLATE utf8_bin NOT NULL, `enabled` int(10) NOT NULL, `issys` int(10) NOT NULL, `user_desc` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '用户表', PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ------------------------------ Records -- ----------------------------INSERT INTO `pub_users` VALUES ('1', 'admin', 'admin', '21232f297a57a5a743894a0e4a801fc3', '1', '1', '超级管理员');INSERT INTO `pub_users` VALUES ('2', 'user', 'user', 'ee11cbb19052e40b07aac0ca060c23ee', '1', '0', '普通用户');/*MySQL Data TransferSource Host: localhostSource Database: wisdomTarget Host: localhostTarget Database: wisdomDate: 2012/10/19 9:30:48*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for pub_users_roles-- ----------------------------CREATE TABLE `pub_users_roles` ( `id` varchar(32) CHARACTER SET utf8 NOT NULL, `user_id` varchar(32) COLLATE utf8_bin NOT NULL, `role_id` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '角色和用户中间表', PRIMARY KEY (`id`), KEY `fk_user` (`user_id`), KEY `fk_role` (`role_id`), CONSTRAINT `fk_role` FOREIGN KEY (`role_id`) REFERENCES `pub_roles` (`role_id`), CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `pub_users` (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;-- ------------------------------ Records -- ----------------------------INSERT INTO `pub_users_roles` VALUES ('1', '1', '1');INSERT INTO `pub_users_roles` VALUES ('2', '2', '2');select b.authority_name from PUB_ROLES a, PUB_AUTHORITIES b, PUB_ROLES_AUTHORITIES c where c.role_id = a.role_id and c.authority_id = b.authority_id and a.role_name in (select role_name from PUB_USERS_ROLES ur, PUB_USERS u, PUB_ROLES r where ur.user_id = u.user_id and ur.role_id = r.role_id and u.user_name = #{username} )select * from PUB_USERS u where u.user_name = #{username}select b.resource_string from PUB_AUTHORITIES_RESOURCES a ,PUB_RESOURCES b, PUB_AUTHORITIES c where a.resource_id = b.resource_id and a.authority_id = c.authority_id and c.authority_name = #{authName}select * from pub_authorities