----------------------------------------------------------------------------- -- SECURITY_PRINCIPAL ----------------------------------------------------------------------------- CREATE TABLE SECURITY_PRINCIPAL ( PRINCIPAL_ID INTEGER NOT NULL, CLASSNAME VARCHAR(254) NOT NULL, IS_MAPPING_ONLY SMALLINT NOT NULL, IS_ENABLED SMALLINT NOT NULL, FULL_PATH VARCHAR(254) NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, UNIQUE (FULL_PATH) ); ALTER TABLE SECURITY_PRINCIPAL ADD PRIMARY KEY (PRINCIPAL_ID) ; ----------------------------------------------------------------------------- -- 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 ); ALTER TABLE SECURITY_PERMISSION ADD PRIMARY KEY (PERMISSION_ID) ; ----------------------------------------------------------------------------- -- PRINCIPAL_PERMISSION ----------------------------------------------------------------------------- CREATE TABLE PRINCIPAL_PERMISSION ( PRINCIPAL_ID INTEGER NOT NULL, PERMISSION_ID INTEGER NOT NULL ); ALTER TABLE PRINCIPAL_PERMISSION ADD 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 SMALLINT NOT NULL, CLASSNAME VARCHAR(254), UPDATE_REQUIRED SMALLINT NOT NULL, IS_ENCODED SMALLINT NOT NULL, IS_ENABLED SMALLINT NOT NULL, AUTH_FAILURES SMALLINT NOT NULL, IS_EXPIRED SMALLINT 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 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 SMALLINT default 0, REQUIRES_CERTIFICATE SMALLINT default 0, CHALLENGE_RESPONSE_AUTH SMALLINT default 0, FORM_AUTH SMALLINT default 0, FORM_USER_FIELD VARCHAR(128), FORM_PWD_FIELD VARCHAR(128), REALM VARCHAR(128), UNIQUE (URL) ); ALTER TABLE SSO_SITE ADD PRIMARY KEY (SITE_ID) ; ----------------------------------------------------------------------------- -- SSO_COOKIE ----------------------------------------------------------------------------- CREATE TABLE SSO_COOKIE ( COOKIE_ID INTEGER NOT NULL, COOKIE VARCHAR(1024) NOT NULL, CREATE_DATE TIMESTAMP NOT NULL ); ALTER TABLE SSO_COOKIE ADD PRIMARY KEY (COOKIE_ID) ; ----------------------------------------------------------------------------- -- SSO_SITE_TO_PRINCIPALS ----------------------------------------------------------------------------- CREATE TABLE SSO_SITE_TO_PRINCIPALS ( SITE_ID INTEGER NOT NULL, PRINCIPAL_ID INTEGER NOT NULL ); ALTER TABLE SSO_SITE_TO_PRINCIPALS ADD 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 ); ALTER TABLE SSO_PRINCIPAL_TO_REMOTE ADD 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 ); ALTER TABLE SSO_SITE_TO_REMOTE ADD 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 ); ALTER TABLE SSO_COOKIE_TO_REMOTE ADD 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 ); ALTER TABLE SECURITY_USER_ROLE ADD PRIMARY KEY (USER_ID,ROLE_ID) ; ----------------------------------------------------------------------------- -- SECURITY_USER_GROUP ----------------------------------------------------------------------------- CREATE TABLE SECURITY_USER_GROUP ( USER_ID INTEGER NOT NULL, GROUP_ID INTEGER NOT NULL ); ALTER TABLE SECURITY_USER_GROUP ADD PRIMARY KEY (USER_ID,GROUP_ID) ; ----------------------------------------------------------------------------- -- SECURITY_GROUP_ROLE ----------------------------------------------------------------------------- CREATE TABLE SECURITY_GROUP_ROLE ( GROUP_ID INTEGER NOT NULL, ROLE_ID INTEGER NOT NULL ); ALTER TABLE SECURITY_GROUP_ROLE ADD PRIMARY KEY (GROUP_ID,ROLE_ID) ; ALTER TABLE PRINCIPAL_PERMISSION FOREIGN KEY FK_PRINCIPAL_PERMISSION_1 (PERMISSION_ID) REFERENCES SECURITY_PERMISSION (PERMISSION_ID) ; ALTER TABLE PRINCIPAL_PERMISSION FOREIGN KEY FK_PRINCIPAL_PERMISSION_2 (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SECURITY_CREDENTIAL FOREIGN KEY FK_SECURITY_CREDENTIAL_1 (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SSO_SITE_TO_PRINCIPALS FOREIGN KEY SSO_SITE_TO_PRINC_FK1 (SITE_ID) REFERENCES SSO_SITE (SITE_ID) ; ALTER TABLE SSO_SITE_TO_PRINCIPALS FOREIGN KEY SSO_SITE_TO_PRINC_FK2 (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SSO_PRINCIPAL_TO_REMOTE FOREIGN KEY FK_SSO_PRINCIPAL_TO_REMOTE_1 (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SSO_PRINCIPAL_TO_REMOTE FOREIGN KEY FK_SSO_PRINCIPAL_TO_REMOTE_2 (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SSO_SITE_TO_REMOTE FOREIGN KEY FK_SSO_SITE_TO_REMOTE_1 (SITE_ID) REFERENCES SSO_SITE (SITE_ID) ; ALTER TABLE SSO_SITE_TO_REMOTE FOREIGN KEY FK_SSO_SITE_TO_REMOTE_2 (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SSO_COOKIE_TO_REMOTE FOREIGN KEY FK_SSO_COOKIE_TO_REMOTE_1 (COOKIE_ID) REFERENCES SSO_COOKIE (COOKIE_ID) ; ALTER TABLE SSO_COOKIE_TO_REMOTE FOREIGN KEY FK_SSO_COOKIE_TO_REMOTE_2 (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SECURITY_USER_ROLE FOREIGN KEY FK_SECURITY_USER_ROLE_1 (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SECURITY_USER_ROLE FOREIGN KEY FK_SECURITY_USER_ROLE_2 (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SECURITY_USER_GROUP FOREIGN KEY FK_SECURITY_USER_GROUP_1 (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SECURITY_USER_GROUP FOREIGN KEY FK_SECURITY_USER_GROUP_2 (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SECURITY_GROUP_ROLE FOREIGN KEY FK_SECURITY_GROUP_ROLE_1 (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ; ALTER TABLE SECURITY_GROUP_ROLE FOREIGN KEY FK_SECURITY_GROUP_ROLE_2 (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ;