117 lines
5.8 KiB
SQL
117 lines
5.8 KiB
SQL
-- 开放平台SQL
|
||
DROP TABLE T_OPN_SECURITY_INF;
|
||
CREATE TABLE T_OPN_SECURITY_INF
|
||
(
|
||
GATEWAY_ID NUMBER DEFAULT 0 NOT NULL,
|
||
SIGN_ALG_TYPE VARCHAR(1) DEFAULT '2' NOT NULL,
|
||
ENC_ALG_TYPE VARCHAR(1) DEFAULT '2' NOT NULL,
|
||
AUTH_TYPE VARCHAR(1) DEFAULT '0' NOT NULL,
|
||
API_KEY VARCHAR(1) DEFAULT ' ' NOT NULL,
|
||
USERNAME VARCHAR(1) DEFAULT ' ' NOT NULL,
|
||
PASSWORD VARCHAR(1) DEFAULT ' ' NOT NULL,
|
||
IP_TYPE VARCHAR(1) DEFAULT '0' NOT NULL,
|
||
IP_LST VARCHAR(1) DEFAULT ' ' NOT NULL,
|
||
REQ_ID VARCHAR2(64) DEFAULT ' ' NOT NULL,
|
||
TM_SMP VARCHAR2(14) DEFAULT ' ' NOT NULL,
|
||
NOD_ID VARCHAR2(20) DEFAULT ' ' NOT NULL,
|
||
UPD_OPR VARCHAR2(10) DEFAULT ' ' NOT NULL,
|
||
PRIMARY KEY (GATEWAY_ID)
|
||
);
|
||
comment on table T_OPN_SECURITY_INF is '安全信息表';
|
||
comment on column T_OPN_SECURITY_INF.GATEWAY_ID is '请求id';
|
||
comment on column T_OPN_SECURITY_INF.SIGN_ALG_TYPE is '签名算法（0-不设置，1-国际RSA，2-国密SM2）';
|
||
comment on column T_OPN_SECURITY_INF.ENC_ALG_TYPE is '加密算法（0-不设置，1-3DES，2-AES，3-国密SM3）';
|
||
comment on column T_OPN_SECURITY_INF.AUTH_TYPE is '授权类型（0-不设置，1-Token，2-用户密码）';
|
||
comment on column T_OPN_SECURITY_INF.API_KEY is 'APIKey';
|
||
comment on column T_OPN_SECURITY_INF.USERNAME is '用户';
|
||
comment on column T_OPN_SECURITY_INF.PASSWORD is '密码';
|
||
comment on column T_OPN_SECURITY_INF.IP_TYPE is 'IP类型（0-不设置，1-白名单，2-黑名单）';
|
||
comment on column T_OPN_SECURITY_INF.IP_LST is 'IP地址';
|
||
comment on column T_OPN_SECURITY_INF.REQ_ID is '请求id';
|
||
comment on column T_OPN_SECURITY_INF.TM_SMP is '时间戳';
|
||
comment on column T_OPN_SECURITY_INF.NOD_ID is '更新节点';
|
||
comment on column T_OPN_SECURITY_INF.UPD_OPR is '更新操作员';
|
||
|
||
|
||
|
||
-- 合作伙伴信息
|
||
DROP TABLE T_OPN_PARTNER_INF;
|
||
CREATE TABLE T_OPN_PARTNER_INF
|
||
(
|
||
ID NUMBER DEFAULT 0 NOT NULL,
|
||
PARTNER_ID VARCHAR(32) DEFAULT ' ' NOT NULL,
|
||
PARTNER_NM VARCHAR(60) DEFAULT ' ' NOT NULL,
|
||
ENC_CERT_SN NUMBER DEFAULT 0 NOT NULL,
|
||
SIGN_CERT_SN NUMBER DEFAULT 0 NOT NULL,
|
||
ENC_CERT_SN2 NUMBER DEFAULT 0 NOT NULL,
|
||
SIGN_CERT_SN2 NUMBER DEFAULT 0 NOT NULL,
|
||
CERT_WIN_BEG_DT VARCHAR(8) DEFAULT ' ' NOT NULL,
|
||
CERT_WIN_END_DT VARCHAR(8) DEFAULT ' ' NOT NULL,
|
||
STATUS VARCHAR(1) DEFAULT '1' NOT NULL,
|
||
NOD_ID VARCHAR(20) DEFAULT ' ' NOT NULL,
|
||
TM_SMP VARCHAR(14) DEFAULT ' ' NOT NULL,
|
||
UPD_OPR VARCHAR(10) DEFAULT ' ' NOT NULL,
|
||
REQ_ID VARCHAR(64) DEFAULT ' ' NOT NULL,
|
||
PRIMARY KEY (ID)
|
||
);
|
||
CREATE UNIQUE INDEX UI1_T_OPN_PARTNER_INF ON T_OPN_PARTNER_INF (PARTNER_ID);
|
||
|
||
COMMENT ON TABLE T_OPN_PARTNER_INF IS '合作伙伴信息';
|
||
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.ID IS '标识';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.PARTNER_ID IS '合作伙伴编号';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.ENC_CERT_SN IS '加密证书序列号';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.SIGN_CERT_SN IS '签名证书序列号';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.ENC_CERT_SN2 IS '加密证书序列号';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.SIGN_CERT_SN2 IS '签名证书序列号';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.CERT_WIN_BEG_DT IS '证书窗口开始日期';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.CERT_WIN_END_DT IS '证书窗口结束日期';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.STATUS IS '状态（1-有效，0-无效）';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.NOD_ID IS '节点ID';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.TM_SMP IS '时间戳';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.UPD_OPR IS '操作员ID';
|
||
COMMENT ON COLUMN T_OPN_PARTNER_INF.REQ_ID IS '请求ID';
|
||
|
||
-- 证书信息
|
||
DROP TABLE T_OPN_CERTIFICATE_INF;
|
||
CREATE TABLE T_OPN_CERTIFICATE_INF
|
||
(
|
||
ID NUMBER DEFAULT 0 NOT NULL,
|
||
PARENT_ID NUMBER DEFAULT 0 NOT NULL,
|
||
SN VARCHAR(32) DEFAULT ' ' NOT NULL,
|
||
USR_NO VARCHAR(32) DEFAULT ' ' NOT NULL,
|
||
DN VARCHAR(128) DEFAULT ' ' NOT NULL,
|
||
INVALID_DT VARCHAR(8) DEFAULT ' ' NOT NULL,
|
||
CERT_TYP VARCHAR(1) DEFAULT ' ' NOT NULL,
|
||
PRIVATE_CERT VARCHAR(2048) DEFAULT ' ' NOT NULL,
|
||
PUBLIC_CERT VARCHAR(2048) DEFAULT ' ' NOT NULL,
|
||
STATUS VARCHAR(1) DEFAULT '1' NOT NULL,
|
||
REMARK VARCHAR(128) DEFAULT ' ' NOT NULL,
|
||
NOD_ID VARCHAR(20) DEFAULT ' ' NOT NULL,
|
||
TM_SMP VARCHAR(14) DEFAULT ' ' NOT NULL,
|
||
UPD_OPR VARCHAR(10) DEFAULT ' ' NOT NULL,
|
||
REQ_ID VARCHAR(64) DEFAULT ' ' NOT NULL,
|
||
PRIMARY KEY (ID)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX UI1_T_OPN_CERTIFICATE_INF ON T_OPN_CERTIFICATE_INF (SN);
|
||
CREATE INDEX NI1_T_OPN_CERTIFICATE_INF ON T_OPN_CERTIFICATE_INF (USR_NO);
|
||
|
||
COMMENT ON TABLE T_OPN_CERTIFICATE_INF IS '证书信息';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.ID IS '证书标识';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.PARENT_ID IS '上级证书标识';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.USR_NO IS '用户编号';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.SN IS '证书DN编号';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.DN IS '证书DN信息';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.INVALID_DT IS '上级证书标识';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.PRIVATE_CERT IS '私钥证书';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.PUBLIC_CERT IS '公钥证书';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.CERT_TYP IS '证书类型(1-国密,2-RSA)';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.STATUS IS '状态（1-有效，0-无效）';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.REMARK IS '备注';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.NOD_ID IS '节点ID';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.TM_SMP IS '时间戳';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.UPD_OPR IS '操作员ID';
|
||
COMMENT ON COLUMN T_OPN_CERTIFICATE_INF.REQ_ID IS '请求ID';
|
||
|