spring Security jwt token授权和认证
导入依赖包:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId> </dependency> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt</artifactId> <version>0.9.1</version> </dependency>
exception导常处理类:
/** *token信息不合法 */ public class IllegalTokenAuthenticationException extends RuntimeException { public IllegalTokenAuthenticationException(String msg){ super(msg); } public IllegalTokenAuthenticationException(String msg, Throwable t){ super(msg,t); } } /** *登录异常处理 */ public class NoneTokenException extends RuntimeException{ public NoneTokenException(String msg){ super(msg); } public NoneTokenException(String msg, Throwable t){ super(msg,t); } } /** *检查token是否存在 */ public class TokenIsExpiredException extends RuntimeException { public TokenIsExpiredException(String msg){ super(msg); } public TokenIsExpiredException(String msg, Throwable t){ super(msg,t); } } /** * */ public class WrongPasswordException extends RuntimeException { public WrongPasswordException(String message) { super(message); } public WrongPasswordException(String message, Throwable cause) { super(message, cause); } } /** * */ public class WrongUsernameException extends RuntimeException{ public WrongUsernameException(String message) { super(message); } public WrongUsernameException(String message, Throwable cause) { super(message, cause); } }
filter相关类(主要做自定义授权认证、登录拦截等):
/** * Created by Mr.WU 2020/06/11 * 登录后的无权访问在此处理 */ public class AccessDenied implements AccessDeniedHandler { Logger logger = LoggerFactory.getLogger(this.getClass()); @Override public void handle(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse, AccessDeniedException e) throws IOException, ServletException { logger.error("当前用户没有访问该资源的权限:{}",e.getMessage()); httpServletResponse.setCharacterEncoding("utf-8"); httpServletResponse.getWriter().write(AuthErrorEnum.ACCESS_DENIED.getMessage()); } } /** * 登录时做处理 */ public class LoginAuthenticationFilter extends UsernamePasswordAuthenticationFilter { Logger log = LoggerFactory.getLogger(this.getClass()); private AuthenticationManager authenticationManager; private String head; private String tokenHeader; public LoginAuthenticationFilter(AuthenticationManager authenticationManager, String head, String tokenHeader) { this.authenticationManager = authenticationManager; this.head = head; this.tokenHeader = tokenHeader; super.setFilterProcessesUrl("/api/login"); } /** * 接收并解析用户登陆信息 /login,必须使用/login,和post方法才会进入此filter * 如果身份验证过程失败,就抛出一个AuthenticationException * * @param request * @param response * @return * @throws AuthenticationException */ @Override public Authentication attemptAuthentication(HttpServletRequest request, HttpServletResponse response) throws AuthenticationException { if (request.getContentType().equals(MediaType.APPLICATION_JSON) || request.getContentType().equals(MediaType.APPLICATION_JSON_VALUE)) { ObjectMapper mapper = new ObjectMapper(); UsernamePasswordAuthenticationToken authRequest = null; try (InputStream stream = request.getInputStream()) { Map<String, String> body = mapper.readValue(stream, Map.class); authRequest = new UsernamePasswordAuthenticationToken( body.get("username"), body.get("password") ); log.info("用户(登录名):{} 正在进行登录验证。。。密码:{}", body.get("username"), body.get("password")); } catch (IOException e) { e.printStackTrace(); authRequest = new UsernamePasswordAuthenticationToken("", ""); } finally { setDetails(request, authRequest); //提交给自定义的provider组件进行身份验证和授权 Authentication authentication = authenticationManager.authenticate(authRequest); return authentication; } } else { return super.attemptAuthentication(request, response); } } /** * 验证成功后,此方法会被调用,在此方法中生成token,并返回给客户端 * * @param request * @param response * @param chain * @param authResult * @throws IOException * @throws ServletException */ @Override protected void successfulAuthentication(HttpServletRequest request, HttpServletResponse response, FilterChain chain, Authentication authResult) throws IOException, ServletException { //设置安全上下文。在当前的线程中,任何一处都可以通过SecurityContextHolder来获取当前用户认证成功的Authentication对象 SecurityContextHolder.getContext().setAuthentication(authResult); log.debug("验证成功"); response.setContentType("application/json;charset=UTF-8"); PrintWriter out = response.getWriter(); JwtUser userDetails = (JwtUser) authResult.getPrincipal(); if (authResult != null) { String account = userDetails.getUsername(); Collection collection =userDetails.getAuthorities(); String authority = collection.iterator().next().toString(); String token = JwtTokenUtils.createToken(account, authority); Map<String, String> result = new HashMap<>(); result.put("authority", authority); result.put("account", account); result.put("token",head + token); response.setStatus(200); ObjectMapper om = new ObjectMapper(); out.write(om.writeValueAsString(result)); } out.flush(); out.close(); } } /** * 验证所有的请求 * * */ public class MyAuthenticationFilter extends BasicAuthenticationFilter { Logger log = LoggerFactory.getLogger(this.getClass()); private String tokenHeader; private String head; private UserDetailsService userDetailsService; private List<String> permitRegexUris; public MyAuthenticationFilter(AuthenticationManager authenticationManager, String tokenHeader, String head, UserDetailsService userDetailsService, String permitUris) { super(authenticationManager); this.head = head; this.tokenHeader = tokenHeader; this.userDetailsService = userDetailsService; this.permitRegexUris = Arrays.asList(permitUris.split(",")).stream().map(s -> { return PathUtil.getRegPath(s); }).collect(Collectors.toList()); } /** * 判断请求是否是否带有token信息,token是否合法,是否过期。设置安全上下文。 * * @param request * @param response * @param chain * @throws IOException * @throws ServletException */ @Override protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain chain) throws IOException, ServletException { String token = request.getHeader(tokenHeader); //可能是登录或者注册的请求,不带token信息,又或者是不需要登录,不需要token即可访问的资源。 String uri = request.getRequestURI(); for (String regexPath : permitRegexUris) { if (Pattern.matches(regexPath, uri)) { chain.doFilter(request, response); return; } } if (token == null) { log.warn("请登录访问"); throw new NoneTokenException(AuthErrorEnum.TOKEN_NEEDED.getMessage()); } if (!token.startsWith(head)) { log.warn("token信息不合法"); throw new IllegalTokenAuthenticationException(AuthErrorEnum.AUTH_HEADER_ERROR.getMessage()); } Claims claims = JwtTokenUtils.checkJWT(token.substring(head.length())); if (claims == null) { log.warn("token不存在"); throw new TokenIsExpiredException(AuthErrorEnum.TOKEN_EXPIRED.getMessage()); } String userName = (String) claims.get("username"); if (userName == null) { log.warn("userName不存在"); throw new TokenIsExpiredException(AuthErrorEnum.TOKEN_EXPIRED.getMessage()); } Date expiredTime = claims.getExpiration(); if ((new Date().getTime() > expiredTime.getTime())) { log.warn("当前token信息已过期,请重新登录"); throw new TokenIsExpiredException(AuthErrorEnum.TOKEN_EXPIRED.getMessage()); } if (userName != null && SecurityContextHolder.getContext().getAuthentication() == null) { UserDetails userDetails = userDetailsService.loadUserByUsername(userName); //生成生物认证 UsernamePasswordAuthenticationToken authentication = new UsernamePasswordAuthenticationToken(userDetails, null, userDetails.getAuthorities()); authentication.setDetails(new WebAuthenticationDetailsSource().buildDetails(request)); log.info("用户:{},正在访问:{}", userName, request.getRequestURI()); log.info("authenticated user " + userName + ", setting security context"); SecurityContextHolder.getContext().setAuthentication(authentication); chain.doFilter(request, response); } } } /** * 自定义授权验证方式 * */ public class MyAuthenticationProvider implements AuthenticationProvider { Logger logger = LoggerFactory.getLogger(SecurityConfiguration.class); private UserDetailsService userDetailsService; private BCryptPasswordEncoder passwordEncoder; public MyAuthenticationProvider(UserDetailsService userDetailsService, BCryptPasswordEncoder passwordEncoder) { this.userDetailsService = userDetailsService; this.passwordEncoder = passwordEncoder; } @Override public boolean supports(Class<?> aClass) { return aClass.equals(UsernamePasswordAuthenticationToken.class); } @Override public Authentication authenticate(Authentication authentication) throws AuthenticationException { // authentication,登录url提交的需要被认证的对象。只含有用户名和密码,需要根据用户名和密码来校验,并且授权。 String userName = authentication.getName(); String password = (String) authentication.getCredentials(); JwtUser userDetails = (JwtUser) userDetailsService.loadUserByUsername(userName); if (userDetails == null){ logger.warn("User not found with userName:{}",userName); throw new WrongUsernameException(AuthErrorEnum.LOGIN_NAME_ERROR.toString()); } //如果从url提交的密码到数据保存的密码没有经过加密或者编码,直接比较是否相同即可。 // 如果在添加用户时的密码是经过加密或者编码的应该使用对应的加密算法和编码工具对密码进行编码之后再进行比较 // if (!passwordEncoder.matches(password, userDetails.getPassword())){ // log.warn("Wrong password"); // throw new WrongPasswordException(AuthErrorEnum.LOGIN_PASSWORD_ERROR.getMessage()); // } if (!password.equals(userDetails.getPassword())){ // log.warn("Wrong password"); throw new WrongPasswordException(AuthErrorEnum.LOGIN_PASSWORD_ERROR.toString()); } Collection<? extends GrantedAuthority> authorities = userDetails.getAuthorities(); return new UsernamePasswordAuthenticationToken(userDetails,password,authorities); } }
JwtUser类:
/** * Created MR.WU 2020/06/11 */ public class JwtUser implements UserDetails { private Integer id; private String username; private String password; private Collection<? extends GrantedAuthority> authorities; public JwtUser() { } // 写一个能直接使用user创建jwtUser的构造器 public JwtUser(Users user) { id = user.getId(); username = user.getUserName(); password = user.getPassword(); authorities = Collections.singleton(new SimpleGrantedAuthority(user.getRoles())); } public Collection<? extends GrantedAuthority> getAuthorities() { return authorities; } public String getPassword() { return password; } public String getUsername() { return username; } public boolean isAccountNonExpired() { return true; } public boolean isAccountNonLocked() { return true; } public boolean isCredentialsNonExpired() { return true; } public boolean isEnabled() { return true; } @Override public String toString() { return "JwtUser{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", authorities=" + authorities + '}'; } }
JwtToken生成帮助类:
public class JwtTokenUtils { @Value("${jwt.tokenHeader}") private String tokenHeader; @Value("${jwt.head}") private String head; // @Value("${jwt.secret}") // private static String APPSECRET_KEY; @Value("${jwt.expiration}") private static long expiration; // public static final String TOKEN_HEADER = "Authorization"; // public static final String TOKEN_PREFIX = "Bearer "; // // public static final String SUBJECT = "congge"; // public static final long EXPIRITION = 1000 * 24 * 60 * 60 * 7; public static final String APPSECRET_KEY = "congge_secret"; private static final String ROLE_CLAIMS = "rol"; /** * 生成token * @param username * @param role * @return */ public static String createToken(String username,String role) { Map<String,Object> map = new HashMap<>(); map.put(ROLE_CLAIMS, role); String token = Jwts .builder() .setSubject(username) .setClaims(map) .claim("username",username) .setIssuedAt(new Date()) .setExpiration(new Date(System.currentTimeMillis() + EXPIRITION)) .signWith(SignatureAlgorithm.HS256, APPSECRET_KEY).compact(); return token; } public static Claims checkJWT(String token) { try { final Claims claims = Jwts.parser().setSigningKey(APPSECRET_KEY).parseClaimsJws(token).getBody(); return claims; } catch (Exception e) { e.printStackTrace(); return null; } } /** * 获取用户名 * @param token * @return */ public static String getUsername(String token){ Claims claims = Jwts.parser().setSigningKey(APPSECRET_KEY).parseClaimsJws(token).getBody(); return claims.get("username").toString(); } /** * 获取用户角色 * @param token * @return */ public static String getUserRole(String token){ Claims claims = Jwts.parser().setSigningKey(APPSECRET_KEY).parseClaimsJws(token).getBody(); return claims.get("rol").toString(); } /** * 是否过期 * @param token * @return */ public static boolean isExpiration(String token){ Claims claims = Jwts.parser().setSigningKey(APPSECRET_KEY).parseClaimsJws(token).getBody(); return claims.getExpiration().before(new Date()); } }
PathUtil(主要读取配置文件实行不拦截放行)
/** * Created MR.WU 2020/06/11 */ public class PathUtil { /** * 将通配符表达式转换为正则表达式 * @param path * @return */ public static String getRegPath(String path) { char[] chars = path.toCharArray(); int len = chars.length; StringBuilder sb = new StringBuilder(); boolean preX = false; for(int i=0;i<len;i++){ if (chars[i] == '*'){//遇到*字符 if (preX){//如果是第二次遇到*,则将**替换成.* sb.append(".*"); preX = false; }else if(i+1 == len){//如果是遇到单星,且单星是最后一个字符,则直接将*转成[^/]* sb.append("[^/]*"); }else{//否则单星后面还有字符,则不做任何动作,下一把再做动作 preX = true; continue; } }else{//遇到非*字符 if (preX){//如果上一把是*,则先把上一把的*对应的[^/]*添进来 sb.append("[^/]*"); preX = false; } if (chars[i] == '?'){//接着判断当前字符是不是?,是的话替换成. sb.append('.'); }else{//不是?的话,则就是普通字符,直接添进来 sb.append(chars[i]); } } } return sb.toString(); } }
AuthErrorEnum(授权异常枚举类):
/** * 授权异常枚举类 */ public enum AuthErrorEnum { ACCESS_DENIED(4301,"权限不足"), LOGIN_NAME_ERROR(4302,"用户名不存在"), LOGIN_PASSWORD_ERROR(4304,"密码错误"), AUTH_HEADER_ERROR(4305,"不合法的token验证"), TOKEN_NEEDED(4306,"无token信息,访问当前资源请先登录"), TOKEN_EXPIRED(4307,"token已过期,请重新登录"), TOKEN_REFRESHED(4308,"token已被刷新,用户在其他地方登录"), ALREADY_LOGIN(4309,"用户已在其他设备登录"), SSO_AUTHENTICATE_FAILED(4310,"当前用户未通过sso服务验证") ; private Integer code; private String message; public Integer getCode() { return code; } public void setCode(Integer code) { this.code = code; } public String getMessage() { return message; } public void setMessage(String message) { this.message = message; } AuthErrorEnum(Integer code, String message) { this.code = code; this.message = message; } }
RestAuthenticationEntryPoint类:统一处理返回信息
/** * 统一处理返回信息 * */ public class RestAuthenticationEntryPoint implements AuthenticationEntryPoint { Logger logger = LoggerFactory.getLogger(this.getClass()); @Override public void commence(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse, AuthenticationException e) throws IOException, ServletException { ObjectMapper mapper = new ObjectMapper(); Map<String, String> map = new HashMap<>(); //Map.of("error", "請先登入才能進行此操作"); map.put("error", "請先登入才能進行此操作"); String error = mapper.writeValueAsString(map); httpServletResponse.setContentType("application/json;charset=UTF-8"); httpServletResponse.setCharacterEncoding("UTF-8"); httpServletResponse.setStatus(httpServletResponse.SC_UNAUTHORIZED); PrintWriter writer = httpServletResponse.getWriter(); writer.write(error); writer.flush(); writer.close(); } }
启用 spring Security 授权认证:
/** * 安全配置: * * @author : wulincheng * @date : 13:40 2020/6/9 */ @EnableGlobalMethodSecurity(prePostEnabled = true, proxyTargetClass = true)//启用方法级的权限认证 @EnableWebSecurity public class SecurityConfiguration extends WebSecurityConfigurerAdapter { Logger logger = LoggerFactory.getLogger(SecurityConfiguration.class); @Value("${jwt.tokenHeader}") private String tokenHeader; @Value("${jwt.head}") private String head; @Value("${jwt.permitUris}") private String permitUris; /** * 数据库中取出用户信息 */ @Bean @Override protected UserDetailsService userDetailsService() { return new UserDetailsServiceImpl(); } /** * HTTP请求安全处理 * anyRequest | 匹配所有请求路径 * access | SpringEl表达式结果为true时可以访问 * anonymous | 匿名可以访问 * denyAll | 用户不能访问 * fullyAuthenticated | 用户完全认证可以访问(非remember-me下自动登录) * hasAnyAuthority | 如果有参数,参数表示权限,则其中任何一个权限可以访问 * hasAnyRole | 如果有参数,参数表示角色,则其中任何一个角色可以访问 * hasAuthority | 如果有参数,参数表示权限,则其权限可以访问 * hasIpAddress | 如果有参数,参数表示IP地址,如果用户IP和参数匹配,则可以访问 * hasRole | 如果有参数,参数表示角色,则其角色可以访问 * permitAll | 用户可以任意访问 * rememberMe | 允许通过remember-me登录的用户访问 * authenticated | 用户登录后可访问 */ @Override public void configure(HttpSecurity http) throws Exception { logger.debug("http安全请求"); http.csrf().disable() .cors() .and() .sessionManagement().sessionCreationPolicy(SessionCreationPolicy.STATELESS) .and() .authorizeRequests() .antMatchers(permitUris.split(",")).permitAll() .antMatchers("/api/admin/**").hasRole("ADMIN") .antMatchers("/api/user/**").hasRole("USER") // .antMatchers("/testApi/**").hasRole("admin") .anyRequest().authenticated() .and() .exceptionHandling() .accessDeniedHandler(new AccessDenied())//处理用户登录后的无权访问 .and() .addFilter(new LoginAuthenticationFilter(authenticationManager(), head,tokenHeader)) .addFilter(new MyAuthenticationFilter(authenticationManager(),tokenHeader, head,userDetailsService(),permitUris)) .exceptionHandling().authenticationEntryPoint(new RestAuthenticationEntryPoint()) // .addFilterBefore(new MyExceptionHandleFilter(), LogoutFilter.class) ; } /** * WEB安全 */ @Override public void configure(WebSecurity web) throws Exception { super.configure(web); } /** * 身份验证管理生成器 */ @Override protected void configure(AuthenticationManagerBuilder auth) throws Exception { auth.authenticationProvider(new MyAuthenticationProvider(userDetailsService(),passwordEncoder())); // auth.userDetailsService(userDetailsService()).passwordEncoder(passwordEncoder()); } /* * spring security 5.0之后加密格式改变了,SecurityConfig类中加入 // return new BCryptPasswordEncoder(); * */ @Bean BCryptPasswordEncoder passwordEncoder() { return new BCryptPasswordEncoder(); } /** * 解决跨域问题 * @return */ @Bean public CorsFilter corsFilter() { final UrlBasedCorsConfigurationSource urlBasedCorsConfigurationSource = new UrlBasedCorsConfigurationSource(); final CorsConfiguration corsConfiguration = new CorsConfiguration(); corsConfiguration.setAllowCredentials(true); corsConfiguration.addAllowedOrigin("*"); corsConfiguration.addAllowedHeader("*"); corsConfiguration.addAllowedMethod("*"); urlBasedCorsConfigurationSource.registerCorsConfiguration("/**", corsConfiguration); return new CorsFilter(urlBasedCorsConfigurationSource); } }
根据用户名查询信息和授权:
/** * 从数据库读取授权类型 * */ public class UserDetailsServiceImpl implements UserDetailsService { Logger logger = LoggerFactory.getLogger(this.getClass()); @Autowired private UsersMapper usersMapper; @Autowired private RoleMapper roleDao; /** * 根据用户名进行登录 */ @Override public UserDetails loadUserByUsername(String s) throws UsernameNotFoundException { Users user = usersMapper.findUserByUserName(s); if (user != null) { user.setRoles("ROLE_ADMIN"); UserDetails userDetails = new JwtUser(user); // List<GrantedAuthority> authorities = (List<GrantedAuthority>) userDetails.getAuthorities(); return userDetails; } else { logger.error("用户不存在"); throw new WrongUsernameException(AuthErrorEnum.LOGIN_NAME_ERROR.getMessage()); } } }
最后数据库
-- MySQL Script generated by MySQL Workbench -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema security -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `security` ; -- ----------------------------------------------------- -- Schema security -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `security` DEFAULT CHARACTER SET utf8 ; USE `security` ; -- ----------------------------------------------------- -- Table `security`.`user` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`user` ; CREATE TABLE IF NOT EXISTS `security`.`user` ( `id` INT NOT NULL AUTO_INCREMENT, `user_name` VARCHAR(45) NOT NULL, `user_no` VARCHAR(45) NOT NULL, `password` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `user_no_UNIQUE` (`user_no` ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `security`.`role` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`role` ; CREATE TABLE IF NOT EXISTS `security`.`role` ( `id` INT NOT NULL AUTO_INCREMENT, `role_name` VARCHAR(45) NOT NULL, `role_no` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `role_name_UNIQUE` (`role_name` ASC), UNIQUE INDEX `role_no_UNIQUE` (`role_no` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `security`.`permission` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`permission` ; CREATE TABLE IF NOT EXISTS `security`.`permission` ( `id` INT NOT NULL AUTO_INCREMENT, `permission_name` VARCHAR(45) NOT NULL, `permission_no` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `permission_name_UNIQUE` (`permission_name` ASC), UNIQUE INDEX `permission_no_UNIQUE` (`permission_no` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `security`.`parent_menu` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`parent_menu` ; CREATE TABLE IF NOT EXISTS `security`.`parent_menu` ( `id` INT NOT NULL AUTO_INCREMENT, `parent_menu_name` VARCHAR(45) NOT NULL, `parent_menu_no` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `menu_name_UNIQUE` (`parent_menu_name` ASC), UNIQUE INDEX `menu_no_UNIQUE` (`parent_menu_no` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `security`.`child_menu` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`child_menu` ; CREATE TABLE IF NOT EXISTS `security`.`child_menu` ( `id` INT NOT NULL AUTO_INCREMENT, `child_menu_name` VARCHAR(45) NOT NULL, `child_menu_no` VARCHAR(45) NOT NULL, `parent_menu_id` INT NOT NULL, PRIMARY KEY (`id`, `parent_menu_id`), UNIQUE INDEX `child_menu_name_UNIQUE` (`child_menu_name` ASC), UNIQUE INDEX `child_menu_no_UNIQUE` (`child_menu_no` ASC), INDEX `fk_child_menu_parent_menu_idx` (`parent_menu_id` ASC), CONSTRAINT `fk_child_menu_parent_menu` FOREIGN KEY (`parent_menu_id`) REFERENCES `security`.`parent_menu` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `security`.`user_role` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`user_role` ; CREATE TABLE IF NOT EXISTS `security`.`user_role` ( `id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `role_id` INT NOT NULL, PRIMARY KEY (`id`, `user_id`, `role_id`), INDEX `fk_user_role_user1_idx` (`user_id` ASC), INDEX `fk_user_role_role1_idx` (`role_id` ASC), CONSTRAINT `fk_user_role_user1` FOREIGN KEY (`user_id`) REFERENCES `security`.`user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_user_role_role1` FOREIGN KEY (`role_id`) REFERENCES `security`.`role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `security`.`role_permission` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`role_permission` ; CREATE TABLE IF NOT EXISTS `security`.`role_permission` ( `id` INT NOT NULL AUTO_INCREMENT, `role_id` INT NOT NULL, `permission_id` INT NOT NULL, PRIMARY KEY (`id`, `role_id`, `permission_id`), INDEX `fk_role_permission_role1_idx` (`role_id` ASC), INDEX `fk_role_permission_permission1_idx` (`permission_id` ASC), CONSTRAINT `fk_role_permission_role1` FOREIGN KEY (`role_id`) REFERENCES `security`.`role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_role_permission_permission1` FOREIGN KEY (`permission_id`) REFERENCES `security`.`permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `security`.`permission_parent_menu` -- ----------------------------------------------------- DROP TABLE IF EXISTS `security`.`permission_parent_menu` ; CREATE TABLE IF NOT EXISTS `security`.`permission_parent_menu` ( `id` INT NOT NULL AUTO_INCREMENT, `permission_id` INT NOT NULL, `parent_menu_id` INT NOT NULL, PRIMARY KEY (`id`, `permission_id`, `parent_menu_id`), INDEX `fk_permission_parent_menu_permission1_idx` (`permission_id` ASC), INDEX `fk_permission_parent_menu_parent_menu1_idx` (`parent_menu_id` ASC), CONSTRAINT `fk_permission_parent_menu_permission1` FOREIGN KEY (`permission_id`) REFERENCES `security`.`permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_permission_parent_menu_parent_menu1` FOREIGN KEY (`parent_menu_id`) REFERENCES `security`.`parent_menu` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
: