----------------------------------------------------------------------------- -- SECURITY_PRINCIPAL ----------------------------------------------------------------------------- CREATE TABLE SECURITY_PRINCIPAL ( PRINCIPAL_ID INTEGER NOT NULL, CLASSNAME VARCHAR(254) NOT NULL, IS_MAPPING_ONLY INT2 NOT NULL, IS_ENABLED INT2 NOT NULL, FULL_PATH VARCHAR(254) NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, PRIMARY KEY (PRINCIPAL_ID), CONSTRAINT UIX_SECURITY_PRINCIPAL UNIQUE (FULL_PATH) ); ----------------------------------------------------------------------------- -- SECURITY_PERMISSION ----------------------------------------------------------------------------- CREATE TABLE SECURITY_PERMISSION ( PERMISSION_ID INTEGER NOT NULL, CLASSNAME VARCHAR(254) NOT NULL, NAME VARCHAR(254) NOT NULL, ACTIONS VARCHAR(254) NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, PRIMARY KEY (PERMISSION_ID) ); ----------------------------------------------------------------------------- -- PRINCIPAL_PERMISSION ----------------------------------------------------------------------------- CREATE TABLE PRINCIPAL_PERMISSION ( PRINCIPAL_ID INTEGER NOT NULL, PERMISSION_ID INTEGER NOT NULL, PRIMARY KEY (PRINCIPAL_ID,PERMISSION_ID) ); ----------------------------------------------------------------------------- -- SECURITY_CREDENTIAL ----------------------------------------------------------------------------- CREATE TABLE SECURITY_CREDENTIAL ( CREDENTIAL_ID INTEGER NOT NULL, PRINCIPAL_ID INTEGER NOT NULL, COLUMN_VALUE VARCHAR(254) NOT NULL, TYPE INT2 NOT NULL, CLASSNAME VARCHAR(254), UPDATE_REQUIRED INT2 NOT NULL, IS_ENCODED INT2 NOT NULL, IS_ENABLED INT2 NOT NULL, AUTH_FAILURES INT2 NOT NULL, IS_EXPIRED INT2 NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, PREV_AUTH_DATE TIMESTAMP, LAST_AUTH_DATE TIMESTAMP, EXPIRATION_DATE DATE, PRIMARY KEY (CREDENTIAL_ID) ); ----------------------------------------------------------------------------- -- SSO_SITE ----------------------------------------------------------------------------- CREATE TABLE SSO_SITE ( SITE_ID INTEGER NOT NULL, NAME VARCHAR(254) NOT NULL, URL VARCHAR(254) NOT NULL, ALLOW_USER_SET INT2 default 0, REQUIRES_CERTIFICATE INT2 default 0, CHALLENGE_RESPONSE_AUTH INT2 default 0, FORM_AUTH INT2 default 0, FORM_USER_FIELD VARCHAR(128), FORM_PWD_FIELD VARCHAR(128), REALM VARCHAR(128), PRIMARY KEY (SITE_ID), CONSTRAINT UIX_SITE_URL UNIQUE (URL) ); ----------------------------------------------------------------------------- -- SSO_COOKIE ----------------------------------------------------------------------------- CREATE TABLE SSO_COOKIE ( COOKIE_ID INTEGER NOT NULL, COOKIE VARCHAR(1024) NOT NULL, CREATE_DATE TIMESTAMP NOT NULL, PRIMARY KEY (COOKIE_ID) ); ----------------------------------------------------------------------------- -- SSO_SITE_TO_PRINCIPALS ----------------------------------------------------------------------------- CREATE TABLE SSO_SITE_TO_PRINCIPALS ( SITE_ID INTEGER NOT NULL, PRINCIPAL_ID INTEGER NOT NULL, PRIMARY KEY (SITE_ID,PRINCIPAL_ID) ); ----------------------------------------------------------------------------- -- SSO_PRINCIPAL_TO_REMOTE ----------------------------------------------------------------------------- CREATE TABLE SSO_PRINCIPAL_TO_REMOTE ( PRINCIPAL_ID INTEGER NOT NULL, REMOTE_PRINCIPAL_ID INTEGER NOT NULL, PRIMARY KEY (PRINCIPAL_ID,REMOTE_PRINCIPAL_ID) ); ----------------------------------------------------------------------------- -- SSO_SITE_TO_REMOTE ----------------------------------------------------------------------------- CREATE TABLE SSO_SITE_TO_REMOTE ( SITE_ID INTEGER NOT NULL, PRINCIPAL_ID INTEGER NOT NULL, PRIMARY KEY (SITE_ID,PRINCIPAL_ID) ); ----------------------------------------------------------------------------- -- SSO_COOKIE_TO_REMOTE ----------------------------------------------------------------------------- CREATE TABLE SSO_COOKIE_TO_REMOTE ( COOKIE_ID INTEGER NOT NULL, REMOTE_PRINCIPAL_ID INTEGER NOT NULL, PRIMARY KEY (COOKIE_ID,REMOTE_PRINCIPAL_ID) ); ----------------------------------------------------------------------------- -- SECURITY_USER_ROLE ----------------------------------------------------------------------------- CREATE TABLE SECURITY_USER_ROLE ( USER_ID INTEGER NOT NULL, ROLE_ID INTEGER NOT NULL, PRIMARY KEY (USER_ID,ROLE_ID) ); ----------------------------------------------------------------------------- -- SECURITY_USER_GROUP ----------------------------------------------------------------------------- CREATE TABLE SECURITY_USER_GROUP ( USER_ID INTEGER NOT NULL, GROUP_ID INTEGER NOT NULL, PRIMARY KEY (USER_ID,GROUP_ID) ); ----------------------------------------------------------------------------- -- SECURITY_GROUP_ROLE ----------------------------------------------------------------------------- CREATE TABLE SECURITY_GROUP_ROLE ( GROUP_ID INTEGER NOT NULL, ROLE_ID INTEGER NOT NULL, PRIMARY KEY (GROUP_ID,ROLE_ID) ); ---------------------------------------------------------------------- -- SECURITY_GROUP_ROLE ---------------------------------------------------------------------- ---------------------------------------------------------------------- -- SECURITY_PRINCIPAL ---------------------------------------------------------------------- ---------------------------------------------------------------------- -- SECURITY_PERMISSION ---------------------------------------------------------------------- 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 ; ---------------------------------------------------------------------- -- PRINCIPAL_PERMISSION ---------------------------------------------------------------------- ALTER TABLE SECURITY_CREDENTIAL ADD CONSTRAINT FK_SECURITY_CREDENTIAL_1 FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ---------------------------------------------------------------------- -- SECURITY_CREDENTIAL ---------------------------------------------------------------------- ---------------------------------------------------------------------- -- SSO_SITE ---------------------------------------------------------------------- ---------------------------------------------------------------------- -- SSO_COOKIE ---------------------------------------------------------------------- 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 ; ---------------------------------------------------------------------- -- SSO_SITE_TO_PRINCIPALS ---------------------------------------------------------------------- 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 ; ---------------------------------------------------------------------- -- SSO_PRINCIPAL_TO_REMOTE ---------------------------------------------------------------------- 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 ; ---------------------------------------------------------------------- -- SSO_SITE_TO_REMOTE ---------------------------------------------------------------------- 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 ; ---------------------------------------------------------------------- -- SSO_COOKIE_TO_REMOTE ---------------------------------------------------------------------- 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 ; ---------------------------------------------------------------------- -- SECURITY_USER_ROLE ---------------------------------------------------------------------- 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 ; ---------------------------------------------------------------------- -- SECURITY_USER_GROUP ---------------------------------------------------------------------- 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 ;