Spring-Security笔记3 相关数据库建立

使用ORALCE数据库

spring-security提供了一套建表语句

表之间的关系：

spring-security提供的建表语句

-- ----------------------------
-- Table structure for PERSISTENT_LOGINS
-- ----------------------------
DROP TABLE "PERSISTENT_LOGINS";
CREATE TABLE "PERSISTENT_LOGINS" (
"USERNAME" VARCHAR2(64 BYTE) NULL ,
"SERIES" VARCHAR2(64 BYTE) NOT NULL ,
"TOKEN" VARCHAR2(64 BYTE) NULL ,
"LAST_USED" TIMESTAMP(6)  NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "PERSISTENT_LOGINS" IS 'Spring Remember me 持久化';

-- ----------------------------
-- Records of PERSISTENT_LOGINS
-- ----------------------------

-- ----------------------------
-- Table structure for SYS_AUTHORITIES
-- ----------------------------
DROP TABLE "SYS_AUTHORITIES";
CREATE TABLE "SYS_AUTHORITIES" (
"AUTHORITY_ID" VARCHAR2(100 BYTE) NOT NULL ,
"AUTHORITY_MARK" VARCHAR2(100 BYTE) NULL ,
"AUTHORITY_NAME" VARCHAR2(100 BYTE) NOT NULL ,
"AUTHORITY_DESC" VARCHAR2(200 BYTE) NULL ,
"MESSAGE" VARCHAR2(100 BYTE) NULL ,
"ENABLE" NUMBER NULL ,
"ISSYS" NUMBER NULL ,
"MODULE_ID" VARCHAR2(100 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Table structure for SYS_AUTHORITIES_RESOURCES
-- ----------------------------
DROP TABLE "SYS_AUTHORITIES_RESOURCES";
CREATE TABLE "SYS_AUTHORITIES_RESOURCES" (
"ID" VARCHAR2(100 BYTE) NOT NULL ,
"RESOURCE_ID" VARCHAR2(100 BYTE) NOT NULL ,
"AUTHORITY_ID" VARCHAR2(100 BYTE) NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of SYS_AUTHORITIES_RESOURCES
-- ----------------------------

-- ----------------------------
-- Table structure for SYS_MODULES
-- ----------------------------
DROP TABLE "SYS_MODULES";
CREATE TABLE "SYS_MODULES" (
"MODULE_ID" VARCHAR2(100 BYTE) NOT NULL ,
"MODULE_NAME" VARCHAR2(100 BYTE) NOT NULL ,
"MODULE_DESC" VARCHAR2(200 BYTE) NULL ,
"MODULE_TYPE" VARCHAR2(100 BYTE) NULL ,
"PARENT" VARCHAR2(100 BYTE) NULL ,
"MODULE_URL" VARCHAR2(100 BYTE) NULL ,
"I_LEVEL" NUMBER NULL ,
"LEAF" NUMBER NULL ,
"APPLICATION" VARCHAR2(100 BYTE) NULL ,
"CONTROLLER" VARCHAR2(100 BYTE) NULL ,
"ENABLE" NUMBER(1) NULL ,
"PRIORITY" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON COLUMN "SYS_MODULES"."I_LEVEL" IS '1';

-- ----------------------------
-- Records of SYS_MODULES
-- ----------------------------

-- ----------------------------
-- Table structure for SYS_RESOURCES
-- ----------------------------
DROP TABLE "SYS_RESOURCES";
CREATE TABLE "SYS_RESOURCES" (
"RESOURCE_ID" VARCHAR2(100 BYTE) NOT NULL ,
"RESOURCE_TYPE" VARCHAR2(100 BYTE) NULL ,
"RESOURCE_NAME" VARCHAR2(100 BYTE) NULL ,
"RESOURCE_DESC" VARCHAR2(200 BYTE) NULL ,
"RESOURCE_PATH" VARCHAR2(200 BYTE) NULL ,
"PRIORITY" VARCHAR2(100 BYTE) NULL ,
"ENABLE" NUMBER NULL ,
"ISSYS" NUMBER NULL ,
"MODULE_ID" VARCHAR2(100 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON COLUMN "SYS_RESOURCES"."RESOURCE_TYPE" IS 'URL,METHOD';

-- ----------------------------
-- Records of SYS_RESOURCES
-- ----------------------------

-- ----------------------------
-- Table structure for SYS_ROLES
-- ----------------------------
DROP TABLE "SYS_ROLES";
CREATE TABLE "SYS_ROLES" (
"ROLE_ID" VARCHAR2(100 BYTE) NOT NULL ,
"ROLE_NAME" VARCHAR2(100 BYTE) NULL ,
"ROLE_DESC" VARCHAR2(200 BYTE) NULL ,
"ENABLE" NUMBER NULL ,
"ISSYS" NUMBER NULL ,
"MODULE_ID" VARCHAR2(100 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Table structure for SYS_ROLES_AUTHORITIES
-- ----------------------------
DROP TABLE "SYS_ROLES_AUTHORITIES";
CREATE TABLE "SYS_ROLES_AUTHORITIES" (
"ID" VARCHAR2(100 BYTE) NOT NULL ,
"AUTHORITY_ID" VARCHAR2(100 BYTE) NOT NULL ,
"ROLE_ID" VARCHAR2(100 BYTE) NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Table structure for SYS_ROLES_MODULES
-- ----------------------------
DROP TABLE "SYS_ROLES_MODULES";
CREATE TABLE "SYS_ROLES_MODULES" (
"ID" VARCHAR2(100 BYTE) NOT NULL ,
"MODULE_ID" VARCHAR2(100 BYTE) NOT NULL ,
"ROLE_ID" VARCHAR2(100 BYTE) NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "SYS_ROLES_MODULES" IS '控制角色对模块的访问权，主要用于生成菜单';

-- ----------------------------
-- Records of SYS_ROLES_MODULES
-- ----------------------------

-- ----------------------------
-- Table structure for SYS_USERS
-- ----------------------------
DROP TABLE "SYS_USERS";
CREATE TABLE "SYS_USERS" (
"USER_ID" VARCHAR2(100 BYTE) NOT NULL ,
"USERNAME" VARCHAR2(100 BYTE) NOT NULL ,
"NAME" VARCHAR2(100 BYTE) NULL ,
"PASSWORD" VARCHAR2(100 BYTE) NOT NULL ,
"DT_CREATE" DATE DEFAULT SYSDATE  NULL ,
"LAST_LOGIN" DATE NULL ,
"DEADLINE" DATE NULL ,
"LOGIN_IP" VARCHAR2(100 BYTE) NULL ,
"V_QZJGID" VARCHAR2(100 BYTE) NULL ,
"V_QZJGMC" VARCHAR2(100 BYTE) NULL ,
"DEP_ID" VARCHAR2(100 BYTE) NULL ,
"DEP_NAME" VARCHAR2(100 BYTE) NULL ,
"ENABLED" NUMBER NULL ,
"ACCOUNT_NON_EXPIRED" NUMBER NULL ,
"ACCOUNT_NON_LOCKED" NUMBER NULL ,
"CREDENTIALS_NON_EXPIRED" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Table structure for SYS_USERS_ROLES
-- ----------------------------
DROP TABLE "SYS_USERS_ROLES";
CREATE TABLE "SYS_USERS_ROLES" (
"ID" VARCHAR2(100 BYTE) NOT NULL ,
"ROLE_ID" VARCHAR2(100 BYTE) NOT NULL ,
"USER_ID" VARCHAR2(100 BYTE) NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;


-- ----------------------------
-- Indexes structure for table PERSISTENT_LOGINS
-- ----------------------------

-- ----------------------------
-- Checks structure for table PERSISTENT_LOGINS
-- ----------------------------
ALTER TABLE "PERSISTENT_LOGINS" ADD CHECK ("SERIES" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table PERSISTENT_LOGINS
-- ----------------------------
ALTER TABLE "PERSISTENT_LOGINS" ADD PRIMARY KEY ("SERIES");

-- ----------------------------
-- Indexes structure for table SYS_AUTHORITIES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_AUTHORITIES
-- ----------------------------
ALTER TABLE "SYS_AUTHORITIES" ADD CHECK ("AUTHORITY_ID" IS NOT NULL);
ALTER TABLE "SYS_AUTHORITIES" ADD CHECK ("AUTHORITY_NAME" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_AUTHORITIES
-- ----------------------------
ALTER TABLE "SYS_AUTHORITIES" ADD PRIMARY KEY ("AUTHORITY_ID");

-- ----------------------------
-- Indexes structure for table SYS_AUTHORITIES_RESOURCES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_AUTHORITIES_RESOURCES
-- ----------------------------
ALTER TABLE "SYS_AUTHORITIES_RESOURCES" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "SYS_AUTHORITIES_RESOURCES" ADD CHECK ("RESOURCE_ID" IS NOT NULL);
ALTER TABLE "SYS_AUTHORITIES_RESOURCES" ADD CHECK ("AUTHORITY_ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_AUTHORITIES_RESOURCES
-- ----------------------------
ALTER TABLE "SYS_AUTHORITIES_RESOURCES" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table SYS_MODULES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_MODULES
-- ----------------------------
ALTER TABLE "SYS_MODULES" ADD CHECK ("MODULE_ID" IS NOT NULL);
ALTER TABLE "SYS_MODULES" ADD CHECK ("MODULE_NAME" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_MODULES
-- ----------------------------
ALTER TABLE "SYS_MODULES" ADD PRIMARY KEY ("MODULE_ID");

-- ----------------------------
-- Indexes structure for table SYS_RESOURCES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_RESOURCES
-- ----------------------------
ALTER TABLE "SYS_RESOURCES" ADD CHECK ("RESOURCE_ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_RESOURCES
-- ----------------------------
ALTER TABLE "SYS_RESOURCES" ADD PRIMARY KEY ("RESOURCE_ID");

-- ----------------------------
-- Indexes structure for table SYS_ROLES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_ROLES
-- ----------------------------
ALTER TABLE "SYS_ROLES" ADD CHECK ("ROLE_ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_ROLES
-- ----------------------------
ALTER TABLE "SYS_ROLES" ADD PRIMARY KEY ("ROLE_ID");

-- ----------------------------
-- Indexes structure for table SYS_ROLES_AUTHORITIES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_ROLES_AUTHORITIES
-- ----------------------------
ALTER TABLE "SYS_ROLES_AUTHORITIES" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "SYS_ROLES_AUTHORITIES" ADD CHECK ("AUTHORITY_ID" IS NOT NULL);
ALTER TABLE "SYS_ROLES_AUTHORITIES" ADD CHECK ("ROLE_ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_ROLES_AUTHORITIES
-- ----------------------------
ALTER TABLE "SYS_ROLES_AUTHORITIES" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table SYS_ROLES_MODULES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_ROLES_MODULES
-- ----------------------------
ALTER TABLE "SYS_ROLES_MODULES" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "SYS_ROLES_MODULES" ADD CHECK ("MODULE_ID" IS NOT NULL);
ALTER TABLE "SYS_ROLES_MODULES" ADD CHECK ("ROLE_ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_ROLES_MODULES
-- ----------------------------
ALTER TABLE "SYS_ROLES_MODULES" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table SYS_USERS
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_USERS
-- ----------------------------
ALTER TABLE "SYS_USERS" ADD CHECK ("USER_ID" IS NOT NULL);
ALTER TABLE "SYS_USERS" ADD CHECK ("USERNAME" IS NOT NULL);
ALTER TABLE "SYS_USERS" ADD CHECK ("PASSWORD" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_USERS
-- ----------------------------
ALTER TABLE "SYS_USERS" ADD PRIMARY KEY ("USER_ID");

-- ----------------------------
-- Indexes structure for table SYS_USERS_ROLES
-- ----------------------------

-- ----------------------------
-- Checks structure for table SYS_USERS_ROLES
-- ----------------------------
ALTER TABLE "SYS_USERS_ROLES" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "SYS_USERS_ROLES" ADD CHECK ("ROLE_ID" IS NOT NULL);
ALTER TABLE "SYS_USERS_ROLES" ADD CHECK ("USER_ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table SYS_USERS_ROLES
-- ----------------------------
ALTER TABLE "SYS_USERS_ROLES" ADD PRIMARY KEY ("ID");


-- ----------------------------
-- Foreign Key structure for table "SYS_AUTHORITIES_RESOURCES"
-- ----------------------------
ALTER TABLE "SYS_AUTHORITIES_RESOURCES" ADD FOREIGN KEY ("AUTHORITY_ID") REFERENCES "SYS_AUTHORITIES" ("AUTHORITY_ID");
ALTER TABLE "SYS_AUTHORITIES_RESOURCES" ADD FOREIGN KEY ("RESOURCE_ID") REFERENCES "SYS_RESOURCES" ("RESOURCE_ID");

-- ----------------------------
-- Foreign Key structure for table "SYS_RESOURCES"
-- ----------------------------
ALTER TABLE "SYS_RESOURCES" ADD FOREIGN KEY ("MODULE_ID") REFERENCES "SYS_MODULES" ("MODULE_ID");

-- ----------------------------
-- Foreign Key structure for table "SYS_ROLES_AUTHORITIES"
-- ----------------------------
ALTER TABLE "SYS_ROLES_AUTHORITIES" ADD FOREIGN KEY ("AUTHORITY_ID") REFERENCES "SYS_AUTHORITIES" ("AUTHORITY_ID");
ALTER TABLE "SYS_ROLES_AUTHORITIES" ADD FOREIGN KEY ("ROLE_ID") REFERENCES "SYS_ROLES" ("ROLE_ID");

-- ----------------------------
-- Foreign Key structure for table "SYS_ROLES_MODULES"
-- ----------------------------
ALTER TABLE "SYS_ROLES_MODULES" ADD FOREIGN KEY ("MODULE_ID") REFERENCES "SYS_MODULES" ("MODULE_ID");
ALTER TABLE "SYS_ROLES_MODULES" ADD FOREIGN KEY ("ROLE_ID") REFERENCES "SYS_ROLES" ("ROLE_ID");

-- ----------------------------
-- Foreign Key structure for table "SYS_USERS_ROLES"
-- ----------------------------
ALTER TABLE "SYS_USERS_ROLES" ADD FOREIGN KEY ("ROLE_ID") REFERENCES "SYS_ROLES" ("ROLE_ID");
ALTER TABLE "SYS_USERS_ROLES" ADD FOREIGN KEY ("USER_ID") REFERENCES "SYS_USERS" ("USER_ID");