环境
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