
-----------------------------------------------------------------------------
-- SECURITY_PRINCIPAL
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_PRINCIPAL
(
    PRINCIPAL_ID NUMBER(10,0) NOT NULL,
    CLASSNAME VARCHAR2(254) NOT NULL,
    IS_MAPPING_ONLY NUMBER(1,0) NOT NULL,
    IS_ENABLED NUMBER(1,0) NOT NULL,
    FULL_PATH VARCHAR2(254) NOT NULL,
    CREATION_DATE TIMESTAMP NOT NULL,
    MODIFIED_DATE TIMESTAMP NOT NULL,
    CONSTRAINT UIX_SECURITY_PRINCIPAL UNIQUE (FULL_PATH)
);

ALTER TABLE SECURITY_PRINCIPAL
    ADD CONSTRAINT SECURITY_PRINCIPAL_PK
PRIMARY KEY (PRINCIPAL_ID);






-----------------------------------------------------------------------------
-- SECURITY_PERMISSION
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_PERMISSION
(
    PERMISSION_ID NUMBER(10,0) NOT NULL,
    CLASSNAME VARCHAR2(254) NOT NULL,
    NAME VARCHAR2(254) NOT NULL,
    ACTIONS VARCHAR2(254) NOT NULL,
    CREATION_DATE TIMESTAMP NOT NULL,
    MODIFIED_DATE TIMESTAMP NOT NULL
);

ALTER TABLE SECURITY_PERMISSION
    ADD CONSTRAINT SECURITY_PERMISSION_PK
PRIMARY KEY (PERMISSION_ID);






-----------------------------------------------------------------------------
-- PRINCIPAL_PERMISSION
-----------------------------------------------------------------------------

CREATE TABLE PRINCIPAL_PERMISSION
(
    PRINCIPAL_ID NUMBER(10,0) NOT NULL,
    PERMISSION_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE PRINCIPAL_PERMISSION
    ADD CONSTRAINT PRINCIPAL_PERMISSION_PK
PRIMARY KEY (PRINCIPAL_ID,PERMISSION_ID);






-----------------------------------------------------------------------------
-- SECURITY_CREDENTIAL
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_CREDENTIAL
(
    CREDENTIAL_ID NUMBER(10,0) NOT NULL,
    PRINCIPAL_ID NUMBER(10,0) NOT NULL,
    COLUMN_VALUE VARCHAR2(254) NOT NULL,
    TYPE NUMBER(5,0) NOT NULL,
    CLASSNAME VARCHAR2(254),
    UPDATE_REQUIRED NUMBER(1,0) NOT NULL,
    IS_ENCODED NUMBER(1,0) NOT NULL,
    IS_ENABLED NUMBER(1,0) NOT NULL,
    AUTH_FAILURES NUMBER(5,0) NOT NULL,
    IS_EXPIRED NUMBER(1,0) NOT NULL,
    CREATION_DATE TIMESTAMP NOT NULL,
    MODIFIED_DATE TIMESTAMP NOT NULL,
    PREV_AUTH_DATE TIMESTAMP,
    LAST_AUTH_DATE TIMESTAMP,
    EXPIRATION_DATE DATE
);

ALTER TABLE SECURITY_CREDENTIAL
    ADD CONSTRAINT SECURITY_CREDENTIAL_PK
PRIMARY KEY (CREDENTIAL_ID);






-----------------------------------------------------------------------------
-- SSO_SITE
-----------------------------------------------------------------------------

CREATE TABLE SSO_SITE
(
    SITE_ID NUMBER(10,0) NOT NULL,
    NAME VARCHAR2(254) NOT NULL,
    URL VARCHAR2(254) NOT NULL,
    ALLOW_USER_SET NUMBER(1,0) default 0,
    REQUIRES_CERTIFICATE NUMBER(1,0) default 0,
    CHALLENGE_RESPONSE_AUTH NUMBER(1,0) default 0,
    FORM_AUTH NUMBER(1,0) default 0,
    FORM_USER_FIELD VARCHAR2(128),
    FORM_PWD_FIELD VARCHAR2(128),
    REALM VARCHAR2(128),
    CONSTRAINT UIX_SITE_URL UNIQUE (URL)
);

ALTER TABLE SSO_SITE
    ADD CONSTRAINT SSO_SITE_PK
PRIMARY KEY (SITE_ID);






-----------------------------------------------------------------------------
-- SSO_COOKIE
-----------------------------------------------------------------------------

CREATE TABLE SSO_COOKIE
(
    COOKIE_ID NUMBER(10,0) NOT NULL,
    COOKIE VARCHAR2(1024) NOT NULL,
    CREATE_DATE TIMESTAMP NOT NULL
);

ALTER TABLE SSO_COOKIE
    ADD CONSTRAINT SSO_COOKIE_PK
PRIMARY KEY (COOKIE_ID);






-----------------------------------------------------------------------------
-- SSO_SITE_TO_PRINCIPALS
-----------------------------------------------------------------------------

CREATE TABLE SSO_SITE_TO_PRINCIPALS
(
    SITE_ID NUMBER(10,0) NOT NULL,
    PRINCIPAL_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE SSO_SITE_TO_PRINCIPALS
    ADD CONSTRAINT SSO_SITE_TO_PRINCIPALS_PK
PRIMARY KEY (SITE_ID,PRINCIPAL_ID);






-----------------------------------------------------------------------------
-- SSO_PRINCIPAL_TO_REMOTE
-----------------------------------------------------------------------------

CREATE TABLE SSO_PRINCIPAL_TO_REMOTE
(
    PRINCIPAL_ID NUMBER(10,0) NOT NULL,
    REMOTE_PRINCIPAL_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_PK
PRIMARY KEY (PRINCIPAL_ID,REMOTE_PRINCIPAL_ID);






-----------------------------------------------------------------------------
-- SSO_SITE_TO_REMOTE
-----------------------------------------------------------------------------

CREATE TABLE SSO_SITE_TO_REMOTE
(
    SITE_ID NUMBER(10,0) NOT NULL,
    PRINCIPAL_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE SSO_SITE_TO_REMOTE
    ADD CONSTRAINT SSO_SITE_TO_REMOTE_PK
PRIMARY KEY (SITE_ID,PRINCIPAL_ID);






-----------------------------------------------------------------------------
-- SSO_COOKIE_TO_REMOTE
-----------------------------------------------------------------------------

CREATE TABLE SSO_COOKIE_TO_REMOTE
(
    COOKIE_ID NUMBER(10,0) NOT NULL,
    REMOTE_PRINCIPAL_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE SSO_COOKIE_TO_REMOTE
    ADD CONSTRAINT SSO_COOKIE_TO_REMOTE_PK
PRIMARY KEY (COOKIE_ID,REMOTE_PRINCIPAL_ID);






-----------------------------------------------------------------------------
-- SECURITY_USER_ROLE
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_USER_ROLE
(
    USER_ID NUMBER(10,0) NOT NULL,
    ROLE_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE SECURITY_USER_ROLE
    ADD CONSTRAINT SECURITY_USER_ROLE_PK
PRIMARY KEY (USER_ID,ROLE_ID);






-----------------------------------------------------------------------------
-- SECURITY_USER_GROUP
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_USER_GROUP
(
    USER_ID NUMBER(10,0) NOT NULL,
    GROUP_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE SECURITY_USER_GROUP
    ADD CONSTRAINT SECURITY_USER_GROUP_PK
PRIMARY KEY (USER_ID,GROUP_ID);






-----------------------------------------------------------------------------
-- SECURITY_GROUP_ROLE
-----------------------------------------------------------------------------

CREATE TABLE SECURITY_GROUP_ROLE
(
    GROUP_ID NUMBER(10,0) NOT NULL,
    ROLE_ID NUMBER(10,0) NOT NULL
);

ALTER TABLE SECURITY_GROUP_ROLE
    ADD CONSTRAINT SECURITY_GROUP_ROLE_PK
PRIMARY KEY (GROUP_ID,ROLE_ID);









ALTER TABLE PRINCIPAL_PERMISSION
    ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_1 FOREIGN KEY (PERMISSION_ID)
    REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
ON DELETE CASCADE
;

ALTER TABLE PRINCIPAL_PERMISSION
    ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_2 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



ALTER TABLE SECURITY_CREDENTIAL
    ADD CONSTRAINT FK_SECURITY_CREDENTIAL_1 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;







ALTER TABLE SSO_SITE_TO_PRINCIPALS
    ADD CONSTRAINT SSO_SITE_TO_PRINC_FK1 FOREIGN KEY (SITE_ID)
    REFERENCES SSO_SITE (SITE_ID)
ON DELETE CASCADE
;

ALTER TABLE SSO_SITE_TO_PRINCIPALS
    ADD CONSTRAINT SSO_SITE_TO_PRINC_FK2 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    ADD CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_1 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;

ALTER TABLE SSO_PRINCIPAL_TO_REMOTE
    ADD CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



ALTER TABLE SSO_SITE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_1 FOREIGN KEY (SITE_ID)
    REFERENCES SSO_SITE (SITE_ID)
ON DELETE CASCADE
;

ALTER TABLE SSO_SITE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_2 FOREIGN KEY (PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



ALTER TABLE SSO_COOKIE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_1 FOREIGN KEY (COOKIE_ID)
    REFERENCES SSO_COOKIE (COOKIE_ID)
ON DELETE CASCADE
;

ALTER TABLE SSO_COOKIE_TO_REMOTE
    ADD CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



ALTER TABLE SECURITY_USER_ROLE
    ADD CONSTRAINT FK_SECURITY_USER_ROLE_1 FOREIGN KEY (ROLE_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;

ALTER TABLE SECURITY_USER_ROLE
    ADD CONSTRAINT FK_SECURITY_USER_ROLE_2 FOREIGN KEY (USER_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



ALTER TABLE SECURITY_USER_GROUP
    ADD CONSTRAINT FK_SECURITY_USER_GROUP_1 FOREIGN KEY (GROUP_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;

ALTER TABLE SECURITY_USER_GROUP
    ADD CONSTRAINT FK_SECURITY_USER_GROUP_2 FOREIGN KEY (USER_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



ALTER TABLE SECURITY_GROUP_ROLE
    ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_1 FOREIGN KEY (GROUP_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;

ALTER TABLE SECURITY_GROUP_ROLE
    ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_2 FOREIGN KEY (ROLE_ID)
    REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
ON DELETE CASCADE
;



