SpringBoo+JPA+Shiro+Redis,多系统权限管理简单案例，单点，同步session

环境

jdk 1.8

maven 3.5

IDEA编辑器

说明

       代码部分只把主要的贴出来了，注意数据库这里我用的是sqlserver，如果你是其他库，自行更换maven中配置和application中的配置。因为使用的JPA代码中获取User信息时就顺带了角色和权限的数据。因为作者比较懒 = -=，所有访问方法全是@ResponseBody的，所以登录直接访问url地址login，问号传账号密码来测试。

       sessionId在redis中的管理是org.crazycake提供的。

       多系统个权限代码一样，建两个相同的项目就可以了，redis地址要一样。用户角色权限的数据放在一个数据库中管理，代码里只是简单哪里所以多个项目的数据库也是一样的，如果想权限数据的管理只要一个项目来弄的话，可以单独弄一个系统来提供接口，供其他系统获取权限数据就可以了。

       如：第一个系统的代码跟下面一致，但是第一个系统多了几个接口供其他系统获取权限数据。第二个系统在realm的doGetAuthenticationInfo方法中通过用户名获取用户信息的哪里，通过Httpclient什么的，来访问第一系统的接口获取用户信息就可以了。

代码

maven配置

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-redis</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

		<!-- fastjson -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.47</version>
		</dependency>

		<!-- shiro spring. -->
		<dependency>
			<groupId>org.apache.shiro</groupId>
			<artifactId>shiro-core</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.shiro</groupId>
			<artifactId>shiro-spring</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.shiro</groupId>
			<artifactId>shiro-all</artifactId>
			<version>1.3.2</version>
		</dependency>
		<!-- shiro-redis -->
		<dependency>
			<groupId>org.crazycake</groupId>
			<artifactId>shiro-redis</artifactId>
			<version>3.1.0</version>
		</dependency>
	</dependencies>

application.xxxxxx

#### Server ####
server.port = 8182
#### server.servlet.context-path=/client_v1
server.tomcat.uri-encoding=UTF-8
spring.http.encoding.charset=UTF-8
spring.http.encoding.enabled=true
spring.http.encoding.force=true
spring.messages.encoding=UTF-8
spring.mvc.favicon.enabled = true
spring.banner.charset=UTF-8

#### datasource ####
spring.datasource.url=jdbc:sqlserver\://localhost\:1433;databaseName\=client_v1
spring.datasource.username=sa
spring.datasource.password=sqlserver2012
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.sql-script-encoding=UTF-8
spring.datasource.data-source-class-name=com.mchange.v2.c3p0.ComboPooledDataSource
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5
spring.datasource.validation-query=SELECT 1
spring.datasource.test-on-borrow=false
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=18800
spring.datasource.jdbc-interceptors=ConnectionState;SlowQueryReport(threshold=0)

#### redis ####
spring.redis.host=127.0.0.1
spring.redis.port=6379
spring.redis.password=123456
spring.redis.timeout=1800000

#### JPA ####
# Specify the DBMS
spring.jpa.database = SQL_SERVER
# Show or not log for each sql query
spring.jpa.show-sql = true
spring.jpa.generate-ddl = false
# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto = update
# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

#### mvc ####
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html
spring.thymeleaf.mode=HTML5
spring.thymeleaf.encoding=UTF-8
spring.thymeleaf.servlet.content-type=text/html; charset=utf-8
spring.thymeleaf.cache=false

realm

package com.client.config;

import com.client.dao.UserInfoDao;
import com.client.entity.SysPermission;
import com.client.entity.SysRole;
import com.client.entity.UserInfo;
import com.client.service.UserInfoService;
import com.client.service.impl.UserInfoServiceImpl;
import com.client.util.SpringBeanFactoryUtils;
import org.apache.shiro.authc.AuthenticationException;
import org.apache.shiro.authc.AuthenticationInfo;
import org.apache.shiro.authc.AuthenticationToken;
import org.apache.shiro.authc.SimpleAuthenticationInfo;
import org.apache.shiro.authz.AuthorizationInfo;
import org.apache.shiro.authz.SimpleAuthorizationInfo;
import org.apache.shiro.realm.AuthorizingRealm;
import org.apache.shiro.subject.PrincipalCollection;
import org.apache.shiro.util.ByteSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;

/**
 * Created by 10332 on 2019/1/23.
 *
 */
public class ShiroRealm extends AuthorizingRealm {

    @Autowired
    private UserInfoService userInfoService;

    @Override
    protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principals) {
        System.out.println("权限认证-----------------------------------------------》》权限认证");
        SimpleAuthorizationInfo authorizationInfo = new SimpleAuthorizationInfo();
        // 获取用户信息
        UserInfo userInfo  = (UserInfo)principals.getPrimaryPrincipal();
        for(SysRole role : userInfo.getSysRoleList()){
            authorizationInfo.addRole(role.getRole());// 添加角色
            for(SysPermission p : role.getSysPermissionList()){
                authorizationInfo.addStringPermission(p.getPermission());// 添加权限
            }
        }
        return authorizationInfo;
    }

    @Override
    protected AuthenticationInfo doGetAuthenticationInfo(AuthenticationToken token) throws AuthenticationException {
        System.out.println("登录认证-----------------------------------------------》》登录认证");
        // 获取用户账号.
        String username = (String)token.getPrincipal();
        // 获取用户信息
        UserInfo userInfo = userInfoService.findByUsername(username);
        if(userInfo == null){
            return null;
        }
        /*
            shiro在对比密码：hashedCredentials 等于 【用户输入的密码】。
            hashedCredentials为SimpleAuthenticationInfo第二参数。
         */
        SimpleAuthenticationInfo authenticationInfo = new SimpleAuthenticationInfo(
                userInfo, // 可以通过securityutis.getsubject().getprincipal();取出
                userInfo.getPassword(), // 查出的用户密码【加密后】
                ByteSource.Util.bytes(userInfo.getCredentialsSalt()),// 因为我写的用户密码是【（密码+username+salt）MD5加密后形成的】，而密码对比操作是shiro做的，所以需要告诉shiro我们加的盐是什么【(username+salt)】。
                getName()  // realm name
        );
        return authenticationInfo;
    }
}

ShiroConfig

package com.client.config;

import org.apache.catalina.User;
import org.apache.shiro.authc.credential.HashedCredentialsMatcher;
import org.apache.shiro.session.mgt.eis.JavaUuidSessionIdGenerator;
import org.apache.shiro.spring.LifecycleBeanPostProcessor;
import org.apache.shiro.spring.security.interceptor.AuthorizationAttributeSourceAdvisor;
import org.apache.shiro.spring.web.ShiroFilterFactoryBean;
import org.apache.shiro.web.mgt.DefaultWebSecurityManager;
import org.apache.shiro.web.servlet.SimpleCookie;
import org.apache.shiro.web.session.mgt.DefaultWebSessionManager;
import org.apache.shiro.mgt.SecurityManager;
import org.crazycake.shiro.RedisCacheManager;
import org.crazycake.shiro.RedisManager;
import org.crazycake.shiro.RedisSessionDAO;
import org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.serializer.Jackson2JsonRedisSerializer;
import org.springframework.data.redis.serializer.StringRedisSerializer;
import org.springframework.web.servlet.handler.SimpleMappingExceptionResolver;

import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

/**
 * Created by 10332 on 2019/1/23.
 */
@Configuration
public class ShiroConfig {

    @Value("${spring.redis.host}")
    private String host;

    @Value("${spring.redis.port}")
    private Integer port;

    @Value("${spring.redis.password}")
    private String password;

    @Value("${spring.redis.timeout}")
    private Integer timeout;

    /*************************************************************************************************
     *                              redis  session
     *************************************************************************************************/

    /**
     * 注入 redisManager
     * @return
     */
    @Bean
    public RedisManager redisManager(){
        RedisManager redisManager = new RedisManager();     // crazycake 实现
        redisManager.setHost(host);
        redisManager.setPort(port);
        redisManager.setPassword(password);
        redisManager.setTimeout(timeout);
        return redisManager;
    }

    /**
     * 注入redis缓存管理
     * @return
     */
    @Bean
    public RedisCacheManager redisCacheManager(){
        RedisCacheManager cacheManager = new RedisCacheManager();   // crazycake 实现
        cacheManager.setRedisManager(redisManager());
        return cacheManager;
    }

    /**
     * session ID生成器
     * @return
     */
    @Bean
    public JavaUuidSessionIdGenerator sessionIdGenerator(){
        return new JavaUuidSessionIdGenerator();
    }

    /**
     * 注入 sessionDAO
     * @return
     */
    @Bean
    public RedisSessionDAO sessionDAO(){
        RedisSessionDAO sessionDAO = new RedisSessionDAO(); // crazycake 实现
        sessionDAO.setRedisManager(redisManager());
        sessionDAO.setSessionIdGenerator(sessionIdGenerator()); //  Session ID 生成器
        return sessionDAO;
    }

    /**
     * 统一cookie
     * @return
     */
    @Bean
    public SimpleCookie cookie(){
        SimpleCookie cookie = new SimpleCookie("SHAREJSESSIONID"); //  cookie的name,对应的默认是 JSESSIONID
        cookie.setHttpOnly(true);
        cookie.setPath("/");        //  path为 / 用于多个系统共享JSESSIONID
        return cookie;
    }

    /**
     * 注入sessionManager
     * @return
     */
    @Bean
    public DefaultWebSessionManager sessionManager(){
        DefaultWebSessionManager sessionManager = new DefaultWebSessionManager();
        sessionManager.setGlobalSessionTimeout(timeout);// 设置session超时时间
        sessionManager.setDeleteInvalidSessions(true);// 删除无效session
        sessionManager.setSessionIdCookie(cookie());// 设置JSESSIONID
        sessionManager.setSessionDAO(sessionDAO());// 设置sessionDAO
        return sessionManager;
    }

    /**
     * 配置RedisTemplate，充当数据库服务
     * @return
     */
    @Bean
    public RedisTemplate<String,User> redisTemplate(RedisConnectionFactory connectionFactory){
        RedisTemplate<String,User> redisTemplate = new RedisTemplate<>();
        redisTemplate.setConnectionFactory(connectionFactory);
        redisTemplate.setKeySerializer(new StringRedisSerializer());
        redisTemplate.setValueSerializer(new Jackson2JsonRedisSerializer<User>(User.class));
        return redisTemplate;
    }

    /*************************************************************************************************
     *                              realm  SecurityManager
     *************************************************************************************************/

    /**
     * 凭证匹配器
     * （应为我们的密码校验交给Shiro的SimpleAuthenticationInfo进行处理了，所以需要告知我们的密码是怎么加密的）
     * @return
     */
    @Bean
    public HashedCredentialsMatcher hashedCredentialsMatcher(){
        HashedCredentialsMatcher hashedCredentialsMatcher = new HashedCredentialsMatcher();
        hashedCredentialsMatcher.setHashAlgorithmName("md5");//散列算法:这里使用MD5算法;
        hashedCredentialsMatcher.setHashIterations(2);//散列的次数，比如散列两次，相当于 md5(md5(""));
        return hashedCredentialsMatcher;
    }

    /**
     * 注入自定义 realm
     * @return
     */
    @Bean
    public ShiroRealm myShiroRealm(){
        ShiroRealm myShiroRealm = new ShiroRealm();
        // 设置凭据匹配器
        myShiroRealm.setCredentialsMatcher(hashedCredentialsMatcher());
        return myShiroRealm;
    }

    /**
     * 配置SecurityManager
     * @return
     */
    @Bean
    public SecurityManager securityManager(){
        DefaultWebSecurityManager securityManager = new DefaultWebSecurityManager();
        securityManager.setRealm(myShiroRealm());  // 设置realm
        securityManager.setSessionManager(sessionManager());    // 设置sessionManager
        return securityManager;
    }


    /*************************************************************************************************
     *                              filter
     *************************************************************************************************/

    /**
     * (先走 filter ，然后 filter 如果检测到请求头存在 token，则用 token 去 login，走 Realm 去验证)
     * @param securityManager
     * @return
     */
    @Bean
    public ShiroFilterFactoryBean shirFilter(SecurityManager securityManager) {
        System.out.println("shiro------------------------------------------------》》filter");
        ShiroFilterFactoryBean shiroFilterFactoryBean = new ShiroFilterFactoryBean();
        shiroFilterFactoryBean.setSecurityManager(securityManager);
        shiroFilterFactoryBean.setLoginUrl("/toLogin");// 不设置默认默认寻找Web工程根目录下的"/login"页面
        shiroFilterFactoryBean.setSuccessUrl("/home");// 登录成功后要跳转的链接
        /*
        shiroFilterFactoryBean.setUnauthorizedUrl("/403"); // 无权限提示页面;
            因为shiro源代码中判断了filter是否为AuthorizationFilter，
            只有perms，roles，ssl，rest，port才是属于AuthorizationFilter，
            而anon，authcBasic，auchc，user是AuthenticationFilter，
            所以unauthorizedUrl设置后不起作用。
         */
        //<!-- 过滤链定义，从上向下顺序执行，一般将/**放在最为下边 -->:这是一个坑呢，一不小心代码就不好使了;
        //<!-- authc:需要认证; anon:无需认证-->
        Map<String,String> filterChainDefinitionMap = new LinkedHashMap<String,String>();
        filterChainDefinitionMap.put("/logout", "logout");// 配置退出过滤器,其中的具体的退出代码Shiro已经替我们实现了
        filterChainDefinitionMap.put("/login", "anon");
        filterChainDefinitionMap.put("/**", "authc");
        shiroFilterFactoryBean.setFilterChainDefinitionMap(filterChainDefinitionMap);
        return shiroFilterFactoryBean;
    }


    /*************************************************************************************************
     *                              shiro 注解
     *************************************************************************************************/

    /**
     *  开启shiro aop注解支持.
     *  使用代理方式;所以需要开启代码支持;
     * @param securityManager
     * @return
     */
    @Bean
    public AuthorizationAttributeSourceAdvisor authorizationAttributeSourceAdvisor(SecurityManager securityManager){
        AuthorizationAttributeSourceAdvisor authorizationAttributeSourceAdvisor = new AuthorizationAttributeSourceAdvisor();
        authorizationAttributeSourceAdvisor.setSecurityManager(securityManager);
        return authorizationAttributeSourceAdvisor;
    }

    /**
     *  简单异常处理
     * @return
     */
    @Bean(name="simpleMappingExceptionResolver")
    public SimpleMappingExceptionResolver createSimpleMappingExceptionResolver() {
        SimpleMappingExceptionResolver exceptionResolver = new SimpleMappingExceptionResolver();
        Properties mappings = new Properties();
        mappings.setProperty("AuthorizationException", "/403");// 无权限提示页面
        exceptionResolver.setExceptionMappings(mappings);
        return exceptionResolver;
    }

   
}

Controller

package com.client.controller;

import com.client.entity.UserInfo;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.*;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.apache.shiro.subject.Subject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

/**
 * Created by 10332 on 2019/1/23.
 */
@Controller
public class TestController {

    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    @RequestMapping("/toLogin")
    @ResponseBody
    public String login(HttpServletRequest request){
        return "准备登录-----------------------------------------》》准备登录";
    }

    @RequestMapping("/login")
    @ResponseBody
    public String login(HttpServletRequest request,
                        @RequestParam String username,
                        @RequestParam String password){
        logger.info("登录-----------------------------------------》》登录");
        logger.info("进行账号---------------"+username+"；；；密码验证---------------"+password);
        UsernamePasswordToken usernamePasswordToken=new UsernamePasswordToken(username,password);
        try{
            //SecurityUtils.getSubject().getSession().setTimeout((1000L*60L)*30L);// 设置用户登录过期时间（单位毫秒）
            Subject subject = SecurityUtils.getSubject();// 设置用户登录过期时间 去设置配置文件中redis的缓存时长
            subject.login(usernamePasswordToken);   //完成登录
            /* UserInfo user=(UserInfo) subject.getPrincipal();
            HttpSession session = request.getSession();
            session.setAttribute("user", user);*/
            return "登录-----------------------------------------》》成功";
        }catch(UnknownAccountException uae){
            logger.info("对用户[" + username + "]进行登录验证..验证未通过,未知账户");
            return "登录-----------------------------------------》》未知账户";
        }catch(IncorrectCredentialsException ice){
            logger.info("对用户[" + username + "]进行登录验证..验证未通过,错误的凭证");
            return "登录-----------------------------------------》》密码不正确";
        }catch(LockedAccountException lae){
            logger.info("对用户[" + username + "]进行登录验证..验证未通过,账户已锁定");
            request.setAttribute("message", "账户已锁定");
            return "登录-----------------------------------------》》账户已锁定";
        }catch(ExcessiveAttemptsException eae){
            logger.info("对用户[" + username + "]进行登录验证..验证未通过,错误次数过多");
            return "登录-----------------------------------------》》用户名或密码错误次数过多";
        }catch(AuthenticationException ae){
            //通过处理Shiro的运行时AuthenticationException就可以控制用户登录失败或密码错误时的情景
            logger.info("对用户[" + username + "]进行登录验证..验证未通过,堆栈轨迹如下");
            ae.printStackTrace();
            request.setAttribute("message", "用户名或密码不正确");
            return "登录-----------------------------------------》》用户名或密码不正确";
        }
    }

    @RequestMapping("/user/list")
    @RequiresPermissions("user:list")//权限管理;
    @ResponseBody
    public String userList(){
        logger.info("用户列表-----------------------------------------》》用户列表");
        return "用户列表";
    }

    @RequestMapping("/user/add")
    @RequiresPermissions("user:add")//权限管理;
    @ResponseBody
    public String userAdd(){
        logger.info("用户新增-----------------------------------------》》用户新增");
        return "用户新增";
    }

    @RequestMapping("/logout")
    @ResponseBody
    public String logOut(HttpSession session) {
        Subject subject = SecurityUtils.getSubject();
        subject.logout();
        session.removeAttribute("user");
        return "登录注销";
    }


    @RequestMapping("/403")
    @ResponseBody
    public String test(){
        logger.info("无权限访问-----------------------------------------》》无权限访问");
        return "无权限访问";
    }
}

Sql

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : SQL Server
 Source Server Version : 11003128
 Source Host           : localhost:1433
 Source Catalog        : client_v1
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 11003128
 File Encoding         : 65001

 Date: 28/01/2019 16:29:50
*/


-- ----------------------------
-- Table structure for sys_permission
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_permission]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_permission]
GO

CREATE TABLE [dbo].[sys_permission] (
  [id] int  NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [permission] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [seq] int  NULL,
  [state] int  NULL,
  [url] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[sys_permission] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_permission]
-- ----------------------------
INSERT INTO [dbo].[sys_permission]  VALUES (N'1', N'user', N'user:list', N'1', N'1', N'/user/list')
GO


-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_role]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_role]
GO

CREATE TABLE [dbo].[sys_role] (
  [id] int  NOT NULL,
  [description] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [role] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [state] int  NULL
)
GO

ALTER TABLE [dbo].[sys_role] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_role]
-- ----------------------------
INSERT INTO [dbo].[sys_role]  VALUES (N'1', NULL, N'admin', N'1')
GO

INSERT INTO [dbo].[sys_role]  VALUES (N'2', NULL, N'user', N'1')
GO


-- ----------------------------
-- Table structure for sys_role_permission
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_role_permission]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_role_permission]
GO

CREATE TABLE [dbo].[sys_role_permission] (
  [role_id] int  NOT NULL,
  [permission_id] int  NOT NULL
)
GO

ALTER TABLE [dbo].[sys_role_permission] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_role_permission]
-- ----------------------------
INSERT INTO [dbo].[sys_role_permission]  VALUES (N'2', N'1')
GO


-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_user_role]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_user_role]
GO

CREATE TABLE [dbo].[sys_user_role] (
  [uid] int  NOT NULL,
  [role_id] int  NOT NULL,
  [user_id] int  NOT NULL
)
GO

ALTER TABLE [dbo].[sys_user_role] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_user_role]
-- ----------------------------
INSERT INTO [dbo].[sys_user_role]  VALUES (N'1', N'1', N'1')
GO

INSERT INTO [dbo].[sys_user_role]  VALUES (N'2', N'2', N'2')
GO


-- ----------------------------
-- Table structure for user_info
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[user_info]') AND type IN ('U'))
	DROP TABLE [dbo].[user_info]
GO

CREATE TABLE [dbo].[user_info] (
  [id] int  NOT NULL,
  [create_date_time] datetime2(7)  NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [password] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [phone] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [reason] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [remarks] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [salt] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [state] int  NULL,
  [update_date_time] datetime2(7)  NULL,
  [username] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[user_info] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [user_info]
-- ----------------------------
INSERT INTO [dbo].[user_info]  VALUES (N'1', NULL, N'admin', N'00dcc979020645704735fafac8bea4c6', NULL, NULL, NULL, N'8d78869f470951332959580424d4bf4f', N'1', NULL, N'admin')
GO

INSERT INTO [dbo].[user_info]  VALUES (N'2', NULL, N'user', N'00dcc979020645704735fafac8bea4c6', NULL, NULL, NULL, N'8d78869f470951332959580424d4bf4f', N'1', NULL, N'user')
GO


-- ----------------------------
-- Primary Key structure for table sys_permission
-- ----------------------------
ALTER TABLE [dbo].[sys_permission] ADD CONSTRAINT [PK__sys_perm__3213E83FC950BDD2] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Uniques structure for table sys_role
-- ----------------------------
ALTER TABLE [dbo].[sys_role] ADD CONSTRAINT [UK_mwbqlu5c82jfd2w9oa9d6e87d] UNIQUE NONCLUSTERED ([role] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table sys_role
-- ----------------------------
ALTER TABLE [dbo].[sys_role] ADD CONSTRAINT [PK__sys_role__3213E83FECCDC0FB] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Uniques structure for table user_info
-- ----------------------------
ALTER TABLE [dbo].[user_info] ADD CONSTRAINT [UK_f2ksd6h8hsjtd57ipfq9myr64] UNIQUE NONCLUSTERED ([username] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table user_info
-- ----------------------------
ALTER TABLE [dbo].[user_info] ADD CONSTRAINT [PK__user_inf__3213E83F9E4EF6AB] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Foreign Keys structure for table sys_role_permission
-- ----------------------------
ALTER TABLE [dbo].[sys_role_permission] ADD CONSTRAINT [FKomxrs8a388bknvhjokh440waq] FOREIGN KEY ([permission_id]) REFERENCES [sys_permission] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[sys_role_permission] ADD CONSTRAINT [FK9q28ewrhntqeipl1t04kh1be7] FOREIGN KEY ([role_id]) REFERENCES [sys_role] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table sys_user_role
-- ----------------------------
ALTER TABLE [dbo].[sys_user_role] ADD CONSTRAINT [FKhh52n8vd4ny9ff4x9fb8v65qx] FOREIGN KEY ([role_id]) REFERENCES [sys_role] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[sys_user_role] ADD CONSTRAINT [FKgkmyslkrfeyn9ukmolvek8b8f] FOREIGN KEY ([uid]) REFERENCES [user_info] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[sys_user_role] ADD CONSTRAINT [FK430n1pwbrc8ygsb2ol17e4otd] FOREIGN KEY ([user_id]) REFERENCES [user_info] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO