SpringBoot + JPA + Shiro,单WEB项目的简单权限案例

环境

jdk 1.8

maven 3.5

IDEA编辑器

说明

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

案例下载地址：https://download.csdn.net/download/qq_39181568/10955430

没积分的评论留言发给你（不知怎么回事，csdn上传东西无法设置积分是多少 = -=）。

过几天再弄个多系统shiro权限管理，通过redis同步session实现的。

代码

maven配置

<dependencies>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.45</version>
		</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>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

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

		<!--开启 cache 缓存-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-cache</artifactId>
		</dependency>
		<!-- ehcache 缓存 -->
		<dependency>
			<groupId>net.sf.ehcache</groupId>
			<artifactId>ehcache</artifactId>
		</dependency>
		<!-- shiro spring. -->
		<dependency>
			<groupId>org.apache.shiro</groupId>
			<artifactId>shiro-core</artifactId>
			<version>1.4.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.shiro</groupId>
			<artifactId>shiro-spring</artifactId>
			<version>1.4.0</version>
		</dependency>
		<!-- shiro ehcache -->
		<dependency>
			<groupId>org.apache.shiro</groupId>
			<artifactId>shiro-ehcache</artifactId>
			<version>1.4.0</version>
		</dependency>

	</dependencies>

application.xxxxxx

#### Server ####
server.port = 8181
#### 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)

#### 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

ehcache.xml

<?xml version="1.0" encoding="UTF-8"?>
<ehcache updateCheck="false" name="shiroCache">

    <!-- 用于shiro【权限】缓存 -->
    <defaultCache
            maxElementsInMemory="10000"
            eternal="false"
            timeToIdleSeconds="120"
            timeToLiveSeconds="120"
            overflowToDisk="false"
            diskPersistent="false"
            diskExpiryThreadIntervalSeconds="120"
    />
    <!--
       name:缓存名称。
       maxElementsInMemory：缓存最大个数。
       eternal:对象是否永久有效，一但设置了，timeout将不起作用。
       timeToIdleSeconds：设置对象在失效前的允许闲置时间（单位：秒）。仅当eternal=false对象不是永久有效时使用，可选属性，默认值是0，也就是可闲置时间无穷大。
       timeToLiveSeconds：设置对象在失效前允许存活时间（单位：秒）。最大时间介于创建时间和失效时间之间。仅当eternal=false对象不是永久有效时使用，默认是0.，也就是对象存活时间无穷大。
       overflowToDisk：当内存中对象数量达到maxElementsInMemory时，Ehcache将会对象写到磁盘中。
       diskSpoolBufferSizeMB：这个参数设置DiskStore（磁盘缓存）的缓存区大小。默认是30MB。每个Cache都应该有自己的一个缓冲区。
       maxElementsOnDisk：硬盘最大缓存个数。
       diskPersistent：是否缓存虚拟机重启期数据 Whether the disk store persists between restarts of the Virtual Machine. The default value is false.
       diskExpiryThreadIntervalSeconds：磁盘失效线程运行时间间隔，默认是120秒。
       memoryStoreEvictionPolicy：当达到maxElementsInMemory限制时，Ehcache将会根据指定的策略去清理内存。默认策略是LRU（最近最少使用）。你可以设置为FIFO（先进先出）或是LFU（较少使用）。
       clearOnFlush：内存数量最大时是否清除。
   -->

</ehcache>

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.shiro.authc.credential.HashedCredentialsMatcher;
import org.apache.shiro.cache.ehcache.EhCacheManager;
import org.apache.shiro.mgt.SecurityManager;
import org.apache.shiro.realm.Realm;
import org.apache.shiro.spring.security.interceptor.AuthorizationAttributeSourceAdvisor;
import org.apache.shiro.spring.web.ShiroFilterFactoryBean;
import org.apache.shiro.web.mgt.DefaultWebSecurityManager;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
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 {



    /**
     * 凭证匹配器
     * （应为我们的密码校验交给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;
    }

    /**
     * 缓存管理器
     * @return
     */
    @Bean
    public EhCacheManager ehCacheManager(){
        EhCacheManager cacheManager = new EhCacheManager();
        cacheManager.setCacheManagerConfigFile("classpath:ehcache.xml");
        return cacheManager;
    }

    @Bean
    public SecurityManager securityManager(){
        DefaultWebSecurityManager securityManager =  new DefaultWebSecurityManager();
        securityManager.setRealm(myShiroRealm());// 设置 realm
        securityManager.setCacheManager(ehCacheManager());// 设置缓存管理器
        return securityManager;
    }

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

    /**
     * (先走 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;
    }

    /**
     *  简单异常处理
     * @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();
            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