后台服务代码架构：基于 spring-security-oauth2 的 mysql 数据表设计

纵然间

于 2024-02-10 14:16:27 发布

阅读量442

点赞数 10

分类专栏： SQL Java 文章标签：架构 spring mysql

本文链接：https://blog.csdn.net/ly_7956/article/details/136090758

版权

Java 同时被 2 个专栏收录

319 篇文章 2 订阅

订阅专栏

SQL

60 篇文章 0 订阅

订阅专栏

建立数据库及数据表结构

建立数据库及数据表结构
CREATE SCHEMA IF NOT EXISTS `oauth2` DEFAULT CHARACTER SET utf8 ;

USE `oauth2` ;

-- -----------------------------------------------------
-- Table `oauth2`.`clientdetails`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth2`.`clientdetails` (
`appId` VARCHAR(128) NOT NULL,
`resourceIds` VARCHAR(256) NULL DEFAULT NULL,
`appSecret` VARCHAR(256) NULL DEFAULT NULL,
`scope` VARCHAR(256) NULL DEFAULT NULL,
`grantTypes` VARCHAR(256) NULL DEFAULT NULL,
`redirectUrl` VARCHAR(256) NULL DEFAULT NULL,
`authorities` VARCHAR(256) NULL DEFAULT NULL,
`access_token_validity` INT(11) NULL DEFAULT NULL,
`refresh_token_validity` INT(11) NULL DEFAULT NULL,
`additionalInformation` VARCHAR(4096) NULL DEFAULT NULL,
`autoApproveScopes` VARCHAR(256) NULL DEFAULT NULL,
PRIMARY KEY (`appId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `oatuh2`.`oauth_access_token`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_access_token` (
`token_id` VARCHAR(256) NULL DEFAULT NULL,
`token` BLOB NULL DEFAULT NULL,
`authentication_id` VARCHAR(128) NOT NULL,
`user_name` VARCHAR(256) NULL DEFAULT NULL,
`client_id` VARCHAR(256) NULL DEFAULT NULL,
`authentication` BLOB NULL DEFAULT NULL,
`refresh_token` VARCHAR(256) NULL DEFAULT NULL,
PRIMARY KEY (`authentication_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `oatuh2`.`oauth_approvals`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_approvals` (
`userId` VARCHAR(256) NULL DEFAULT NULL,
`clientId` VARCHAR(256) NULL DEFAULT NULL,
`scope` VARCHAR(256) NULL DEFAULT NULL,
`status` VARCHAR(10) NULL DEFAULT NULL,
`expiresAt` DATETIME NULL DEFAULT NULL,
`lastModifiedAt` DATETIME NULL DEFAULT NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `oatuh2`.`oauth_client_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_client_details` (
`client_id` VARCHAR(128) NOT NULL,
`resource_ids` VARCHAR(256) NULL DEFAULT NULL,
`client_secret` VARCHAR(256) NULL DEFAULT NULL,
`scope` VARCHAR(256) NULL DEFAULT NULL,
`authorized_grant_types` VARCHAR(256) NULL DEFAULT NULL,
`web_server_redirect_uri` VARCHAR(256) NULL DEFAULT NULL,
`authorities` VARCHAR(256) NULL DEFAULT NULL,
`access_token_validity` INT(11) NULL DEFAULT NULL,
`refresh_token_validity` INT(11) NULL DEFAULT NULL,
`additional_information` VARCHAR(4096) NULL DEFAULT NULL,
`autoapprove` VARCHAR(256) NULL DEFAULT NULL,
PRIMARY KEY (`client_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `oatuh2`.`oauth_client_token`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_client_token` (
`token_id` VARCHAR(256) NULL DEFAULT NULL,
`token` BLOB NULL DEFAULT NULL,
`authentication_id` VARCHAR(128) NOT NULL,
`user_name` VARCHAR(256) NULL DEFAULT NULL,
`client_id` VARCHAR(256) NULL DEFAULT NULL,
PRIMARY KEY (`authentication_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `oatuh2`.`oauth_code`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_code` (
`code` VARCHAR(256) NULL DEFAULT NULL,
`authentication` BLOB NULL DEFAULT NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `oatuh2`.`oauth_refresh_token`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_refresh_token` (
`token_id` VARCHAR(256) NULL DEFAULT NULL,
`token` BLOB NULL DEFAULT NULL,
`authentication` BLOB NULL DEFAULT NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;