221 lines
6.2 KiB
Plaintext
221 lines
6.2 KiB
Plaintext
/*
|
|
* $Revision$
|
|
* $Id$
|
|
*/
|
|
|
|
// Documentatie in de wiki onder Authenticeren
|
|
CREATE TABLE aut_idp
|
|
(
|
|
aut_idp_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_k_idp_key PRIMARY KEY,
|
|
aut_idp_code -- Voor &sso=<code>
|
|
VARCHAR2(30),
|
|
aut_idp_type -- 1=Internal (login.asp, future use), 2=UID_DEC (deprecated),
|
|
NUMBER(3), -- 3=GUID-encrypted (deprecated), 4=JWT, 5=SAML (future use)
|
|
aut_idp_algorithm -- HS256 is HMAC-SHA256
|
|
VARCHAR2(30),
|
|
aut_idp_omschrijving
|
|
VARCHAR2(30),
|
|
aut_idp_opmerking
|
|
VARCHAR2(320),
|
|
aut_idp_secret
|
|
VARCHAR2(128),
|
|
aut_idp_audience
|
|
VARCHAR2(128),
|
|
aut_idp_issuer
|
|
VARCHAR2(128),
|
|
aut_idp_remote_loginurl
|
|
VARCHAR2(128),
|
|
aut_idp_remote_logouturl
|
|
VARCHAR2(128),
|
|
aut_idp_saml_metaurl
|
|
VARCHAR2(128),
|
|
aut_idp_clockskew
|
|
NUMBER(10),
|
|
aut_idp_duration
|
|
NUMBER(10),
|
|
aut_idp_autocreate
|
|
NUMBER(1)
|
|
DEFAULT 0 -- +1: create; +2: update
|
|
NOT NULL,
|
|
prs_afdeling_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_idp_prs_afdeling REFERENCES prs_afdeling(prs_afdeling_key) ON DELETE CASCADE,
|
|
prs_bedrijf_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_idp_prs_bedrijf REFERENCES prs_bedrijf(prs_bedrijf_key) ON DELETE CASCADE,
|
|
fac_functie_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_idp_functie REFERENCES fac_functie(fac_functie_key),
|
|
aut_idp_internal -- can be used for FACFAC
|
|
NUMBER(1)
|
|
DEFAULT 0 NOT NULL,
|
|
aut_idp_ipfilter
|
|
VARCHAR2(320),
|
|
aut_idp_ipauto
|
|
NUMBER(1)
|
|
DEFAULT 0 NOT NULL,
|
|
aut_idp_loglevel
|
|
NUMBER(1) DEFAULT 0 NOT NULL,
|
|
aut_idp_aanmaak
|
|
DATE
|
|
DEFAULT SYSDATE
|
|
);
|
|
|
|
CREATE TABLE aut_idp_map
|
|
(
|
|
aut_idp_map_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_k_idp_map_key PRIMARY KEY,
|
|
aut_idp_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_idp_map_id REFERENCES aut_idp(aut_idp_key) ON DELETE CASCADE,
|
|
aut_idp_map_from -- as sent in JWT
|
|
VARCHAR(100)
|
|
NOT NULL,
|
|
aut_idp_map_to -- FACILITOR column of 1000+kenmerk_key
|
|
NUMBER(10)
|
|
NOT NULL,
|
|
aut_idp_map_identify
|
|
NUMBER(1)
|
|
DEFAULT 0
|
|
NOT NULL,
|
|
aut_idp_map_autocreate -- unused/future use
|
|
NUMBER(1)
|
|
DEFAULT 0 -- +1: create; +2: update
|
|
NOT NULL,
|
|
aut_idp_map_default
|
|
VARCHAR(256),
|
|
aut_idp_map_aanmaak
|
|
DATE
|
|
DEFAULT SYSDATE
|
|
);
|
|
|
|
CREATE TABLE aut_sp
|
|
(
|
|
aut_sp_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_k_sp_key PRIMARY KEY,
|
|
aut_sp_code -- Nodig?
|
|
VARCHAR2(30),
|
|
aut_sp_type -- 1=Internal (login.asp, future use), 2=UID_DEC (deprecated),
|
|
NUMBER(3), -- 3=GUID-encrypted (deprecated), 4=JWT, 5=SAML (future use)
|
|
aut_sp_algorithm -- HS256 is HMAC-SHA256
|
|
VARCHAR2(30),
|
|
aut_sp_omschrijving
|
|
VARCHAR2(30),
|
|
aut_sp_opmerking
|
|
VARCHAR2(320),
|
|
aut_sp_secret
|
|
VARCHAR2(128),
|
|
aut_sp_audience
|
|
VARCHAR2(128),
|
|
aut_idp_duration
|
|
NUMBER(10),
|
|
aut_sp_issuer
|
|
VARCHAR2(128),
|
|
aut_sp_redirect_uri
|
|
VARCHAR2(320),
|
|
fac_functie_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_sp_functie REFERENCES fac_functie(fac_functie_key),
|
|
aut_sp_internal
|
|
NUMBER(1)
|
|
DEFAULT 0 NOT NULL,
|
|
aut_sp_loglevel
|
|
NUMBER(1) DEFAULT 0 NOT NULL,
|
|
aut_sp_aanmaak
|
|
DATE
|
|
DEFAULT SYSDATE
|
|
);
|
|
|
|
-- Mapping attributen
|
|
CREATE TABLE aut_sp_map
|
|
(
|
|
aut_sp_map_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_k_sp_map_key PRIMARY KEY,
|
|
aut_sp_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_sp_map_id REFERENCES aut_sp(aut_sp_key),
|
|
aut_sp_map_from
|
|
NUMBER(3)
|
|
NOT NULL,
|
|
aut_sp_map_to -- as sent in JWT
|
|
VARCHAR(100)
|
|
NOT NULL,
|
|
aut_sp_map_aanmaak
|
|
DATE
|
|
DEFAULT SYSDATE
|
|
);
|
|
|
|
-- Clients (zoals al dan niet native App's)
|
|
CREATE TABLE aut_client
|
|
(
|
|
aut_client_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_k_aut_client_key PRIMARY KEY,
|
|
aut_client_id
|
|
VARCHAR2(64),
|
|
aut_client_type -- 1=Internal (login.asp, future use), 2=UID_DEC (deprecated),
|
|
NUMBER(3), -- 3=GUID-encrypted (deprecated), 4=JWT, 5=SAML (future use)
|
|
aut_client_omschrijving
|
|
VARCHAR2(30),
|
|
aut_client_opmerking
|
|
VARCHAR2(320),
|
|
aut_client_secret
|
|
VARCHAR2(128),
|
|
prs_bedrijf_key -- reference to find tech-adres (bedrijfadres of type N) for notifications
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_prs_bedrijf_key1 REFERENCES prs_bedrijf(prs_bedrijf_key) ON DELETE CASCADE,
|
|
aut_client_customuri -- future use
|
|
VARCHAR2(128),
|
|
aut_client_aanmaak
|
|
DATE
|
|
DEFAULT SYSDATE
|
|
);
|
|
|
|
-- Koppeling client en (toestemming gegeven door) perslid
|
|
CREATE TABLE aut_client_perslid
|
|
(
|
|
aut_client_perslid_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_k_client_perslid PRIMARY KEY,
|
|
aut_client_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_aut_client_id REFERENCES aut_client(aut_client_key) ON DELETE CASCADE,
|
|
aut_client_perslid_device_id
|
|
VARCHAR(320),
|
|
aut_client_perslid_device_name
|
|
VARCHAR(320),
|
|
aut_client_perslid_appbuild
|
|
NUMBER(10),
|
|
aut_client_perslid_appversion
|
|
VARCHAR2(20),
|
|
aut_client_perslid_scope
|
|
VARCHAR(320)
|
|
DEFAULT NULL,
|
|
aut_client_perslid_pushtoken
|
|
VARCHAR(320),
|
|
aut_client_perslid_refreshtkn -- unused yet
|
|
VARCHAR(320),
|
|
aut_client_perslid_refreshdate
|
|
DATE,
|
|
aut_client_perslid_accesstoken
|
|
VARCHAR(320),
|
|
aut_client_perslid_accessdate
|
|
DATE,
|
|
aut_client_perslid_login -- wanneer voor het laatst de app gebruikt
|
|
DATE,
|
|
prs_perslid_key
|
|
NUMBER(10)
|
|
CONSTRAINT aut_r_aut_client_perslid REFERENCES prs_perslid(prs_perslid_key) ON DELETE CASCADE
|
|
CONSTRAINT aut_c_aut_client_perslid CHECK(prs_perslid_key IS NOT NULL),
|
|
aut_client_perslid_aanmaak
|
|
DATE
|
|
DEFAULT SYSDATE
|
|
);
|
|
|
|
REGISTERONCE('$Id$')
|