-
-
Save alsritter/c6485a4b66e1cf133c6916d01ed3a9a7 to your computer and use it in GitHub Desktop.
Oauth2相关的5张表
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- oauth_access_token:访问令牌 | |
-- oauth_refresh_token:更新令牌 | |
-- oauth_client_details:客户端信息 | |
-- oauth_code:授权码 | |
-- oauth_approvals:授权记录 | |
-- oauth_client_token: 客户端用来记录token信息 | |
-- ---------------------------- | |
-- 只以密码模式来进行测试,不考虑管理功能,只用到了三张表 | |
-- ---------------------------- | |
-- oauth_client_details | |
-- oauth_access_token | |
-- oauth_refresh_token | |
-- ---------------------------- | |
-- 授权码模式使用下面这四张表 | |
-- ---------------------------- | |
-- oauth_access_token:访问令牌 | |
-- oauth_client_details:客户端信息 | |
-- oauth_code:授权码 | |
-- oauth_refresh_token:更新令牌 | |
-- ---------------------------- | |
-- 凭证(账号)和权限表3张 | |
-- ---------------------------- | |
-- authority 权限表 | |
-- credentials 凭证表(相当于用户账号表) | |
-- credentials_authorities 授权表(以上2个表的关联表) | |
-- ---------------------------- | |
-- 访问令牌 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `oauth_access_token`; | |
CREATE TABLE `oauth_access_token` | |
( | |
`token_id` varchar(255) DEFAULT NULL COMMENT '加密的access_token的值', | |
`token` longblob COMMENT 'OAuth2AccessToken.java 对象序列化后的二进制数据', | |
`authentication_id` varchar(255) DEFAULT NULL COMMENT '加密过的username,client_id,scope', | |
`user_name` varchar(255) DEFAULT NULL COMMENT '登录的用户名', | |
`client_id` varchar(255) DEFAULT NULL COMMENT '客户端ID', | |
`authentication` longblob COMMENT 'OAuth2Authentication.java 对象序列化后的二进制数据', | |
`refresh_token` varchar(255) DEFAULT NULL COMMENT '加密的refresh_token的值' | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 访问令牌 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `oauth_approvals`; | |
CREATE TABLE `oauth_approvals` | |
( | |
`userId` varchar(255) DEFAULT NULL COMMENT '登录的用户名', | |
`clientId` varchar(255) DEFAULT NULL COMMENT '客户端ID', | |
`scope` varchar(255) DEFAULT NULL COMMENT '申请的权限范围', | |
`status` varchar(10) DEFAULT NULL COMMENT '状态(Approve或Deny)', | |
`expiresAt` datetime DEFAULT NULL COMMENT '过期时间', | |
`lastModifiedAt` datetime DEFAULT NULL COMMENT '最终修改时间' | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 客户端信息 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `oauth_client_details`; | |
CREATE TABLE `oauth_client_details` | |
( | |
`client_id` varchar(255) NOT NULL COMMENT '客户端ID', | |
`resource_ids` varchar(255) DEFAULT NULL COMMENT '资源ID集合,多个资源时用逗号(,)分隔', | |
`client_secret` varchar(255) DEFAULT NULL COMMENT '客户端密匙', | |
`scope` varchar(255) DEFAULT NULL COMMENT '客户端申请的权限范围', | |
`authorized_grant_types` varchar(255) DEFAULT NULL COMMENT '客户端支持的 grant_type', | |
`web_server_redirect_uri` varchar(255) DEFAULT NULL COMMENT '重定向URI', | |
`authorities` varchar(255) DEFAULT NULL COMMENT '客户端所拥有的 Spring Security 的权限值,多个用逗号(,)分隔', | |
`access_token_validity` int(11) DEFAULT NULL COMMENT '访问令牌有效时间值(单位:秒)', | |
`refresh_token_validity` int(11) DEFAULT NULL COMMENT '更新令牌有效时间值(单位:秒)', | |
`additional_information` varchar(255) DEFAULT NULL COMMENT '预留字段', | |
`autoapprove` varchar(255) DEFAULT NULL COMMENT '用户是否自动Approval操作' | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 客户端用来记录token信息 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `oauth_client_token`; | |
CREATE TABLE `oauth_client_token` | |
( | |
`token_id` varchar(255) DEFAULT NULL COMMENT '加密的 access_token值', | |
`token` longblob COMMENT 'OAuth2AccessToken.java 对象序列化后的二进制数据', | |
`authentication_id` varchar(255) DEFAULT NULL COMMENT '加密过的 username,client_id,scope', | |
`user_name` varchar(255) DEFAULT NULL COMMENT '登录的用户名', | |
`client_id` varchar(255) DEFAULT NULL COMMENT '客户端ID' | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 授权码 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `oauth_code`; | |
CREATE TABLE `oauth_code` | |
( | |
`code` varchar(255) DEFAULT NULL COMMENT '授权码(未加密)', | |
`authentication` varbinary(255) DEFAULT NULL COMMENT 'AuthorizationRequestHolder.java 对象序列化后的二进制数据' | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 更新令牌 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `oauth_refresh_token`; | |
CREATE TABLE `oauth_refresh_token` | |
( | |
`token_id` varchar(255) DEFAULT NULL COMMENT '加密过的 refresh_token 的值', | |
`token` longblob COMMENT 'OAuth2RefreshToken.java 对象序列化后的二进制数据 ', | |
`authentication` longblob COMMENT 'OAuth2Authentication.java 对象序列化后的二进制数据' | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 用户信息 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `sys_user`; | |
CREATE TABLE `sys_user` | |
( | |
`id` bigint(20) NOT NULL AUTO_INCREMENT, | |
`username` varchar(50) DEFAULT NULL COMMENT '用户名', | |
`password` varchar(50) DEFAULT NULL COMMENT '密码', | |
PRIMARY KEY (`id`) | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8 COMMENT ='用户信息表'; | |
-- ---------------------------- | |
-- 权限表 | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `authority`; | |
CREATE TABLE `authority` | |
( | |
`id` bigint(11) NOT NULL COMMENT '权限id', | |
`authority` varchar(255) DEFAULT NULL COMMENT '权限', | |
PRIMARY KEY (`id`) | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 凭证表(相当于用户账号表) | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `credentials`; | |
CREATE TABLE `credentials` | |
( | |
`id` bigint(11) NOT NULL COMMENT '凭证id', | |
`enabled` tinyint(1) NOT NULL COMMENT '是否可用', | |
`name` varchar(255) NOT NULL COMMENT '用户名', | |
`password` varchar(255) NOT NULL COMMENT '密码', | |
`version` int(11) DEFAULT NULL COMMENT '版本号', | |
PRIMARY KEY (`id`) | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; | |
-- ---------------------------- | |
-- 授权表(以上2个表的关联表) | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `credentials_authorities`; | |
CREATE TABLE `credentials_authorities` | |
( | |
`credentials_id` bigint(20) NOT NULL COMMENT '凭证id', | |
`authorities_id` bigint(20) NOT NULL COMMENT '权限id' | |
) ENGINE = InnoDB | |
DEFAULT CHARSET = utf8; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment