架构实战项目心得（十一）：基于spring-security-oauth2的mysql数据表设计

最新推荐文章于 2024-06-22 13:29:05 发布

weixin_30908707

最新推荐文章于 2024-06-22 13:29:05 发布

阅读量427

点赞数

文章标签： java 数据库

原文链接：http://www.cnblogs.com/laowangc/p/8882689.html

版权

一、建立数据库及数据表结构

  1 CREATE SCHEMA IF NOT EXISTS `oauth2` DEFAULT CHARACTER SET utf8 ;
  2 USE `oauth2` ;
  3 
  4 -- -----------------------------------------------------
  5 -- Table `oauth2`.`clientdetails`
  6 -- -----------------------------------------------------
  7 CREATE TABLE IF NOT EXISTS `oauth2`.`clientdetails` (
  8   `appId` VARCHAR(128) NOT NULL,
  9   `resourceIds` VARCHAR(256) NULL DEFAULT NULL,
 10   `appSecret` VARCHAR(256) NULL DEFAULT NULL,
 11   `scope` VARCHAR(256) NULL DEFAULT NULL,
 12   `grantTypes` VARCHAR(256) NULL DEFAULT NULL,
 13   `redirectUrl` VARCHAR(256) NULL DEFAULT NULL,
 14   `authorities` VARCHAR(256) NULL DEFAULT NULL,
 15   `access_token_validity` INT(11) NULL DEFAULT NULL,
 16   `refresh_token_validity` INT(11) NULL DEFAULT NULL,
 17   `additionalInformation` VARCHAR(4096) NULL DEFAULT NULL,
 18   `autoApproveScopes` VARCHAR(256) NULL DEFAULT NULL,
 19   PRIMARY KEY (`appId`))
 20 ENGINE = InnoDB
 21 DEFAULT CHARACTER SET = utf8;
 22 
 23 
 24 -- -----------------------------------------------------
 25 -- Table `oatuh2`.`oauth_access_token`
 26 -- -----------------------------------------------------
 27 CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_access_token` (
 28   `token_id` VARCHAR(256) NULL DEFAULT NULL,
 29   `token` BLOB NULL DEFAULT NULL,
 30   `authentication_id` VARCHAR(128) NOT NULL,
 31   `user_name` VARCHAR(256) NULL DEFAULT NULL,
 32   `client_id` VARCHAR(256) NULL DEFAULT NULL,
 33   `authentication` BLOB NULL DEFAULT NULL,
 34   `refresh_token` VARCHAR(256) NULL DEFAULT NULL,
 35   PRIMARY KEY (`authentication_id`))
 36 ENGINE = InnoDB
 37 DEFAULT CHARACTER SET = utf8;
 38 
 39 
 40 -- -----------------------------------------------------
 41 -- Table `oatuh2`.`oauth_approvals`
 42 -- -----------------------------------------------------
 43 CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_approvals` (
 44   `userId` VARCHAR(256) NULL DEFAULT NULL,
 45   `clientId` VARCHAR(256) NULL DEFAULT NULL,
 46   `scope` VARCHAR(256) NULL DEFAULT NULL,
 47   `status` VARCHAR(10) NULL DEFAULT NULL,
 48   `expiresAt` DATETIME NULL DEFAULT NULL,
 49   `lastModifiedAt` DATETIME NULL DEFAULT NULL)
 50 ENGINE = InnoDB
 51 DEFAULT CHARACTER SET = utf8;
 52 
 53 
 54 -- -----------------------------------------------------
 55 -- Table `oatuh2`.`oauth_client_details`
 56 -- -----------------------------------------------------
 57 CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_client_details` (
 58   `client_id` VARCHAR(128) NOT NULL,
 59   `resource_ids` VARCHAR(256) NULL DEFAULT NULL,
 60   `client_secret` VARCHAR(256) NULL DEFAULT NULL,
 61   `scope` VARCHAR(256) NULL DEFAULT NULL,
 62   `authorized_grant_types` VARCHAR(256) NULL DEFAULT NULL,
 63   `web_server_redirect_uri` VARCHAR(256) NULL DEFAULT NULL,
 64   `authorities` VARCHAR(256) NULL DEFAULT NULL,
 65   `access_token_validity` INT(11) NULL DEFAULT NULL,
 66   `refresh_token_validity` INT(11) NULL DEFAULT NULL,
 67   `additional_information` VARCHAR(4096) NULL DEFAULT NULL,
 68   `autoapprove` VARCHAR(256) NULL DEFAULT NULL,
 69   PRIMARY KEY (`client_id`))
 70 ENGINE = InnoDB
 71 DEFAULT CHARACTER SET = utf8;
 72 
 73 
 74 -- -----------------------------------------------------
 75 -- Table `oatuh2`.`oauth_client_token`
 76 -- -----------------------------------------------------
 77 CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_client_token` (
 78   `token_id` VARCHAR(256) NULL DEFAULT NULL,
 79   `token` BLOB NULL DEFAULT NULL,
 80   `authentication_id` VARCHAR(128) NOT NULL,
 81   `user_name` VARCHAR(256) NULL DEFAULT NULL,
 82   `client_id` VARCHAR(256) NULL DEFAULT NULL,
 83   PRIMARY KEY (`authentication_id`))
 84 ENGINE = InnoDB
 85 DEFAULT CHARACTER SET = utf8;
 86 
 87 
 88 -- -----------------------------------------------------
 89 -- Table `oatuh2`.`oauth_code`
 90 -- -----------------------------------------------------
 91 CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_code` (
 92   `code` VARCHAR(256) NULL DEFAULT NULL,
 93   `authentication` BLOB NULL DEFAULT NULL)
 94 ENGINE = InnoDB
 95 DEFAULT CHARACTER SET = utf8;
 96 
 97 
 98 -- -----------------------------------------------------
 99 -- Table `oatuh2`.`oauth_refresh_token`
100 -- -----------------------------------------------------
101 CREATE TABLE IF NOT EXISTS `oauth2`.`oauth_refresh_token` (
102   `token_id` VARCHAR(256) NULL DEFAULT NULL,
103   `token` BLOB NULL DEFAULT NULL,
104   `authentication` BLOB NULL DEFAULT NULL)
105 ENGINE = InnoDB
106 DEFAULT CHARACTER SET = utf8;